CXCONSUMER wait type – history and what you need to know

I’m a little bit overdue to blog about this new wait type, but I wanted to wait until SQL Server 2016 SP2 was released for a bit and people started seeing this new wait type.

History

Back in September 2016 I created a Connect item (3102145) to split the CXPACKET wait into benign and actionable waits, so that when a CXPACKET wait happens, there’s really something to investigate. Unfortunately the Connect website has been shut down by Microsoft so I can’t point you to the original request I made (I also tried in the Internet Archive but couldn’t find it).

The reason I wanted this change is that CXPACKET waits have always been registered by both producer and consumer threads for query plan operators where some threads produce data (i.e. producer threads) and some threads consume the produced data (i.e. consumer threads). However, the waits for the consumer threads are not actionable, because it’s the *producer* threads that are the cause of the consumer thread waits, and so it’s the producer thread waits that are actionable. By splitting the consumer waits out, the number of CXPACKET waits should be reduced, and those that are left should be investigated.

During the PASS Summit in 2017, my friend Pedro Lopes (b|t) on the Tiger Team announced that they’d made the change. The new wait type, CXCONSUMER, was initially added to SQL Server 2017 RTM CU3 and was added to SQL Server 2016 SP2 in April. You can read Pedro’s blog post here. Note that there was initially a bug with the implementation which was fixed in SQL Server 2017 RTM CU4.

What You Need To Know

CXCONSUMER waits can be ignored. And I’ve seen control threads (thread ID = 0) show CXPACKET or CXCONSUMER, depending on what’s happening in the query plan.

I’ve added a CXCONSUMER page to my waits library, and added it to the ‘ignore’ list in all published locations of my wait stats script.

PS And if you do have CXPACKET waits, start here. Don’t just blindly follow random internet advice to set your sp_configure ‘max degree of parallelism’ or ‘cost threshold for parallelism’ options so a set value without testing how it affects your workload and whether a higher or lower number is better for your environment.

The post CXCONSUMER wait type – history and what you need to know appeared first on Paul S. Randal.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569