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 (https://blog.sqlauthority.com)
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 .
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?