I am seeing CXPACKET waits increase in wait stats. The problem is that this instance is configured to maximum degree of parallelism = 1.

• No parallel plans in cache
• MAXDOP is set to 1 on the instance
• No MAXDOP hints in stored procs or cached plans.

Any thoughts on what might cause this other than an adhoc query with a MAXDOP hint?

• Edited by Daniel Janik 11 hours 37 minutes ago fixed wait type :)

I am seeing <strike>MAXDOP</strike> CXPacket waits increase in wait stats. The problem is that this instance is configured to maximum degree of parallelism = 1.

Never looked into it, but could it be a worker thread waiting on an IO thread?

What are your other highest waits?

Josh

top 10 ordered by wait_time_ms

• HADR_WORK_QUEUE
  HADR_NOTIFICATION_DEQUEUE
  HADR_LOGCAPTURE_WAIT
  BROKER_TASK_STOP
  CXPACKET
  LCK_M_U
  SP_SERVER_DIAGNOSTICS_SLEEP
  BROKER_TRANSMITTER
  WAITFOR
  LAZYWRITER_SLEEP

top 10 ordered by waiting_tasks_count

• CXPACKET
  SOS_SCHEDULER_YIELD
  SLEEP_TASK
  PAGELATCH_EX
  CMEMTHREAD
  WRITELOG
  ASYNC_NETWORK_IO
  LOGMGR_QUEUE
  IO_COMPLETION

Found it! It's UPDATE STATISTICS. So this must be from autostats because we don't update during core hours. Very interesting indeed. Thanks Josh!

FYI: For any future readers, this is what I did to catch it.

IF EXISTS(select * from sys.dm_exec_requests where wait_type = 'cxpacket' or last_wait_type = 'cxpacket')
BEGIN 
	select * from sys.dm_exec_requests where wait_type = 'cxpacket' or last_wait_type = 'cxpacket'
END
waitfor delay '00:00:00.5';

GO 50000

• Marked as answer by Daniel Janik 11 hours 9 minutes ago
• Edited by Daniel Janik 11 hours 8 minutes ago

Found it! It's UPDATE STATISTICS. So this must be from autostats because we don't update during core hours. Very interesting indeed. Thanks Josh!

FYI: For any future readers, this is what I did to catch it.

IF EXISTS(select * from sys.dm_exec_requests where wait_type = 'cxpacket' or last_wait_type = 'cxpacket')
BEGIN 
	select * from sys.dm_exec_requests where wait_type = 'cxpacket' or last_wait_type = 'cxpacket'
END
waitfor delay '00:00:00.5';

GO 50000

Not quite what I said but if it worked I'm happy to have helped!

Actually, that's quite an interesting method you came up with, I think it's a trick with wide application, so thank YOU!

(though I might clean it up to throw the results in a table and issue semi-realtime summaries ...)

Josh

ps - if autostats ignores the system maxdop setting, is that a feature or a bug?

• Edited by JRStern 8 hours 27 minutes ago