Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues

MSSQLTips author Manvendra Singh By:   |   Read Comments (26)   |   Related Tips: More > Performance Tuning
Problem

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

Solution

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.

Identify RESOURCE_SEMAPHORE Waits

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.

SELECT * FROM SYSPROCESSES
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_semaphore

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)

Conclusion

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.


Last Update: 1/4/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, November 26, 2014 - 7:21:23 AM - Martin Read The Tip

Great article, thank you very much!


Tuesday, August 19, 2014 - 2:24:45 PM - Nama Read The Tip

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 Read The Tip

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


Tuesday, July 01, 2014 - 1:16:09 AM - Pooja Read The Tip

Excellent article. Very easy to understand ! 


Wednesday, June 25, 2014 - 12:40:42 PM - Ron Read The Tip

This was a really life-saver last night.


Monday, September 23, 2013 - 4:43:40 PM - Suc Read The Tip

Excellent and very helpful!


Tuesday, September 10, 2013 - 1:46:24 PM - sql_sasquatch Read The Tip

"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 :)
http://sql-sasquatch.blogspot.com/2013/07/pending-query-memory-grant-requests.html 


Monday, June 24, 2013 - 5:54:34 AM - sultan Read The Tip

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 Read The Tip

Very nice article. Very important even for developers.


Friday, January 25, 2013 - 10:59:13 AM - Juanita Read The Tip

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 Read The Tip

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

Thanks,

Varun 


Wednesday, January 23, 2013 - 3:51:14 AM - Sandy Read The Tip

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. 

Thanks


Monday, January 14, 2013 - 12:18:00 AM - Yusuf Read The Tip

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 09, 2013 - 8:57:32 PM - mq44944 Read The Tip

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 09, 2013 - 8:31:59 AM - Manvendra Read The Tip

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 09, 2013 - 2:05:32 AM - mq44944 Read The Tip

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 08, 2013 - 12:14:18 AM - Gopalakrishnan Arthanarisamy Read The Tip

Excellent one. Hats off to You.


Monday, January 07, 2013 - 1:38:34 PM - Bijal Read The Tip

Great one manvender!!!


Monday, January 07, 2013 - 3:39:15 AM - Srinath Read The Tip

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


Sunday, January 06, 2013 - 2:25:25 PM - Anand Read The Tip

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


Sunday, January 06, 2013 - 10:02:01 AM - Ajay Read The Tip

Thanks for Information.


Sunday, January 06, 2013 - 12:17:58 AM - Kumanan Read The Tip

Simple and good


Friday, January 04, 2013 - 9:13:04 PM - Som Pichai Read The Tip

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


Friday, January 04, 2013 - 3:34:42 PM - Deepak Kumar Read The Tip

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.

 

Thanks

Deepak


Friday, January 04, 2013 - 12:55:33 PM - MaryAnn Read The Tip

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


Friday, January 04, 2013 - 10:03:58 AM - Ranga Read The Tip

Very well written. Thanks for sharing.




 
Sponsor Information