Querying for Parallelism

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 (
,MAX(ISNULL(exec_context_id, 0)) as number_of_workers
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
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
,db_name(cp.database_id) AS database_name
,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
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.