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.
A NULL blocking_exec_context_id indicates that the corresponding port on the consumer side of the exchange isn't ready yet (not connected to the task that will receive rows there). It is (sort of) accurate to say the current task is blocked by the parent task (ecid 0) at that moment in time, but once the exchange has finished initializing, the ecid handling that port will be a different ecid. It might be better to regard ecid NULL as "information not yet available, please try again later".
ReplyDeleteThanks! That makes sense. Especially from the standpoint that there's not anything particular to tune or with which to intervene except *maybe* looking for some condition that makes memory handling sluggish.
Delete