Wednesday, September 20, 2017

SQL Server 2016 Execution Context ID: Trust but Verify II - Short & Sweet

In a previous post, I mentioned that sometimes two separate tasks/workers in a parallel query will identify themselves with the same execution context ID.  A join to sys.dm_os_threads allows identifying tasks with the kpid and clearing up any possible confusion.

Trust but Verify: SQL Server 2016 SP1 CU4 Execution Context ID
http://sql-sasquatch.blogspot.com/2017/09/trust-but-verify-sql-server-2016-sp1.html 

Today I'm just going to pretend that whole issue doesn't exist because I'm feeling lazy. 😊  Let me show another reason that - when it comes to execution context ID - I recommend "trust but verify."

The system I grabbed the graphic below from is, same as the earlier post, SQL Server SP1 CU4.
 
Got a parallel query running, and I want to grab some wait & blocking info for each of the parallel threads.  Makes sense to start in sys.dm_os_tasks and left outer join to sys.dm_os_waiting_tasks.  Because I might have some tasks working at time of capture by the query - they'd be unmatched in the view of waiting tasks.  I've underlined my left outer join back to sys.dm_os_tasks because it was originally unintuitive to do this.  If I want to grab the session ID and execution ID of the task that's blocking... isn't sys.dm_os_waiting_tasks good enough? Well... no. Execution context ID 0 can show up as NULL in the blocking_exec_context_id column, even though the blocking_task_address is populated.  Huh.

No problem, though.  Using that blocking_task_address for a join back to sys.dm_os_tasks allows me to see that execution context ID 2 below is blocked by execution context ID 0 within the same query.



Good.  That's enough for tonight.

No comments:

Post a Comment