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.

SQL SERVER - T-SQL Script to Identify TOP 10 Parallel Running Queries parallelqueriestop-800x1064

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:

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

, , , ,
Previous Post
SQL SERVER – Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE
Next Post
SQL SERVER – Generate In-Memory OLTP Migration Checklists – SSMS

Related Posts

3 Comments. Leave new

  • I does not compile, lots of error. Tested SQL Server 2014 ent

    Reply
  • Ok, you seem to have corrected this: “p.[dbid], p.objectid, p.query_plan,
    q.,”. <-

    Other that does not compile is:
    qs.last_dop

    Probably that is missing from earlier version.

    Reply

Leave a Reply

Menu