All Current Waiting Tasks (sys_dm_os_waiting_tasks.sql)

This Can Be Used To Determine All Current Tasks

SELECT

                [a].[session_id],

                [a].[exec_context_id],

                [a].[wait_duration_ms],

                [a].[wait_type],

                [a].[blocking_session_id],

                [a].[resource_description],

                [b].[program_name],

                [d].[text],

                [d].[dbid],

                [e].[query_plan],

                [b].[cpu_time],

                [b].[memory_usage]

FROM sys.dm_os_waiting_tasks [a]

INNER JOIN sys.dm_exec_sessions [b] ON

                [a].[session_id] = [b].[session_id]

INNER JOIN sys.dm_exec_requests [c] ON

                [b].[session_id] = [c].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([c].[sql_handle]) [d]

OUTER APPLY sys.dm_exec_query_plan ([c].[plan_handle]) [e]

WHERE [b].[is_user_process] = 1

ORDER BY wait_duration_ms desc

GO