CX Packet Diagnosis

What is CX packet waittype.

When query uses the parallel threads .i.e available cores assign the threads and execute them. Under these scenarios coordinator wait for the query to be completed.

If another same query executes and uses the same parallel threads and the wait is known as CX packet wait-type.

E.g We can inspect the execution plan and how many rows are distributed in the threads . If it's not equal then one threads will complete quickly those has minimum pages of rows to read and another thread still needs to process due to high pages reads ....so the coordinator wait for another thread to be completed.

To find the wait-type

Declare @spid int

SELECT session_id ,status ,blocking_session_id
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended' and session_id=@spid;
GO

To find the coordinator thread and it's associated parallel threads

SELECT
     task_state,  ---wait-type
    exec_context_id, ---coordinator
     parent_task_address, --parent task o\null means coordinator
    task_address ---associated thread work resource address
  FROM sys.dm_os_tasks
where session_id =@spid

--To detect further status of coordinator wait-type and it's threads

select wait_type,exec_context_id,blocking_exec_context_id,waiting_task_address,blocking_task_address
from sys.dm_os_waiting_tasks

-- To detect the query which coordinator scheduler_id is running or which core is executing ....

select scheduler_id from sys.dm_exec_requeusts.

This information is useful if more than one request processed on the same scheduler_id or core then the remaining request waiting for the coordinator to be completed or wait for the particular core to be available.

What is sleep task

To get number of rows written in each page for the particluar table

SELECT record_count/page_count as Avgrecords  FROM sys.dm_db_index_physical_stats
    (DB_ID(N'dbname'), OBJECT_ID(N'dbo.tablename'), NULL, NULL , 'DETAILED')
where index_level=0

--To get the details of CX packet as it sense how many pages waited in the sleep task . i.e if the query were used in intensive CPU and the same thread used for another request.

The quantum wait-type for each pages to be scheduled in the thread may be 4 ms can be found on sys.dm_os_sys_info  . if you have the total value of waiting_tasks_counts then divide by number of threads used in the particular query and multiply the rows result based on the above value produced by sys.dm_db_index_physical_stats.

SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats