Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues

By:   |   Comments (39)   |   Related: > Performance Tuning


Today, one of our SQL Server instances was performing very slowly. When I logged in to the database server to do some initial checks I noticed it was memory pressure from the initial observation. Next we had to find what out was causing our instance to have memory pressure. When I checked the wait types for the transactions, the RESOURCE_SEMAPHORE wait was the issue for most of the transactions. In this tip I will describe this issues and how to find which query or transaction is causing the memory pressure


When I checked the wait types for all transactions, the RESOURCE_SEMAPHORE wait was the wait type for most of the transactions in addition to some page IO waits. The page IO waits were also due to memory pressure because those transactions were not able to get enough memory to perform their operation.

Resource Semaphore Wait

Before moving on, I would like to shed some light on the Resource Semaphore wait so you can better understand how memory is granted to SQL Server queries.

When SQL Server receives a user query, it first creates a complied plan, then an execution plan is created based on the complied plan. When SQL Server creates a complied plan it calculates two memory grant parameters called "required memory" and "additional memory". Required memory is the minimum memory needed to run a sort and hash join. It is known as required because a query would not start without this memory available. Additional memory is the amount of memory needed to store temporary rows in memory. This is known as additional because a query can be stored on disk if there is not enough memory available.

First, the server calculates how much memory is needed for any given query to execute. This is generally the sum of required memory and additional memory, but if your instance is using parallelism then the needed memory would be (required memory * DOP) + additional memory. The server checks if the needed memory exceeds the per query limit, then the server reduces additional memory until the total fits within the limit. This revised size is called requested memory. There is an internal facility within SQL Server known as RESOURCE SEMAPHORE which is used to grant this requested memory to a query. If query is not able to be granted this requested memory by a Resource Semaphore, then that query will be in a waiting state with a RESOURCE_SEMAPHORE wait type if you query the sysprocesses system table or sys.dm_exec_request DMV.

When a Resource Semaphore receives a new request, it first checks if any query is waiting or not. If it finds one, it puts the new query in the queue because the wait queue is designed on a first-come-first-served basis with a small weighting to favor small queries. Resource Semaphore attempts to grant memory when there is no waiting query or when a query returns reserved memory. If it finds enough memory, then the requested memory is granted and the query can start running and if it does not find enough free memory to grant the requested memory then it puts the current query into the waiting queue with a RESOURCE_SEMAPHORE wait type and your server starts facing memory pressure.


Step 1

First, we need to look into our instance to see why memory pressure is occurring within SQL Server. To get an initial overview of all transactions, we can query sysprocesses or we can use the sys.dm_exec_requests DMV.

ORDER BY lastwaittype

Here we can see processes that have a RESOURCE_SEMAPHORE wait type.

Run sysprocesses to get about all waittypes

Step 2

From the above query, we can see a large number of transactions are waiting with Resource Semaphore wait type. Now we can run the below query to see the status of total number of queries which have been granted memory and the number of queries which have not yet been granted memory.

The output of this DMV returns two rows, one for large queries (resource_semaphore_id is 0) and another one for small queries (resource_semaphore_id is 1) specially less than 5 MB. Here you can get the total granted memory and total available memory for the instance. See the numbers on grantee_count and waiter_count, the grantee_count is the number of queries which have their memory and the waiter_count is the number of quires which are waiting in queue to get memory. So here we can see approximately 100 queries are waiting to get their requested memory.

SELECT * FROM sys.dm_exec_query_resource_semaphores

Find the no of queries in waiting to get memories

Step 3

Now we will get the details of all queries which are waiting in queue to get their requested memory. We will use DMV sys.dm_exec_query_memory_grants to get the total number of queries which are waiting in queue to get their memory along with the details. The columns grant_time and granted_memory_kb will be NULL for those queries which are waiting to get their requested memory. You can see in the below screenshot the requested memory amount and their waiting state because their grant_time and granted_memory_kb value is NULL.  We can also get the plan_handle and sql_handle of all queries with this DMV. We will use these values later to get the exact queries.

Note: there are too many columns to show, so this is just a partial listing of all columns.

SELECT * FROM sys.dm_exec_query_memory_grants

Details of all waiting quries

Step 4

Now we will find the memory intensive queries. We can see the requested memory for all waiting queries. Here we can see the requested memory is too large for most of the transactions. We will get the plan_handle of all these queries to get the exact SQL text to look into the query plan.

select top 10 * from sys.dm_exec_query_memory_grants

Find plan_handle of memory intensive queries to get the sql code

Step 5

Now we will use the above plan_handle and sql handle to get the SQL code.

Run the below statement to get the SQL code using the sql_handle from the above query.

SELECT * FROM sys.dm_exec_sql_text(sql_handle)

Find the SQL code

We can also get the SQL plan using the plan_handle from the query in Step 4.

SELECT * FROM sys.dm_exec_sql_plan(plan_handle)


Now that we have found the memory intensive queries and their execution plans, our next step is to look into these queries and figure out how to tune them.  We should look into bad or missing indexes used in the query and implement proper indexing.  In our case, it was bad indexes which were causing the memory pressure. After implementing proper indexing the same query was running with much less requested memory.

Next Steps
  • Use this tip to identify the queries which are consuming more memory and putting remaining transactions into wait state due to lack of memory.
  • Also look at the other columns of the above DMVs and correlate them to each other for better analysis and understanding of performance issues.
  • These DMVs should provide an ample amount of information so you can identify the issue.
  • Read more tips on Performance Tuning to improve your system performance.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, January 5, 2024 - 6:55:15 AM - Mohit Kumar Back To Top (91832)
Yes, We performed similar actions and found extensive queries for further tuning, for Temporary Fix, We initiated a manual failover on SQL Managed Instance and resolved the same

Tuesday, January 21, 2020 - 6:06:23 AM - umasankar Back To Top (83906)

Good article.

Monday, December 16, 2019 - 11:52:05 PM - Javier Back To Top (83443)

Great article, just one question if queries with Resource_semaphore are waiting for memory, the queries that you get when running step 3 are the bigger queries waiting for memory but the problem is there is no memory because other queries are using it.

Wouldn't be better to chase queries with great amount of memory "granted" instead of "wating"?


Thursday, March 21, 2019 - 6:22:27 AM - Matt Back To Top (79359)

Great article - thank you very much

I think there's a minor typo

SELECT * FROM sys.dm_exec_query_resource_semaphore

should be

SELECT * FROM sys.dm_exec_query_resource_semaphores

Wednesday, March 20, 2019 - 10:42:34 PM - Vamsi Back To Top (79357)

Superb article and good explanation in details. Crystal clear sir.

Wednesday, June 6, 2018 - 5:11:27 PM - Richard Back To Top (76138)


Great article, thanks.

Friday, September 15, 2017 - 9:41:43 AM - K Prasad Back To Top (66313)

Great Article, this article is more helpfull. 


Friday, April 21, 2017 - 12:23:20 PM - Fraz Back To Top (55083)

 Great article Manvendra, thanks for sharing your experience with DBA community.  


Thursday, October 20, 2016 - 11:14:39 AM - AZJim Back To Top (43602)

 Very informative.  Thank you for this post.


Friday, May 13, 2016 - 6:16:04 AM - Rajender Sharma Back To Top (41480)


 Excelent Article that help a lot of many DBA's.

Thursday, November 5, 2015 - 12:06:16 AM - Rajj Back To Top (39024)

Super explaination .Thanks a ton.

Tuesday, February 3, 2015 - 7:47:46 AM - paul Back To Top (36143)

Thanks Manvendra, just fixed an issue using the tips in your post.

Friday, December 19, 2014 - 12:51:45 PM - Gary Hamrick Back To Top (35690)

very helpful to a problem I was working on. thanks for spending the time to write this up !

Wednesday, November 26, 2014 - 7:21:23 AM - Martin Back To Top (35413)

Great article, thank you very much!

Tuesday, August 19, 2014 - 2:24:45 PM - Nama Back To Top (34198)

what are your initial checks to conclude this is a memory pressure issue ? Can some one explain on what steps to do to arrive as memory pressure is causing the slowness of SQL server

Monday, July 28, 2014 - 1:13:56 AM - Siva Back To Top (33899)

Nice article Manvendra.. Very useful.. Thanks for knowledge sharing..

Tuesday, July 1, 2014 - 1:16:09 AM - Pooja Back To Top (32483)

Excellent article. Very easy to understand ! 

Wednesday, June 25, 2014 - 12:40:42 PM - Ron Back To Top (32398)

This was a really life-saver last night.

Monday, September 23, 2013 - 4:43:40 PM - Suc Back To Top (26902)

Excellent and very helpful!

Tuesday, September 10, 2013 - 1:46:24 PM - sql_sasquatch Back To Top (26710)

"First, we need to look into our instance to see why memory pressure is occurring within SQL Server."
In order to prevent misundertanding, I usually point out that "pending query memory grants" are not truly memory pressure, but more something like "memory promise pressure".  The entire memory grant isn't used right away by the grantee, and may never fully be used.  

I like to point this out because sometimes folks will assume that "memory pressure" can be alleviated by adding physical memory: sometimes adding physical memory and increasing max server memory leads to the offending queries simply requesting even larger query memory grants, with the number of pending query memory grants remaining the same.  Overall performance may improve due to increased database cache and decreased query memory spill, but the most significant performance bottleneck on such a system are the queries requesting huge query memory grants :) 

Monday, June 24, 2013 - 5:54:34 AM - sultan Back To Top (25535)

we have SQL 2012 server. where we try to create index, it took more time and process went suspended mode with lastwaittype RESOURCE_SEMAPHORE .

While we are checking this server using Resource Governer feature. where 2 user resoruce pool min_memory_percent  allocated with 50 % and 50 % .  No minimum memory left out for default pool.

you can check the values by  executing Select * from sys.resource_governor_resource_pools

Later one user resource pool  min_memory_percent reduced to 25 % and 25 % left out for default and internal. Then the index creation completed with in 6 minutes.

Thursday, May 30, 2013 - 10:06:30 AM - Gangadhar Back To Top (25200)

Very nice article. Very important even for developers.

Friday, January 25, 2013 - 10:59:13 AM - Juanita Back To Top (21705)

Thank you so much for this article! I was searching for information on this very topic.

Thursday, January 24, 2013 - 5:03:42 AM - Varun Back To Top (21675)

Hi Manvendra,


Does this work only in SQL server 2008 and later verisons... I tried this in 2005 but couldn't see some of othe dm views (I might be wrong)..

BTW .. very nice article....



Wednesday, January 23, 2013 - 3:51:14 AM - Sandy Back To Top (21637)

This is better understandable article for every one. Even i was well settled in good DBA position,this article helps me to recollect useful stuff. 


Monday, January 14, 2013 - 12:18:00 AM - Yusuf Back To Top (21431)

Hi mq44944,

I guess you are looking for an option to configure the max memory per query (like min memory per query option) which is set by memory broker as about 90% of server memory but there is no way to set this limit. However in SQL 2008/2012 enterprise edition you can use resource governor to achieve this. There you can set the request_max_memory_grant_percent to set the memory (this is the percent relative to the pool size specified by the pool's max_memory_percent value). This setting in not query specific, it is session specific

Wednesday, January 9, 2013 - 8:57:32 PM - mq44944 Back To Top (21359)

Hi Manvendra,

  Thank you for your reply !  

  As a DBA, when I explain to the developers why the sqls runs slow sometimes ,I say the sql query is waiting for query memory. then the developer ask why I don't set enough  query memory. Actually I have no way to know where I can set a upper limit for the query memory.  Sometimes I  make the max memory size bigger, this makes less waiting on  resource_semaphore. 


Wednesday, January 9, 2013 - 8:31:59 AM - Manvendra Back To Top (21341)

Thanks everyone for such a valuable feedback!!


@mq44944- As per my understaing about your question is, you want to know how much memory a particular query is taking? or how much memory all queries is taking?

For above both questions, read step3 carefully, You can use sys.dm_exec_query_memory_grants DMV to look into the memory granted/required to every query.

Wednesday, January 9, 2013 - 2:05:32 AM - mq44944 Back To Top (21333)

Great and well explained article.

I had been trying to find how much memory can be used as query memory but failed. Do you know this. eg, with a 4GB memory sqlserver instance, how much memory will be used as query memory.

Tuesday, January 8, 2013 - 12:14:18 AM - Gopalakrishnan Arthanarisamy Back To Top (21308)

Excellent one. Hats off to You.

Monday, January 7, 2013 - 1:38:34 PM - Bijal Back To Top (21303)

Great one manvender!!!

Monday, January 7, 2013 - 3:39:15 AM - Srinath Back To Top (21289)

Its definitely a great article. Well, please correct the misspelled word - "complied" which should be "compiled". Thanks.

Sunday, January 6, 2013 - 2:25:25 PM - Anand Back To Top (21279)

Great and well explained........

Sunday, January 6, 2013 - 10:02:01 AM - Ajay Back To Top (21277)

Thanks for Information.

Sunday, January 6, 2013 - 12:17:58 AM - Kumanan Back To Top (21274)

Simple and good

Friday, January 4, 2013 - 9:13:04 PM - Som Pichai Back To Top (21266)

Well written and easy to understand. Good Job! Thanks!

Friday, January 4, 2013 - 3:34:42 PM - Deepak Kumar Back To Top (21265)

What a great article it is. I have been looking for this long time. Manvendra you did a great job. It would definately going to help DBA's to resolve their problem regarding memory Pressure. We are looking forward such kind of great article from your side near future too.




Friday, January 4, 2013 - 12:55:33 PM - MaryAnn Back To Top (21263)

This is great!  Very informative and well-explained!  THANKS SO MUCH!

Friday, January 4, 2013 - 10:03:58 AM - Ranga Back To Top (21261)

Very well written. Thanks for sharing.

get free sql tips
agree to terms