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.

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 (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – Find Queries using Parallelism from Cached Plan

  1. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Reducing CXPACKET Wait Stats for High Transactional Database Journey to SQL Authority with Pinal Dave

  3. 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

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s