Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인

Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

Sys.dm_os_waiting_tasks를 활용하여 실행중인 병렬쿼리의 정보를 확인한다.

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

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/updated-sys-dm_os_waiting_tasks-script/

 

2015-07-15 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQLServer, 병렬쿼리, DMV, sys.dm_os_waiting_tasks, parallelism, CXPACKET