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.