This article is contributed. See the original author and article here.
First published on MSDN on Jan 01, 2013
The Memory Consumer with Many Names
Have you ever wondered what Memory grants are? What about QE Reservations ? And Query Execution Memory ? Workspace memory ? How about Memory Reservations ?
As with most things in life, complex concepts often reduce to a simple one: all these names refer to the same memory consumer in SQL Server: memory allocated during query execution for Sort and Hash operations (bulk copy and index creation fit into the same category but a lot less common).
Allow me to provide some larger context: during its lifetime a query may request memory from different “buckets” or clerks, depending on what it needs to do. For example, when a query is parsed and compiled initially, it will consume compile or optimizer memory. Once the query is compiled that memory is released and the resulting query plan needs to be stored in cache. For that, the plan will consume procedure cache memory and will stay in that cache until server is restarted or memory pressure occurs. At that point, the query is ready for execution. If the query happens to be doing any sort operations or hash match (join or aggregates), then it will first reserve and later use part or all of the reserved memory to store sorted results or hash buckets. These memory operations during the execution of a query are what all these many names refer to.
Terminology and Troubleshooting Tools
Let’s review the different terms that you may encounter referring to this memory consumer. Again, all these describe concepts that relate to the same memory allocations:
Query Execution Memory (QE Memory): This term is used to highlight the fact that sort/hash memory is used during the execution of a query and is the largest memory consumption that may come from a query during execution.
Update(9/17): QE Memory is the very type of memory that Resource Governor actually limits, when used. See Resource Pools Max and Min Memory percent
Query Execution (QE) Reservations or Memory Reservations: When a query needs memory for sort/hash operations, during execution it will make a reservation request based on the original query plan which contained a sort or a hash operator. Then as the query executes, it requests the memory and SQL Server will grant that request partially or fully depending on memory availability. There is a memory clerk (accountant) named ‘MEMORYCLERK_SQLQERESERVATIONS’ that keep track of these memory allocations (check out DBCC MEMORYSTATUS or sys.dm_os_memory_clerks).
Memory Grants: When SQL Server grants the requested memory to an executing query it is said that a memory grant has occurred. There is a Perfmon counter that keeps track of how many queries have been granted the requested memory: Memory Grants Outstanding . Another counter shows how many queries have requested sort/hash memory and have to wait for it because the Query Execution memory has run out (QE Reservation memory clerk has given all of it away): Memory Grants Pending . These two only display the count of memory grants and do not account for size. That is, one query alone could have consumed say 4 GB of memory to perform a sort, but that will not be reflected in either of these.
To view individual requests, and the memory size they have requested and have been granted, you can query the sys.dm_exec_query_memory_grants DMV. This shows information about currently executing queries, not historically.
In addition, you can capture the Actual Query Execution plan and find an XML element called <Query plan> which will contain an attribute showing the size of the memory grant (KB) as in the following example:
<QueryPlan DegreeOfParallelism=”8″ MemoryGrant =”2009216″
Another DMV- sys.dm_exec_requests – contains a column granted_query_memory which reports the size in 8 KB pages. For example a value of 1000 would mean 1000 * 8 KB , or 8000 KB of memory granted.
Workspace Memory: This is yet another term that describes the same memory. Often you will see this in the Perfmon counter Granted Workspace Memory (KB) which reflects the overall amount of memory currently used for sort/hash operations in KB. The Maximum Workspace Memory (KB) accounts for the maximum amount of workspace memory ever used since the start of the SQL Server. In my opinion, the term Workspace Memory is a legacy one used to describe this memory allocator in SQL Server 7.0 and 2000 and was later superseded by the memory clerks terminology after SQL Server 2005.
Resource Semaphore: To add more complications to this concept, SQL Server uses a thread synchronization object called a semaphore to keep track of how much memory has been granted. The idea is this: if SQL Server runs out of workspace memory/QE memory, then instead of failing the query execution with an out-of-memory error, it will cause the query to wait for memory. In this context, the Memory Grants Pending Perfmon counter makes sense. And so do wait_time_ms , granted_memory_kb = NULL, timeout_sec in sys.dm_exec_query_memory_grants . BTW, this and compile memory are the only places in SQL Server where a query will actually be made to wait for memory if it is not available; in all other cases, the query will fail outright with a 701 error – out of memory.
There is a Wait type in SQL Server that shows that a query is waiting for a memory grant – RESOURCE_SEMAPHORE. As the documentation states, this “occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.” You will observe this wait type in sys.dm_exec_requests for individual sessions. Here is a KB article written primarily for SQL Server 2000 which describes how to troubleshoot this issue and also what happens when a query finally “gets tired” of waiting for a memory grant.
Why do you Care About Memory Grants or Workspace Memory or Query Execution Memory, or whatever you call it?
Over the years of troubleshooting performance problems, I have seen this to be one of the most common memory-related issues. Applications often execute seemingly simple queries that end up wreaking tons of performance havoc on the SQL Server side because of huge sort or hash operations. These not only end up consuming a lot of SQL Server memory during execution, but also cause other queries to have to wait for memory to become available – thus the performance bottleneck.
Using the tools I have outlined above (DMVs, Perfmon counters and actual query plan), you can investigate which queries are large-grant consumers and can have those tuned/re-written where possible.
What Can a Developer Actually Do about Sort/Hash Operations?
Speaking of re-writing queries, here are some things to look for in a query that may lead to large memory grants.
Reasons why a query would use a SORT operator (not all inclusive list):
ORDER BY (T-SQL)
GROUP BY (T-SQL)
DISTINCT (T-SQL)
Merge Join operator selected by the optimizer and one of the inputs of the Merge join has to be sorted because a clustered index is not available on that column.
Reasons why a query would use a Hash Match operator (not all inclusive list):
JOIN (T-SQL) – if SQL ends up performing a Hash Join. Typically, lack of good indexes may lead to the most expensive of join operators – Hash Join. Look at query plan.
DISTINCT (T-SQL) – a Hash Aggregate could be used to perform the distinct. Look at query plan.
SUM/AVG/MAX/MIN (T-SQL)– any aggregate operation could potentially be performed as a Hash Aggregate . Look at query plan.
UNION – a Hash Aggregate could be used to remove the duplicates.
Knowing these common reasons can help an application developer eliminate, as much as possible, the large memory grant requests coming to SQL Server.
As always, basic query tuning starts with checking if your queries have appropriate indexes to help them reduce reads, minimize or eliminate large sorts where possible.
Update : Since SQL Server 2012 SP3, there exist a query hint that allows you to control the size of your memory grant. You can read about it in New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012 . Here is an example
SELECT * FROM Table1 ORDER BY Column1 OPTION (min_grant_percent = 3, max_grant_percent = 5 )
Memory Grant Internals:
Here is a great blog post on Memory Grant Internals
Summary of Ways to Deal with Large Grants:
- Re-write queries
- Use Resource Governor
- Find appropriate indexes for the query which may reduce the large number of rows processed and thus change the JOIN algorithms (see Database Engine Tuning Advisor and Missing Indexes DMVs)
- Use OPTION (min_grant_percent = XX, max_grant_percent = XX ) hint
- SQL Server 2017 and 2019 introduce Adaptive query processing allowing for Memory Grant feedback mechanism to adjust memory grant size dynamically at run-time.
Namaste!
Joseph
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments