Hi,

I am facing wait event CXPACKET in top wait events. we are using SQL Server 2012. the DOP is set to 1. 

Please suggest how to resolve this

R

CXPACKET wait type is showing that your task is getting processed in multiple threads. Usually, this wait is not a good sign as it occurs due to several reasons as below:

1. Index missing

2.Bad coding

3. Stats are not up to date etc...

I would suggest you to fine tune your queries that are being parallel processed.

thanks,

How can I check which queries are being paralllel processed?

R

thanks,

How can I check which queries are being paralllel processed?

R

Hi,

I am facing wait event CXPACKET in top wait events. we are using SQL Server 2012. the DOP is set to 1. 

Any reaosn why DOP is set to 1.Have you tested this scenario.Instaed of first moving for resolving CXPACKET wait which is not a acctual reason it is just a symptom .It happens when various parallel threads are waiting to synchronize after doing the task.What is other major wait stats you can see.Can you paste output of below query here

--By Jnathan Kehayias
SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

Thanks all.

Below is the out of the query : Shanky

Bilal,

 Looking at the image shared, CXPACKET is not the major contributor in wait types. However, you can use the query to find the parallel queries and fine tune those.

Another point is that, the image would not be a correct one as you were already SET MAX DOP to 1. For OLTP systems, instead of setting MAXDOP cap, you may leave the default value 0 and better fine tune those parallel queries.