Go Understanding SQL Server Memory Grant

This article describes how to query memory grant works in Microsoft SQL Server. It applies to both SQL2005 and SQL2008. Written by Jay Choe, Microsoft SQL Server Engine.

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

Query memory Grant (a.k.a. Query work buffer) was a part of the server memory used to the store temporary row data while sorting an D joining rows. It is called "grant" because the server requires those queries to "reserve" before actually using memory. This reservation improves query reliability under server load, because a query with reserved memory are less likely to hits Out-of-memory while running, and the server prevents one query from dominating entire server memory. In the following sections, I'll describe how to SQL Server estimates query memory requirement, and how it throttles memory Grants when multiple queries is competing.

When SQL Server receives a user query, the It follows well-defined steps to produce a result for user. First, it creates a compiled plan, which is a set of the logical instructions such as how to join rows. Next, it creates an execution plan based on the compiled plan. This execution plan contains instructions with all logical references translated to actual objects, and tracking mechanism s for query execution. Finally, the server starts execution from the top of the instruction tree. Creating A compiled plan is expensive because the server needs to find the optimal plan out of hundreds of potential Candi Dates. Distinguishing between compile and execution helps overall server performance because each compiled plan can be cached and Shared among multiple execution plans. The memory grant estimate follows the same overall sequence. It has parameters saved in compiled plan, and a mechanism to calculate actual the grant size at execution time.

Memory Consumers now that I briefly covered the lifetime of query execution, I would like to show wher e Query memory grant fits in overall server memory usage. As hinted in the previous paragraph, a successful query execution involves 3 major memory consumers:compile, cache, and M Emory Grant.

• Compile (query optimization): Building and searching the most optimal plan out of hundreds candidates typically Requires significant amount of memory. The lifetime of this usage are typically short because optimizer releases memory as soon as optimal plan is found. Lack of available memory would cause delays in compile, and potentially inefficient (slow) plans.
• cache:finding Optimal plan is costly in terms of CPU and memory usage. SQL Server tries to store compiled plans on caches for later reuse. Lifetime of this memory usage is long-term. Lack of cache memory would cause more unnecessary re-compiles.
• Memory Grant:this memory is used to store temporary rows for sort and hash join. The lifetime of memory grant is the same as the lifetime of query. Lack of available memory grant causes a query to use hard disk, which affects query performance.

SQL Server maintains the balance between these 3 consumers with internal facility called "Memory Broker". Based on the usage and physical memory available, the memory broker sets the usage limit and tells each component to trim Its memory usage if it anticipates a shortage. Generally, a well behaving server would has about the same contributions from these consumers.

Grant Parameters When SQL Server creates a compiled plan, it calculates the memory grant parameters called "Required memory" and "Additiona L Memory ".

• Required memory:minimum memory needed to run sort and hash join. It is called required because a query would not start without this memory available. SQL Server uses this memory to the create internal data structures to handle sort and hash join.
• Additional Memory:amount needed to store all temporary the rows in memory. This depends the cardinality estimate (expected number rows and average size of row). This was called additional because a query can survive lack of such memory by storing part of the temporary rows on hard disk. A query is not guaranteed to having the full amount if the total exceeds preset limit.

For example, let's consider a simple query which needs to sort 1 million rows of ten bytes each in size. The required memory for this query is 512KB because the minimum amount SQL server needs to construct internal data Structures to handle one sort. Since it would take 10MB to store all rows, the additional memory would being 10MB (slightly higher when overhead is included ). This calculation becomes complicated if the compiled plan has multiple sorts and joins because SQL Server also considers T He lifetime of each operator for more efficient memory usage. You would generally see smaller estimate than the sum of all sorts and joins. If you is interested in the relative memory usages among operators, you can check out <MemoryFractions> tag in Show Plan XML. The following sections show how these parameters is used when calculating grant size at runtime.

DOP Dependence If SQL server have more than 1 CPUs, it can run a query in parallel mode for improved per Formance by sharing work among parallel workers. These workers run independent of each other, and use "Parallelism operator (a.k.a. Exchange)" to transfer processed rows. This parallel mode increases memory usage because each worker needs its own copy of sort or hash join, and the parallelism operator needs buffers for temporary storage of transferred rows. Since DOP N would use n parallel workers, the query would need N times more required memory. On the other hand, the total number of rows to handle (and memory cost to store them) is does not a change with DOP. This means the additional memory would stay the same regardless of DOP setting. Starting with SQL, the buffer memory used by parallelism operator are also counted as a required part of memory grant.

Memory Grant Process In the previous sections, we discussed how parallelism affects the query memory requirement. In this section, we'll discuss how SQL Server takes server memory and number of the concurrent queries into considerations. The server needs to consider such dynamic factors to avoid committing memory beyond its physical limit. This is the done in 2 distinct steps. First, the server calculates how much memory to grant for given query. Then it uses the internal facility called Resource Semaphore to reserve actual memory, or throttle if too many queries ask For memory. First, the following steps show how the request size is determined.

• The server decides parallelism (DOP) based on the plan and the server state.
• The server checks if memory grant is needed not. If not needed, the server can start the query immediately. For example, a simple serial query without "ORDER by" or "GROUP by" may not need memory grant.
• The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of the total query memory (which are set by memory broker as about 90% of serve R memory). This per-query limit helps through prevent one query from dominating the whole server. This percentage was configurable on SQL 2008.
• The server calculates the ideal query memory as REQUIRED*DOP + additional (+ Exchange on SQL 2008). This was amount a query would like to has, based on its cardinality estimate.
• The server checks if the ideal memory exceeds the per-query limit. If it does, then the server reduces the additional memory until the total fits within the limit. This revised size is called requested memory. The server asks Resource Semaphore to grant the requested memory.

Resource Semaphore Resource Semaphore is responsible for satisfying memory grant requests while keeping overall memory grant usages within th E server limit.

• Resource Semaphore allows a query to reserve memory only if there are enough free memory available. Otherwise, a requesting query is the forced to wait in a queue.
• When Resource Semaphore receives a new request, the IT first checks if any query is waiting. If it finds one, it puts the new query to a queue for fairness because the wait queue is designed as First-come-first-se RVed basis.
• Resource Semaphore checks for waiting query with its queue. If it finds one, it puts the new query into a wait queue for fairness, because the wait queue is designed as First-come-fi rst-served basis with small weight to favor small queries.
• Resource Semaphore makes a grant attempt if it does not find waiting query, or when existing query returns memory.
• Resource Semaphore attempts to grant memory when there are no waiting query, or when a query returns memory.
• Grant attempt was made when there was no waiting query, or when an existing query returns reserved memory.
• If it finds a waiting query, it puts the current query to a waiting queue for fairness.
• If it does not find any waiting query, it then checks available free memory.
• If it finds enough free memory, then the requested memory is granted and the query can start running.
• If it does not find enough free memory, then it puts the current query into the waiting queue.
• Resource Semaphore wakes up queries in the waiting queue when enough free memory becomes available.

debugging memory Grant related issues SQL Server supplies a few dynamic management views (DMV) to help Investigate memory grant related issues. Refer BOL (Books-on-line) for detailed information on DMVs discussed below.

sys.dm_exec_query_resource_semaphores This DMV shows the current status of resource Semaphore mentioned in the previous section. On the SQL 2005, you'll find 2 of them. One with Non-null max_target_memory_kb column was called Regular Resource Semaphore, and the other is called Small Resource Semaphore. As its name implies, Regular Resource Semaphore are used by all queries under normal condition, while Small Resource semaph Ore is used by small size queries (less than 5 MB) when they has to wait (see step 6 in the previous section). This would improve response time of small queries which is expected to finish very fast. Note max_target_memory_kb column shows the server memory limit used in step 2 of the previous section.

sys.dm_exec_query_memory_grants This DMV shows all queries consuming memory grants including those waiting in Resource Semaphore queue. Waiting queries would has a null grant_time column. Resource Semaphore uses internal query cost estimate to prioritize memory grants, and is_next_candidate column shows which Query to wake up when the memory is available.

sys.dm_os_wait_stats This DMV shows wait for statistics of all server objects. Memory Grant uses "Resource_semahore" wait type. If you see significant waits on the this wait type, your may has an issue with big queries.

Sample Queries The following sample queries show how memory Grant DMV is used.

Find all queries waiting in the memory queue:

SELECT * from sys.dm_exec_query_memory_grants where grant_time is null

Find who uses 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)

Search cache for queries with memory grants:

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

Original address:

Understanding SQL Server Memory Grant

Http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

[Go]understanding SQL Server memory grant