SQL SERVER – Find Queries using Parallelism from Cached Plan

I recently came across wonderful blog post of Feodor Georgiev. He is one fine developer and like to dwell in the subject of performance tuning and query optimizations. He is one real genius and original blogger. Recently I came across his wonderful script, which I was in fact writing myself and I found out that he has already posted the same query over here. After getting his permission I am reproducing the same query on this blog.

Note to not run the following script on busy transactional production environment as well, it does not get all historical results as it only applies to cached plan.

Solarwinds

Following T-SQL script gets all the queries and their execution plan where parallelism operations is kicked up. Pay attention there is TOP 10 is used, if you have lots of transactional operations, I suggest that you change TOP 10 to TOP 50.

SELECT TOP 10
p.*,
q.*,
qs.*,
cp.plan_handle
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
OPTION (MAXDOP 1)

 

Above query will return all the queries which are generating parallel plans. I suggest you run above query on your development server and check if above query is returning all the parallel plan queries.

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

Solarwinds
, , ,
Previous Post
SQLAuthority News – SolidQ Journal Released – A Must Read for All
Next Post
SQLAuthority News – The story of the world – Spatial Data types – July 24, 2010

Related Posts

2 Comments. Leave new

  • Thank you, Pinal! One more thing: instead of querying the plan cache directly on a busy environment, I recommend “taking a snapshot” of the cache to a different server and querying it as much as you want. I have a blog post on how to “take a snapshot” here .

    Reply
  • Hi Pinal,

    I don’t see an ORDER BY clause in this query. Wouldn’t we want to order by some criteria to see the top n something? Perhaps ORDER BY total_worker_time DESC?

    Thanks,
    Bennett

    Reply

Leave a Reply

Menu