Edit 10/30/19: the latest version of this script, with additions and adapted for newer versions is here.
A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I’ve updated my waiting tasks script to pull in the dop field from sys.dm_exec_query_memory_grants. I’ve also added in a URL field that points into the new waits library, and shortened some of the column names.
Here it is for your use.
(Note that ‘text’ on one line does not have delimiters because that messes up the code formatting plugin):
/*============================================================================ File: WaitingTasks.sql Summary: Snapshot of waiting tasks SQL Server Versions: 2005 onwards ------------------------------------------------------------------------------ Written by Paul S. Randal, SQLskills.com (c) 2016, SQLskills.com. All rights reserved. For more scripts and sample code, check out http://www.SQLskills.com You may alter this code for your own *non-commercial* purposes. You may republish altered code as long as you include this copyright and give due credit, but you must obtain prior permission before blogging this code. THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. ============================================================================*/ SELECT [owt].[session_id] AS [SPID], [owt].[exec_context_id] AS [Thread], [ot].[scheduler_id] AS [Scheduler], [owt].[wait_duration_ms] AS [wait_ms], [owt].[wait_type], [owt].[blocking_session_id] AS [Blocking SPID], [owt].[resource_description], CASE [owt].[wait_type] WHEN N'CXPACKET' THEN RIGHT ([owt].[resource_description], CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1) ELSE NULL END AS [Node ID], [eqmg].[dop] AS [DOP], [er].[database_id] AS [DBID], CAST ('https://www.sqlskills.com/help/waits/' + [owt].[wait_type] as XML) AS [Help/Info URL], [eqp].[query_plan], [est].text FROM sys.dm_os_waiting_tasks [owt] INNER JOIN sys.dm_os_tasks [ot] ON [owt].[waiting_task_address] = [ot].[task_address] INNER JOIN sys.dm_exec_sessions [es] ON [owt].[session_id] = [es].[session_id] INNER JOIN sys.dm_exec_requests [er] ON [es].[session_id] = [er].[session_id] FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON [owt].[session_id] = [eqmg].[session_id] OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est] OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp] WHERE [es].[is_user_process] = 1 ORDER BY [owt].[session_id], [owt].[exec_context_id]; GO
10 thoughts on “Updated sys.dm_os_waiting_tasks script to add query DOP”
Nice script.. One question though.. Why a full join against sys.dm_exec_query_memory_grants?
Just what I found worked when I was testing it.
Also, now you have your awesome wait type library you could consider adding an extra line into the script
Cast (‘https://www.sqlskills.com/help/’ + [owt].[wait_type] as XML) as URL
This provides a direct hyper link to your new resource
Good idea – done!
Paul you are awsome!!!!
Thank you Paul.
In 1 earlier article you mentioned Dop is calculated/used per operator. If my query has 2 operators in query plan, 1 using 4 threads and other 6 threads. What will be final DOP in DMV. Will it be 6 or 4 or 6+4=10?
Thank you always
Whatever the DOP of the operator that’s currently being executed when you run the DMV.
Do I assume correctly that a DOP of “NULL” means that it is not specified and can use the server setting?
No – it means the query is running single-threaded – thanks