To identify the responsible for CXPACKET waits in your SQL Server, run the following query: select t.wait_type, t.wait_duration_ms, t.session_id, t.resource_description, s.program_name, b.sql_handle, b.statement_start_offset, b.statement_end_offset, [statement_text]=SUBSTRING(st.text, (b.statement_start_offset/2)+1, ((CASE b.statement_end_offset WHEN-1 THEN DATALENGTH(st.text) ELSE b.statement_end_offset END - b.statement_start_offset)/2) + 1), st.text
from sys.dm_os_waiting_tasks t left join sys.dm_exec_requests b left join sys.dm_exec_sessions s on b.session_id = s.session_id on t.waiting_task_address = b.task_address outer apply sys.dm_exec_sql_text (b.sql_handle) st where b.sql_handle is not null and t.wait_type = 'CXPACKET'; To identify the responsible for CXPACKET waits and also display the execution plan, run the following modified script: select t.wait_type, t.wait_duration_ms, t.session_id, t.resource_description, s.program_name, qp.query_plan, b.statement_start_offset, b.statement_end_offset, [statement_text]=SUBSTRING(st.text, (b.statement_start_offset/2)+1, ((CASE b.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE b.statement_end_offset END - b.statement_start_offset)/2) + 1), st.text
from sys.dm_os_waiting_tasks t left join sys.dm_exec_requests b left join sys.dm_exec_sessions s on b.session_id = s.session_id on t.waiting_task_address = b.task_address outer apply sys.dm_exec_sql_text (b.sql_handle) st cross apply sys.dm_exec_query_plan(b.plan_handle) qp where b.sql_handle is not null and t.wait_type = 'CXPACKET'
After running the query, click the ShowPlan link in the query_plan column to display the execution plan. Review the plan to see if the query in question needs additional tuning. |