Understanding SQL Server's Query Memory grant (translated)

This article describes how the query memory grant, which works on SQL Server, applies to SQL 2005 through 2008.

Query Memory Grants (hereinafter abbreviated as QMG) are used to store temporary intermediate data rows when data is sorted and concatenated. A query needs to reserve memory before it is actually executed, so there is a grant action.

The benefit of this is to improve the reliability of the query and avoid consuming all of the memory for a single query.

When SQL Server receives a query, it performs 3 defined steps to return the result set requested by the user.

1. Generate a compilation plan. It includes a variety of logic directives, such as how to join data rows.

2. Generate an execution plan that contains instructions for translating various logical references in the compilation plan into actual objects and tracking mechanisms for query execution.

3. Start execution from the top of the instruction tree.

Generating a compilation plan is a costly task because it requires a better one in hundreds of compilation plans. Each compilation plan can be cached and shared with multiple execution plans.

Some parameters of the memory grant estimate are stored in the compilation plan, and it itself has a mechanism for calculating the amount of memory that needs to be granted to the query when it is actually executed.

Memory User (consumers)

A successfully executed query consists of three primary memory users: compilation, caching, and memory grant.

Compiling: Creating and in hundreds of plans when finding the optimal compilation plan is a very memory-intensive thing. It usually takes a short time because the optimizer frees up memory immediately after it finds the optimal compilation plan.

Compiling primarily uses memory and CPU resources. Lack of available memory can lead to compilation delays and suboptimal compilation schedules.

Caching: In order to compile plan reuse, SQL Server saves the compilation plan to the cache. It takes up memory for a long time. If available memory is missing, it causes unnecessary compilation.

Memory Grant: This section is primarily used to store temporary intermediate data rows when the data is sorted and concatenated. Lack of available memory causes queries to use hardware to stage data and reduce query performance.

SQL Server uses a memory broker to balance three consumer memory usage. Based on the memory and available physical memory required by the user, if the memory agent estimates that memory is low, it sets the memory usage limit and notifies the three to reduce their memory usage.

Grant parameters (grants Parameters)

When SQL Server creates a compilation plan, it calculates two parameters: the memory must be requeried and the extra memory (Additional).

Required Memory: The minimum amount of memory required to perform a sort and hash join. This portion of memory is "required" and is used to create the internal data structures needed to handle sorting and hashing.

Extra Memory: The memory required to store all temporary data rows. Its size is determined by the cardinality evaluation (cardinality estimate, such as row count and row size). "Extra", as the name implies when this memory is missing, will save the temporary data rows to the hard disk, and will not cause the query to fail.

The extra memory size of a query exceeds the preset limit, and it actually gets the same amount of RAM as the request.

For example, to sort 1 million rows of data with a row size of 10byte, this query must have a memory of 512KB (this value is the minimum amount of storage required for SQL Server to process a sort operation to create an internal data structure). In order to store all data rows, the additional memory may be 10MB.

When a compilation plan contains multiple sort and join operations, the calculation of additional memory becomes complex. Because SQL Server considers how efficient memory is used by all operators. You can view the <MemoryFractions> tags section of the Showplan XML for more information on memory usage.

degree of parallelism dependent (DOP dependence)

When SQL Server uses multiple CPUs, the query can be decomposed into multiple worker threads to perform in parallel to improve performance. These worker threads run independently of each other and then transfer data using the parallel operator (Parallelism operator a.k.a Exchange).

Parallel mode increases the use of memory because each worker in parallel needs a copy of the sorted and joined data and the parallel operator needs to cache all the temporary data being transmitted. So DOP n will use n times the required memory.

However, the number of data rows that need to be processed and the amount of memory stored in them will not change with the DOP. In other words, the extra memory is not changed by the Dop face. The cache memory used by the parallel operator starting with SQL 2008 is also counted as a mandatory memory grant.

Memory Grant Process

SQL Server needs to take into account the amount of server memory and the number of concurrent queries to avoid the eventual commit of more than the physical memory limit. This is done in two steps, first it calculates how much memory needs to be allocated to a query, and then uses the internal factor-resource semaphore (Resource Semaphore) To keep the actual memory, or when too many queries request memory, it adjusts (throttle) these requests. Follow the steps below to determine the amount of memory requests:

1. SQL Server determines the maximum degree of parallelism based on server state and schedule.

2. SQL Server checks if a memory grant needs to be performed and executes the query immediately if it is not required. For example, there is no group by or order by in the query, and no memory grant is required.

3. Compute the memory limit for a single query, the default value =0.25 (23bit SQL 200 is 0.2) *0.9*server memory. This ratio is configurable in SQL 2008. The memory limit for a single query avoids a query consuming the server memory.

4. Calculate the desired amount of query memory = The desired RAM Demand *dop+ additional memory (+exchange on SQL 2008).

5. Then check if the ideal memory manager exceeds the single query memory limit. If exceeded, the amount of extra memory will be reduced until a single query limit is met. This modified memory is called the requested memory (requested memories), SQL The server invokes the resource signal to grant the requested memory.

Resource semaphore (Resource Semaphore)

Resource semaphores are responsible for adjusting and satisfying memory grant requests to ensure that their usage is within the limits of the server.

1. The resource signal allows the query to reserve memory only if there is enough free memory. Otherwise, it will be queued up in a waiting queue.

2. When a resource semaphore receives a query request, it checks the wait queue for a pending request, and if so, the request is also placed in the queue for this FIFO.

3. The resource signal attempts to grant memory when there are no waiting queries in the queue or when the previous query returns memory that is reserved.

4. If a waiting query is found, the requested query is entered into the queue.

5. If there is no waiting query, it checks the available free memory.

6. If sufficient free memory is found, the memory requested by the query is granted. The query will begin to run.

7. If sufficient free memory is not found, the current query is placed in the wait queue.

8. When there is enough free memory available, the resource semaphore wakes up the waiting query in the queue.

debugging memory grant-related issues

SQL Server provides some of the issues that the DMV uses to analyze memory grants.

Sys.dm_exec_query_resource_semaphores

This view returns the status of the current resource semaphore. It returns two rows, a line called a regular resource semaphore (max_target_memory_kb column is non-null), and one row is called a small resource semaphore (less than 5MB query).

Sys.dm_exec_query_memory_grants

Returns information about a query that has obtained memory grants and is waiting for memory grants. The Grant_time column of the waiting query will be null. Resource semaphores Use the internal query cost to evaluate the order in which the memory is granted.

The Is_next_candidate column identifies the waiting query that is being woken up next.

Sys.dm_os_wait_stats

This DMV returns the wait statistics that show all server objects. The wait type for memory grant is called "Resource_semaphore". If this wait type is too large, it might be caused by a big query.

using the example

Querying queries in the memory queue that are waiting:

SELECT * from sys.dm_exec_query_memory_grants where grant_time is null

Queries who use the most query memory grant:

SELECT mg.granted_memory_kb, mg.session_id, T.text, Qp.query_plan
From Sys.dm_exec_query_memory_grants as MG
Cross APPLY Sys.dm_exec_sql_text (Mg.sql_handle) as T
Cross APPLY sys.dm_exec_query_plan (Mg.plan_handle) as QP
ORDER by 1 DESC OPTION (MAXDOP 1)

Queries in query cache that are granted using query memory:

SELECT T.text, Cp.objtype,qp.query_plan
From Sys.dm_exec_cached_plans as CP
JOIN sys.dm_exec_query_stats as qs on cp.plan_handle = Qs.plan_handle
Cross APPLY sys.dm_exec_query_plan (Cp.plan_handle) as QP
Cross APPLY Sys.dm_exec_sql_text (Qs.sql_handle) as T
WHERE qp.query_plan.exist (' Declare namespace n= "Http://schemas.microsoft.com/sqlserver/2004/07/showplan";//n: Memoryfractions ') = 1

Summary:

1. Original address: Understanding SQL Server Memory Grant

2. I have read this blog post of Shanksgao and found this information in brain tonic.

3. Large queries, especially large statistical queries, can be sorted and hashed, and are the most resource-intensive operations. Understanding query memory grants helps to determine the necessity and optimize this query.

Understanding SQL Server's Query Memory grant (translated)