Handling RESOURCE_SEMAPHORE Waits

We’ve reached November 2021. If you haven’t noticed by now, that means more Black Friday advertising.

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).

Maybe you need a bigger car or maybe you should stick with some dumbbells instead.

Never Enough

I mentioned above that RESOURCE_SEMAPHORE waits occur when SQL Server does not have enough memory to execute a query. If I’m seeing this wait for the first time on a server, I’m going to ask myself a couple of questions.

First, is this a brand new server? It’s possible that the server was not built with an adequate amount of memory to handle the load. I’d also be curious to know if the server is solely for SQL Server or if we’re using it as a web server for applications. In other words, do I need an SUV instead of my sedan or do I need to get rid of the junk piling up in the back seat that shouldn’t be there?

If the server has been around for awhile then it’s probably time for some query tuning. We don’t want SQL Server requesting way more memory than needed when a query executes. You might get by with adding an index to prevent an excessive memory grant. If that doesn’t lead to an acceptable plan then the best option could be rewriting the query. It’s always a smarter decision to not spend resources on more than we need.

There’s not one guaranteed quick fix. Adding more memory is likely to be a sledgehammer approach. Don’t just take the easy route and leave poor queries.

Check for Waits

My favorite way to check top waits on a server is to use sp_BlitzFirst from the First Responder Kit:

sp_BlitzFirst @OutputType = 'Top10'

That will give you the top 10 waits so you know what to focus on.

You can run the following to sort BlitzCache results by memory grant to help track down queries causing RESOURCE_SEMAPHORE:

sp_BlitzCache @SortOrder = 'memory grant'

Deals on Things You Don’t Need

Are you all set on RESOURCE_SEMAPHORE? Time to go check out those Black Friday deals.

Thanks for reading!

One thought on “Handling RESOURCE_SEMAPHORE Waits”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s