What are memory grants? Memory grants, additionally referred to as Query Execution (QE) Reservations, Query Execution Memory, Workspace Memory, and Memory Reservations, describe the utilization of memory at question execution time. To provide some context, throughout its lifetime, a question might request memory from completely different memory allocators or clerks relying on what it must do. For example, when a question is parsed and compiled initially, it consumes compilation memory. As soon as the query is compiled, that memory is released, and the ensuing query plan is stored in the plan cache memory. Once a plan is cached, the question is ready for execution. If the query does any type operations, hash match operations (Be part of or aggregates), or insertions right into a COLUMNSTORE indexes, it uses memory from question execution allocator. Initially, the question asks for that execution memory, and later if this memory is granted, the question makes use of all or a part of the memory for kind results or hash buckets. This memory allocated throughout question execution is what is known as memory grants.

As you'll be able to think about, once the query execution operation completes, the memory grant is released again to SQL Server to make use of for other work. Therefore, memory grant allocations are non permanent in nature however can still final a very long time. For instance, if a query execution performs a sort operation on a very large rowset in memory, the type could take many seconds or minutes, and the granted memory is used for the lifetime of the question. This query selects a rowset of over 300,000 rows and kinds it. The kind operation induces a memory grant request. For those who run this query in SSMS, you possibly can view its query plan. XML component that exhibits the identical memory grant data. Several phrases want explanation right here. A query might desire a specific amount of execution memory (DesiredMemory) and would generally request that amount (RequestedMemory). At runtime, SQL Server grants all or a part of the requested memory depending on availability (GrantedMemory).

Ultimately, the question may use more or less of the initially requested memory (MaxUsedMemory). If the question optimizer has overestimated the quantity of memory needed, it makes use of less than the requested size. However that memory is wasted as it might have been used by another request. Then again, if the optimizer has underestimated the size of memory wanted, the surplus rows may be spilled to disk to get the work accomplished at execution time. As an alternative of allocating extra memory than the initially requested measurement, SQL Server pushes the extra rows over to disk and makes use of it as a brief workspace. For extra information, see Workfiles and Worktables in Memory Grant Issues. Let's overview the totally different terms you may encounter relating to this memory consumer. Again, all these describe ideas that relate to the identical memory allocations. Query Execution Memory Wave App (QE Memory): This term is used to highlight the truth that kind or hash memory is used throughout the execution of a question.

Generally QE memory is the most important consumer of memory in the course of the life of a question. Query Execution (QE) Reservations or Memory Reservations: When a question wants memory for type or hash operations, it makes a reservation request for memory. That reservation request is calculated at compile time based on estimated cardinality. Later, when the query executes, SQL Server grants that request partially or totally relying on memory availability. Ultimately, the question may use a share of the granted memory. Memory Grants: When SQL Server grants the requested memory to an executing question, it's said that a memory grant has occurred. There are a few efficiency counters that use the term "grant." These counters, Memory Grants Outstanding and Memory Grants Pending, display the rely of memory grants satisfied or waiting. They don't account for the memory grant size. One query alone could have consumed, for example, four GB of memory to carry out a type, but that is not reflected in either of those counters.

Workspace Memory is another time period that describes the identical memory. Typically, you may see this term within the Perfmon counter Granted Workspace Memory (KB), which reflects the overall amount of memory currently used for sort, hash, bulk copy, and index creation operations, expressed in KB. The utmost Workspace Memory (KB), another counter, accounts for the maximum quantity of workspace memory obtainable for any requests that will need to do such hash, sort, bulk copy, and index creation operations. The time period Workspace Memory is encountered infrequently outdoors of those two counters. Most often, when a thread requests memory inside SQL Server to get one thing completed and the memory is not obtainable, the request fails with an out of memory error. However, there are a few exception eventualities the place the thread doesn't fail however waits till memory does change into obtainable. A kind of scenarios is memory grants, and Memory Wave App the other is query compilation memory. SQL Server uses a thread synchronization object called a semaphore to keep observe of how a lot memory has been granted for query execution.

Edit

Pub: 23 Dec 2025 00:28 UTC

Views: 28