We decided to host Christmas dinner for our families this year. There were to be about fifteen people coming over for dinner. Suzanne and I set the menu and we went with a standing rib roast as the main attraction. Early in the week Suzanne stopped at our local meat outlet and walked out with a sixteen pound, seven rib side of beef that looked like it came right from Valhalla. It was too large for our biggest pan so we had to use a tray.
The night before, Christmas Eve, we were at Suzanne’s parents for dinner and opening a few gifts. We started talking about the roast and cooking times. My brother-in-law, Tim, is a professional chef (and married to my sister, the professional nutritionist) and earlier in the week he had told us the beast would only need to cook for three hours at most. I started thinking that Tim was probably basing his cooking time on a smaller piece of meat, so we consulted two different cook books to get an idea on cooking times.
Both books told us to expect to cook the meat for 25 minutes per pound. That meant if we wanted to eat for 2PM we would need to start cooking around 7AM. We called Tim again and he confirmed, again, that we would only need to cook it for three hours at 300 degrees. OK, fine, we thought. We got up in the morning, took the beast out to let it warm up to room temperature, and opened gifts. We encrusted the beef in salt and got it into the oven for 10:30AM, as I would have preferred to have it done early. We set the thermometer alarm for 127 degrees and walked away.
And at 1:10PM the alarm went off. The beast was done. We took it out, let it sit for thirty minutes, and when we sliced it open it was a perfect medium. Despite two different cookbooks telling us to cook by the pound, we followed the guidance of a professional who told us to cook according to the internal temperature, and forget the cookbooks.
SQL Server Wait Stats Are Your Thermometer
Last week we had an issue with one of our instances. It was suddenly running hot, the CPU was pegged at 95% or more and no other system using that instance was getting a timely response. We helped to narrow the issue to a particular vendor product (surprise!) and had the people in charge of that application take some action to mitigate. And by “mitigate” I mean they said “our stuff will be done soon, just let it run”. And sure enough, their stuff finished, and the box was fine. Until the next morning, of course.
So, the issue pops up again, this time it was affecting more than just a handful of users that were trying to use that instance and I was asked to take a look. Figuring I would not be able to remote to the box because of the high CPU, I decided to just connect using SSMS and look at the activity monitor. I know, I know…but I did not have many other options so I decided to start with that, just to see what I could see.
I found the culprit spid that was spiking the CPU and I also found a wait stat of CXPACKET. I decided to alter the MAXDOP on the instance from zero to three (it has four physical CPU’s) and the CPU spike started to trend downwards almost immediately. After about 10 minutes the box was stable, no one was reporting any issues with any other applications. Chances are the queries have suddenly spiked the CPU for that particular database because a recent insert or update of data has altered the query plans.
In much the same way as cooking that rib roast, SQL Server wait stats are a way for you to know and measure what is happening inside your database engine. When someone dumps a 16 lb standing rib roast in your lap you do not need to panic; just grab your nearest thermometer and check your wait stats first.
Use them as a way for you to start an action plan. And when in doubt, consult a professional.
Well put! It’s a shame so many people are overwhelmed by the idea of cooking something out of the ordinary. I’m guessing your family was dazzled by the “show” of the roast. Merry, merry!