SQL SERVER – Active Parallel Requests and Cached Parallel Query History

I Identify parallel running requests separately from parallel queries recorded in the cache. Those lists answer different performance questions.

During a performance health check, a client reported a slowdown without an obvious cause. Parallel queries were part of the investigation. We did not want to make every query serial by changing the server-wide setting.

Find parallel requests running now

SELECT TOP (10) r.session_id, r.request_id, DB_NAME(r.database_id) AS database_name,
       r.status, r.dop, r.parallel_worker_count, r.cpu_time, r.total_elapsed_time,
       SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
         (CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(t.text)
               ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID AND r.dop > 1
ORDER BY r.cpu_time DESC, r.session_id, r.request_id;

This SQL Server 2016-and-later query shows requests with a degree of parallelism greater than one. CPU and elapsed time are milliseconds for the current request. A quiet instance can return no rows. That is a valid observation, not a failed demonstration.

Find frequently executed parallel cache entries

SELECT TOP (10) qs.execution_count, qs.last_execution_time, qs.last_dop,
       qs.last_elapsed_time, qs.last_logical_reads, qs.last_logical_writes,
       qs.last_rows, qs.plan_handle, t.text, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE qs.last_dop > 1
ORDER BY qs.execution_count DESC, qs.last_execution_time DESC
OPTION (MAXDOP 1);

The original script inspected cached plans and execution statistics. That is history since each statement was compiled, not a list of work running now. last_dop describes the last execution, and last_elapsed_time uses microseconds.

Entries disappear when plans leave the cache. Multiple statements in a batch can have separate statistics. Use statement offsets when narrowing the batch text to one statement, and use Query Store when retained history is required.

Permission requirements depend on the server version. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for these server-level performance DMVs. Query text can contain business data, so share the output through the approved process.

Related reading

• Comprehensive Database Performance Health Check
• Read What My Clients Say
• Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216
• SQL SERVER – Parallelism for Heap Scan
• SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
• SQL SERVER – Parallelism Query in Database
• SQL SERVER – Relationship with Parallelism with Locks and Query Wait – Question for You

A cached execution is not a request running now, it is historical work retained with its cache entry.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.