Check for any waiting connections

Below is a script that will list any connections that are waiting for resources.
Useful if you find that a process is taking longer than expected.

SELECT
DB_NAME(ER.database_id) DatabaseName,
OT.[session_id],
OT.[exec_context_id],
OT.[wait_duration_ms],
OT.[wait_type],
OT.[blocking_session_id],
OT.[resource_description],
ES.[program_name],
ST.,
ES.[cpu_time],
ES.[memory_usage],
QP.[query_plan]
FROM
sys.dm_os_waiting_tasks OT
INNER JOIN sys.dm_exec_sessions ES ON OT.[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]) ST
OUTER APPLY sys.dm_exec_query_plan (ER.[plan_handle]) QP
WHERE ES.[is_user_process] = 1
ORDER BY OT.[session_id], OT.[exec_context_id];
GO
Subscribe

0 comments