Page History

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.