Trust but Verify: SQL Server 2016 SP1 CU4 Execution Context ID

The @@version of the system I'm currently working on:

 

I spend a lot of time looking at sys.dm_os_waiting_tasks for parallel queries these days. For parallel queries, multiple sys.dm_os_waiting_tasks rows for a single execution context ID within a session is not unusual.  For example...

Got two rows for session_id 68 exec_context_id 1 above.  Matching the blocking_task_address in those rows to waiting_task_address... ok, exec_context_id 1 is slotted in CXPACKET wait for exec_context_id 3 and 4.  That's reasonable.  As DOP increases the number of exec_context_id values with multiple rows may also increase.  At really high DOP the number of CXPACKET rows can look downright scary :-)  But still legit once used to seeing it.

Here's the waiting tasks for another parallel query.

Like the previous results, there are two rows for exec_context_id 1.  Yeah, we've seen that before :-)

There are also 2 rows for exec_context_id 4.  Hmmm.  That seems plausible.  They show the same resource_address, show the same blocking_task_address.

However... don't be too quick to brush off multiple rows for a given exec_context_id as due to the parallel CXPACKET wait implementation we just discussed.  Or to the "not always transactionally consistent" nature of some of the DMVs.

Check it out below.   The two rows with exec_context_id value 4 have different values for waiting_task_address, and different wait_duration_ms values.  They are different tasks, on different workers.  They really shouldn't be reported as the same exec_context_id.

Does this matter?  Well - the presence of these duplicate values for exec_context_id doesn't seem to be an indication of anything nefarious under the hood.  But if you try to piece together wait information for parallel queries, things can get confusing if not ready to accommodate this possibility.  And monitoring/trending utilities that collect and correlate information from the DMVs can compound the confusion.

Trust but verify, y'all.

Ciao, thanks for reading!

And if you really have a hankerin' for more of the same...

SQL Server 2016 Execution Context ID: Trust but Verify II - Short & Sweet
http://sql-sasquatch.blogspot.com/2017/09/sql-server-2016-execution-context-id.html