While working on some parallelism issues today I started using the query that detects whether there are queries currently running utilizing parallelism. It’s the same query that can be found in the white paper Troubleshooting Performance Problems in SQL Server 2005.
Which I’ve included below:
WITH CurrentParallelism AS ( SELECT r.session_id ,r.request_id ,r.database_id ,MAX(ISNULL(exec_context_id, 0)) as number_of_workers ,r.sql_handle ,r.statement_start_offset ,r.statement_end_offset ,r.plan_handle FROM sys.dm_exec_requests r INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id WHERE s.is_user_process = 0x1 GROUP BY r.session_id, r.request_id, r.database_id, r.sql_handle, r.plan_handle, r.statement_start_offset, r.statement_end_offset HAVING MAX(ISNULL(exec_context_id, 0)) > 0 ) SELECT cp.session_id ,cp.request_id ,db_name(cp.database_id) AS database_name ,cp.number_of_workers ,CASE statement_end_offset WHEN -1 THEN q.text ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement ,p.query_plan ,q.text ,cp.plan_handle FROM CurrentParallelism cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q
The information that I like to have on hand is the exact statement and query plan that is executing so that I know what it is that I am dealing with. Hopefully others feel the same and find some use in this.
Strate SQL 