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.
Identify RESOURCE_SEMAPHORE Waits
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.
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
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
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
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)
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.
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.
"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
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
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
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.