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
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.