SQL SERVER – T-SQL Script to Identify TOP 10 Parallel Running Queries

Today we see T-SQL Script to Identify TOP 10 Parallel Running Queries which I wrote as a part of my Comprehensive Database Performance Health Check yesterday’s engagement. You may read my Read What My Clients Say about my performance services here.

Brief Story

One of my expertise is to work with clients who are struggling with the unknown Performance troubles. Think about the situation where suddenly your server gets slower without any known reasons to you. I thrive on those challenges and love to solve those problems.

The client had also a similar problem and that was the biggest challenge for us. We did some investigation and eventually figured out that the queries which were running on multiple CPU were the real culprits. I will blog about the investigation method in future posts.

The initial reaction was to resolve this issue with Max Degree of Parallelism settings at the server levels but that is not something desirable in our situation. We really needed some queries to run parallel.

Identify TOP 10 Parallel Running Queries

Finally, we decided to write a query which will inspect the memory buffer and identify all the queries which are frequently running and using multiple processors.

SELECT TOP 10
p.[dbid], p.objectid, p.query_plan,
q.text,
qs.plan_handle, qs.creation_time, qs.last_execution_time,
qs.execution_count, qs.last_dop, qs.last_elapsed_time,
qs.last_logical_reads, qs.last_logical_writes,
qs.last_rows
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
JOIN sys.dm_exec_query_stats qs
ON qs.plan_handle = cp.plan_handle
WHERE
cp.cacheobjtype = 'Compiled Plan' AND
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY qs.execution_count DESC
OPTION (MAXDOP 1)

When you run the above query, it will bring back the top 10 frequently run which are using multiple CPUs. Please note that the query will also give you how many CPUs each of the query is using as well.

Here are few of the blog posts which are related to this topic:

• 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

Reference: Pinal Dave (https://blog.sqlauthority.com)