What are memory grants? Memory grants, additionally referred to as Question Execution (QE) Reservations, Question Execution Memory, Workspace Memory, and Memory Reservations, describe the utilization of memory at question execution time. To provide some context, throughout its lifetime, a query could request memory from different memory allocators or clerks relying on what it must do. For instance, when a query is parsed and compiled initially, it consumes compilation Memory Wave brainwave tool. As soon as the question is compiled, that memory is released, and the resulting query plan is saved within the plan cache memory. As soon as a plan is cached, the query is ready for execution. If the question does any type operations, hash match operations (Be part of or aggregates), or insertions into a COLUMNSTORE indexes, it makes use of memory from question execution allocator. Initially, the query asks for that execution memory, and later if this memory is granted, the question uses all or a part of the memory for type results or hash buckets. This memory allotted throughout query execution is what's referred to as memory grants.

As you'll be able to imagine, once the question execution operation completes, the memory grant is released back to SQL Server to use for other work. Subsequently, memory grant allocations are momentary in nature but can nonetheless last a long time. For instance, if a question execution performs a sort operation on a very giant rowset in memory, the kind may take many seconds or minutes, and the granted memory is used for the lifetime of the query. This query selects a rowset of over 300,000 rows and kinds it. The sort operation induces a memory grant request. For those who run this question in SSMS, you may view its query plan. XML ingredient that reveals the same memory grant info. Several terms need explanation right here. A query might want a specific amount of execution memory (DesiredMemory) and would generally request that amount (RequestedMemory). At runtime, SQL Server grants all or part of the requested memory relying on availability (GrantedMemory).

In the long run, the question could use roughly of the initially requested memory (MaxUsedMemory). If the query optimizer has overestimated the quantity of memory wanted, it uses lower than the requested size. However that memory is wasted because it may have been used by one other request. However, if the optimizer has underestimated the scale of memory wanted, the excess rows could also be spilled to disk to get the work finished at execution time. As a substitute of allocating more memory than the initially requested measurement, SQL Server pushes the extra rows over to disk and makes use of it as a short lived workspace. For more info, see Workfiles and Worktables in Memory Grant Concerns. Let's review the different phrases chances are you'll encounter concerning this memory shopper. Again, Memory Wave all these describe ideas that relate to the same memory allocations. Question Execution Memory (QE Memory): This term is used to highlight the truth that kind or hash memory is used during the execution of a question.

Commonly QE memory is the largest client of memory during the life of a query. Query Execution (QE) Reservations or Memory Reservations: When a question wants memory for kind or hash operations, it makes a reservation request for memory. That reservation request is calculated at compile time based mostly on estimated cardinality. Later, when the question executes, SQL Server grants that request partially or fully relying on memory availability. In the long run, the query could use a percentage of the granted memory. Memory Grants: When SQL Server grants the requested memory to an executing question, it is stated that a memory grant has occurred. There are just a few efficiency counters that use the time period "grant." These counters, Memory Grants Outstanding and Memory Grants Pending, display the rely of memory grants happy or waiting. They do not account for the memory grant measurement. One query alone might have consumed, for instance, four GB of memory to perform a type, but that isn't mirrored in either of these counters.

Workspace Memory is another term that describes the identical memory. Often, you may see this time period in the Perfmon counter Granted Workspace Memory (KB), which displays the overall quantity of memory at present used for sort, Memory Wave hash, bulk copy, and index creation operations, expressed in KB. The utmost Workspace Memory (KB), one other counter, accounts for the maximum quantity of workspace memory available for any requests that may have to do such hash, type, bulk copy, and index creation operations. The term Workspace Memory is encountered infrequently outside of those two counters. Most often, when a thread requests memory inside SQL Server to get one thing performed and the memory is not available, the request fails with an out of memory error. Nonetheless, there are a couple of exception scenarios where the thread doesn't fail however waits till memory does change into out there. A kind of scenarios is memory grants, and the other is question compilation memory. SQL Server makes use of a thread synchronization object known as a semaphore to maintain observe of how a lot memory has been granted for question execution.