Memory Meditation: Multiple names related to the mysterious SQL Server memory consumer.

Original: Memory Meditation: Multiple names related to the mysterious SQL Server memory consumer.

Original source: https://blogs.msdn.microsoft.com/sqlmeditation/2013/01/01/ memory-meditation-the-mysterious-sql-server-memory-consumer-with-many-names/

For multiple memory consumers with different names

Do you ever wonder what memory grants are (memories grants)?
What is the reserved (scheduled) memory for query execution (QE reservations)?
and query execution memory (query execution)?
Workspace memory (Workspace memories), as well as memory retention (translator Note: Many places will be reservations translated "reserved", personally think the translation "book" Once easier to understand, the following book synonymous with "retention")

It's like a lot of things in life, in short, one sentence: So the name points to the same memory consumer in SQL Server,
Memory allocations for sorting or hashing operations (including bulk copy and index creation, etc.) during the query
Allow me to come up with a bigger topic, in the course of a query execution, the query may request memory from different "buckets" or clerks, (differences from different places) depend on what the requested memory is used for.
For example, when a query is initially parsed and compiled, he needs to consume the compilation or optimize the memory. Once the query statement has been compiled, the memory he is applying for will be released.
At the same time its corresponding query execution plan will be cached, for this reason, the execution plan consumes process cache memory, and the memory is occupied until the server restarts or memory pressure, at this time, the query is ready to execute.
If a query statement is sorted or hashed (concatenated or combined), then it prefers to use the intended memory to store the result or store the hash bucket (the result)
This part of the memory occurs during query execution, which involves a lot of memory names.

Terminology and troubleshooting tools

Let's review the different categories you might encounter about this memory consumer.
Again, all of these describe the concepts associated with the same memory allocation:

Query execution memory (QE memory):
This is used to emphasize the fact that sort/hash memory is used during query execution and is the largest portion of memory consumption during query execution.

Query memory (QE) bookings or memory bookings:
When a query requires the Sort/hash operation to prepare memory, during execution, he will execute a memory reservation request (memory reservation request) based on the original query plan containing the sort or hash operation.
Then the query starts executing, he requests the memory, and SQL Server will grant the query some or all of the memory requests, mainly relying on the server available memory
There is a memory clerk called Memoryclerk_sqlqereservations, this memory counter to keep this part of the memory allocation situation.

------------------------------Translator's note---------------------------------
This part of the memory can be queried from the sys.dm_os_memory_clerks, the following is a server with a load of memoryclerk_sqlqereservations distribution on the server
Visible this part of the memory is still not small, here is more than 4GB memoryclerk_sqlqereservations (server memory 32GB)
If a server does not have the load, the use of this memory is very low, even can be 0

　　

--------------------------------------------------------------------------

Memory Grant (Grants):
When SQL Server grants request memory to a request, it is called the memory grant has occurred,
There is a performance counter that retains how much memory has been granted to the requested memory: Grants outstanding
Another counter indicates how many queries have requested Sort/hash memory, but is out of wait because the query execution memory is exhausted: Grants Pending
These two memory counters show insufficient memory grants and memory grants, which means that a single query may consume 4GB of memory to perform a sort, but neither of these will be reflected

------------------------------Translator's note---------------------------------
This part of memory can be understood as real-time request memory, the more SQL Server real-time requests, this portion of memory is larger,
For example, a session needs 20MB of running memory, while running 10 sessions will require 200MB, while running 100 sessions will require 2000MB, of course, each session needs to run memory is different (memory grant), Here is just an example
Can be queried from Sys.dm_os_memory_clerks, as follows is the memoryclerk_sqlqereservations distribution on the server of a server that has a load
Visible this part of the memory is still not small, here is more than 4GB memoryclerk_sqlqereservations (server memory 32GB)
If a server does not have the load, the use of this memory is very low, even can be 0
Memory Grants outstanding A session can not get its request for the RAM, in the waiting memory state,
SQL Server is a self tuning engine that is generally reserved enough that normally does not occur because SQL execution lacks memory and waits for the situation
All things scored clear priorities, memory is also divided into urgent and non-urgent need, not to cache data over there to occupy memory, and this side even real-time requests can not respond to it.
To cite an inappropriate example, and then poor, can not buy a house do not buy a car, always leave enough money to eat.
Memory in other places can be stressful (for example, the data cache can be quickly swapped out of memory), but in the face of the corresponding real-time requests of memory, SQL Server is still relatively sufficient, the individual rarely see because the session is waiting for memory to be unable to execute the situation.
But words can not be reversed, can not say that there is no memory Grants Pending, it means that there is no pressure.

　　

----------------------------------------------------------------------------

In order to observe individual requests and the memory they have applied for, you can query sys.dm_exec_query_memory_grants this DMV
This DMV shows the memory grant of the currently running query, not the historical situation
In addition, you can capture the actual query execution plan and query its XML node called query plan, which contains the size of the memory grant.
The following example:
<queryplan degreeofparallelism= "8" memorygrant= "2009216"
Another DMV is sys.dm_exec_requests, which contains a column called Granted_query_memory, which is a unit of 8KB,
A value of 1000, for example, means 1000*8kb, or 8000kb of memory grant

Working space memory (Workspace Memories):

This is still another project that describes the same memory,
You will often see this performance technology Zone Grant workspace memory (KB), reflecting the current use of all sort/hash operations
The maximum amount of work space memory (Maximum Workspace Memories (KB)) is the maximum working space memory since SQL Server started.
In my opinion, the workspace Memory project is a legacy issue that describes the memory allocations of sqlserver7.0 sqlserver2000, which has been invalidated after SQLServer2005

Resource semaphore (Resource Semaphore):
To add more complexity to this concept, SQL Server uses a thread synchronization object called semaphore (semaphore) to track how much memory has been granted.
The idea is this: If SQL Server runs out of workspace memory/qe memory, use the out-of-memory error to replace the query execution failure (not the direct feedback query failure, but wait for the memory)
It will cause the query to wait for available memory and then (after getting available memory) can be re-executed

In this process, the Memory Grants Pending performance counters begin to make sense.
Therefore, wait_time_ms,granted_memory_kb = NULL is generated in sys.dm_exec_query_memory_grants, timeout_sec
By the way, this and the compiled memory are the only ones in SQL Server that are running out of memory and will execute two memory waits.
In other cases, the query will fail directly and report a 701 error--out of memory.

Another wait type in SQL Server also indicates that a query is waiting for memory grant--resource_semaphore
As the document says, this happens when a query memory cannot be granted immediately due to other concurrently executing queries.
Frequent wait and wait times can indicate this excessive concurrent query, or excessive memory grant count.
You can watch the session level waiting in the sys.dm_exec_requests DMW.

Why pay attention to memory Grants or Workspace memory or Query execution memory or whatever you call it.

In the last few years of diagnosing performance problems, I've found that this is one of the most common memory-related problems that applications often perform in seemingly simple queries.
Has suffered a lot of performance damage because of the large number of sort or hash operations performed on it.
Those queries not only consume a lot of memory during execution, but also cause other queries to wait for memory, which is a performance bottleneck.

Using the tools I provided above (Dwvs, performance counters and actual execution plan), you can find out which query is consuming a lot of memory and then consider the possibility of optimization/rewriting

Sort/hash actions generated by the scene

Mentions query rewriting, here are some scenarios that could lead to a lot of memory grants

The reason for the sort operation (including but not limited to the following scenarios)
ORDER by (T-SQL)
GROUP by (T-SQL)
DISTINCT (T-SQL)
The merge Join operation is selected by the optimizer, where one input of the merge join must be sorted

The cause of the Hash match operation (including but not limited to the following scenarios)
Join (T-SQL) – A costly join operation that is typically caused by a lack of reasonable indexing, if a hash operation is performed by the--hash join, observing the execution plan
DISTINCT (T-SQL) –hash aggregation may be used to perform DISTINCT operations and observe execution plans
Sum/avg/max/min (T-SQL) – Any aggregation operation may result in hash aggregation, observing the execution plan
Union–hash aggregation may be used to remove duplicates

Understanding these common causes can help application developers to eliminate a large number of memory grant requests to SQL Server as much as possible.
As always, basic query tuning begins with checking that the query has an appropriate index to help them reduce reads, minimize or eliminate large sorts.

Here is a memory-granting blog post for reference http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

Memory Meditation: Multiple names related to the mysterious SQL Server memory consumer.