SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://blog.sqlauthority.com/2010/07/24/sql-server-find-queries-using-parallelism-from-cached-plan/

SQL SERVER – Find Queries using Parallelism from Cached Plan

July 24, 2010Pinal DaveSQL, SQL Performance, SQL Server, SQL Tips and Tricks5 comments

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_handleFROMsys.dm_exec_cached_plans cpCROSS apply sys.dm_exec_query_plan(cp.plan_handle) pCROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS qJOIN sys.dm_exec_query_stats qsON qs.plan_handle = cp.plan_handleWHEREcp.cacheobjtype = 'Compiled Plan' ANDp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0OPTION (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.