Few years back, on one of my engagements, identified lots of performance issues with an application. As part of a detailed analysis, it is observed there are lots of queries under performing with wait type “CXPACKET”. In short, CXPACKET wait types are involved in parallel query execution. These wait types indicate that the SPID is waiting on a parallel process to complete or start. There are many reasons your query may undergo a parallel execution like missing index, non optimized sql script etc. But, CXPACKET is not ALWAYS a bad thing.We will see about CXPACKET in detail with another post.
Now, We will see how to identify the queries with parallel execution. Once you identified the queries, you can further examine to identify the actual cause of the issues and optimize.
select p.*,q.*,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 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
Please note that the above query gets information from cached plan in SQL Server. Hence, I will recommend to run a load test with a real time workload and use the script to identify the parallel executing queries. It is also important that the cached plans may get flushed due various factors like memory pressure, recompile of procedures, auto stats updates etc. But, this helped me a lot at times!!!