New live online training class in August: Planning and Implementing an Upgrade/Migration to SQL Server 2017

Continuing our series of live, online classes, Glenn will be delivering his new IEPUM2017: Immersion Event on Planning and Implementing an Upgrade/Migration to SQL Server 2017 in August! The class will be delivered live via WebEx on August 28-30 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

Here are some select quotes from prior attendees of Glenn’s in-person classes:

  • “Glenn is always so patient in answering my numerous questions.”
  • “Course information was very relevant since we are in the midst of migating our on-premise production environment to the cloud and upgrading to SQL Server 2016 or 2017.”
  • “Customers stories were a nice complement to the materials.”
  • “Great info for installing from the ground up.”

The modules covered will be:

  • Upgrade Planning
  • Hardware and Storage Selection
  • SQL Server 2017 Installation and Configuration
  • Upgrade Testing
  • Migration Planning
  • Production Migration Methods

The price of the class is US$699 (or US$599 for prior live, online attendees) and there’s also a combo price for all three new classes announced today.

You can get all the details here.

The class was also announced in our newsletter today, with a US$100 discount for those people who received that newsletter, valid through the end of June. All future live, online classes will always feature a discount for newsletter subscribers.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!

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 *generally* be ignored, but not always, just like most of the waits we generally ignore. I’ve seen control threads (thread ID = 0) show CXPACKET or CXCONSUMER, depending on what’s happening in the query plan, and I’ve seen some weird cases of skewed parallelism where everything shows as CXCONSUMER instead of CXPACKET. 

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. However, if you see a bunch of parallel threads on a long-running query accruing a lot of time waiting for CXCONSUMER, I’d troubleshoot it just like CXPACKET – look for skewed work distribution problems.

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.