No tags yet.
I know, it sounds like it could be a desert. But it's poison!
You ever have one of those Saturday morning emergencies and come face to face with a wait type that you're completely unfamiliar with?
I recently got contacted that a highly transactional SQL Server was performing slowly. I noticed some memory pressure, but when I dove into wait types, I found that many of the transactions were waiting on the RESOURCE_SEMAPHORES wait.
What is it?
Basically it's when a query is waiting for an execution memory grant so that it can proceed. This becomes problematic when there are either many concurrent queries or excessive memory requests going on. So a high wait value of RESOURCE_SEMAPHORE is due to queries not being able to get the memory that they need to execute.
What do we do?
A good place to start would be observing what is currently running on the server. Either query the sysprocesses (maybe order by lastwaittype). Or look at the currently running sessions (I highly recommend sp_whoisactive).
Then, luckily for us, this wait type has it's own DMV. So query against sys.dm_exec_query_resource_semaphores. You can see in the waiter_count column how many queries are waiting. (zero in my screen shot, because I have nothing going on )
Next, let's see the most memory intensive queries. You can do it with one of the many available scripts available on the internet. Almost all of them will involve sys.dm_exec_sql_text, sys.dm_exec_sql_handle and/or sys.dm_exec_query_memory_grants. You can use the latter to get the plan handle and feed that into the first 2 DMFs.
The query(s) might lead to you to have to adjust or created indexes. You may find some cardinality issues. Or you might just see a huge number of concurrent queries.