Fix RESOURCE_SEMAPHORE

Question

I ran sp_Blitz and on the top I got:

Finding: Poison Wait Detected: RESOURCE_SEMAPHORE

Details: 0:12:36:38 of this wait have been recorded. This wait often indicates killer performance problems. This means that SQL Server ran out of available memory to run queries, and queries had to wait on available memory before they could even start…

My questions are: -Is the time in minutes? Is it a number to worry? Can I conclude just by this that I need to buy more memory for the server?

(Server has 30GB installed RAM).

Instance has only been up for 2 weeks… EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

EXEC sp_BlitzFirst @SinceStartup = 1

Where should I redirect my efforts?

Answer 1

Thanks for using sp_Blitz!

RESOURCE_SEMAPHORE is indeed a tough wait to run into.

-Is the time in minutes?

The time is DD:HH:MM:SS, so you've had about 12 hours of it total.

Is it a number to worry?

Your server has spent 12 hours waiting to get enough memory to run queries. That means queries that appear to be running are actually sitting idle waiting to get memory. How long has your server been up for?

Can I conclude just by this that I need to buy more memory for the server?

That's one 'easy' way to deal with it, but you need to dig into what's going on when that wait type pops up.

For that, you'll want to try using sp_BlitzFirst.

To get total metrics since startup:

EXEC sp_BlitzFirst @SinceStartup = 1

To sample metrics during a 30 second window:

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

You could just plain not have enough memory here -- 30 GB of memory isn't much (for instance, my laptop has 32 GB of memory just to look at gifs and have 100 Chrome tabs open).

How much data are you storing on there?

You could also have awful queries using ridiculous amounts of memory.

To dig into those (if you're on a pretty recent build of a fairly modern version of SQL Server), you could run

EXEC sp_BlitzCache @SortOrder = 'memory grant' to find queries with large memory grants.

Hope this helps, and thanks again!