Category: Wait Stats

Chad Callihan has an analogy for us:

Have you ever spent Black Friday shopping, filled up your car, and then ran out of space at the end for a big purchase? Your vehicle is already full but that oversized exercise equipment is too big of a deal to pass up! You’re going to have to wait until you can unload at home first before there’s room for that new clothes rack…um…I mean exercise equipment.

That’s kind of the same idea as RESOURCE_SEMAPHORE waits. SQL Server has a large query (that exercise equipment) but not enough memory to execute (purchase).

Read on for Chad’s explanation of how you can deal with RESOURCE_SEMAPHORE waits, but as someone who drives a Miata, of course I have stories. My wife and I went to the mall one day, a few years back, and we bought a space heater for the room above our garage. Well, that thing was just a little too big for the trunk and so, on a 40-something degree day (approximately -15,000 in Celsius, I’m pretty sure), here we are driving down the highway in a Miata with the top down and my wife’s arms wrapped around this box (and mind you, the box was bigger around than she is, so those arms don’t quite get all the way around that box) sitting in the passenger’s seat. Good times.

Now, in fairness to that car, you can fit a lot of stuff in a Miata trunk. Another time, we’d bought a large area rug for our dining room, as well as a lengthy floor runner. When we made the purchase, I thought it was going to be delivered, but nope. I did learn that day, however, just how compressible a rug is, as they proceeded to bind that thing so together that it fit comfortably into the trunk, where by “comfortably” I mean “barely but I’m trying to sell up how large this trunk is.” As for the floor runner, we had to cram it into the space between the headrests on our seats and the raised top, where it just barely fit. Sure, I couldn’t see behind me, but winners never look back.

Erik Darling continues a series on interesting wait stats:

Hash spills are, as we’ll see, sometimes identified by a different wait than sort spills. In small quantities, spills are often not worth bothering with. But when they pile up, they can really cause some severe performance issues.

In this post, I want to show that both Hash Aggregate and Joins can cause the same wait type to show, along with some evidence that strings make things worse.

Click through for a good explanation of the wait stat you’re liable to see the most.

Erik Darling starts a new series on wait stats, starting with one particular topic:

The point is not that spills are the sole things that cause these waits, it’s just to give you some things to potentially watch out for if you see these waits piling up and can’t pin down where they’re coming from.

In all the queries, I’m going to be using the MAX_GRANT_PERCENT hint to set the memory grant ridiculously low to make the waits I care about stick out.

Click through for the first of several demonstrations.

Chad Callihan recommends not messing with worker threads:

Our story begins on a test server hosting a couple hundred databases. Over time, the server kept getting slower and slower when trying to navigate in SSMS. Query windows would take too much time to load, logging in would lag a bit, etc. When investigating with sp_Blitz, I found that the server had been experiencing Threadpool waits.

There are some cases where increasing thread count is important, especially when you’re working with database mirroring or availability groups. I worked with a customer with thousands of mirrored databases per server. None of the databases were particularly large or heavily-used, so it was on properly-sized hardware. As a result, to prevent the server from falling over due to threadpool waits, we had to scale thread counts to scary-high levels.