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!!! [Edit – 8th-Sep-202]
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
How do we know the degree of parallelism of a currently running query?
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
select session_id, scheduler_id from sys.dm_os_tasks where parent_task_address IS NOT NULL --AND session_id = ? --You can uncomment and give spid as required.