Edit 10/30/19: the latest version of this script, with additions and adapted for newer versions is here.
Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. 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) 2015, 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], [owt].[exec_context_id], [ot].[scheduler_id], [owt].[wait_duration_ms], [owt].[wait_type], [owt].[blocking_session_id], [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], --[es].[program_name], [est].text, [er].[database_id], [eqp].[query_plan], [er].[cpu_time] 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] 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
2 thoughts on “Updated sys.dm_os_waiting_tasks script”
It will be helpful if get to know why am I getting same exec_context_id for multiple rows with different waits having waiting task address also same
Can you email me some example output along with the script you’re using please?