DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 133--------------------------------------------------
       Snapshot of waiting tasks
       By: srinivasma_exceldbp Date: January 20, 2015, 8:11 pm
       ---------------------------------------------------------
       /*==============================================================
       ==============
       
       Summary:  Snapshot of waiting tasks
       
       SQL Server Versions: 2005 onwards
       ----------------------------------------------------------------
       --------------
       
       ================================================================
       ============*/
       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
       *****************************************************