What are Memory Wave App grants? Memory grants, also referred to as Query Execution (QE) Reservations, Query Execution Memory, Workspace Memory, and Memory Reservations, describe the usage of memory at query execution time. To offer some context, during its lifetime, a query may request memory from different memory allocators or clerks relying on what it must do. For example, when a query is parsed and compiled initially, it consumes compilation memory. Once the query is compiled, that memory is released, and the resulting question plan is saved in the plan cache memory. As soon as a plan is cached, the question is prepared for execution. If the query does any type operations, hash match operations (Join or aggregates), or insertions into a COLUMNSTORE indexes, it uses memory from question execution allocator. Initially, the query asks for that execution memory, Memory Wave and later if this memory is granted, the question uses all or part of the memory for type results or hash buckets. This memory allotted throughout question execution is what's known as memory grants.
As you possibly can think about, once the query execution operation completes, the memory grant is launched again to SQL Server to make use of for different work. Due to this fact, memory grant allocations are short-term in nature however can still last a very long time. For instance, if a question execution performs a type operation on a really massive rowset in memory, the type could take many seconds or Memory Wave App 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 type operation induces a memory grant request. If you run this question in SSMS, you can view its query plan. XML ingredient that reveals the identical memory grant data. Several phrases need rationalization right here. A query could want a specific amount of execution memory (DesiredMemory) and would commonly request that amount (RequestedMemory). At runtime, SQL Server grants all or part of the requested memory relying on availability (GrantedMemory).
In the end, the query might use roughly of the initially requested memory (MaxUsedMemory). If the query optimizer has overestimated the amount of memory needed, it makes use of lower than the requested measurement. But that memory is wasted because it could have been utilized by another request. Alternatively, if the optimizer has underestimated the dimensions of memory wanted, the excess rows may be spilled to disk to get the work achieved at execution time. As a substitute of allocating extra memory than the initially requested dimension, SQL Server pushes the additional rows over to disk and uses it as a temporary workspace. For more data, see Workfiles and Worktables in Memory Grant Concerns. Let's assessment the completely different terms you might encounter regarding this memory client. Once more, all these describe concepts that relate to the identical memory allocations. Query Execution Memory (QE Memory): This time period is used to focus on the fact that type or hash memory is used during the execution of a query.
Generally QE memory is the biggest consumer of memory through the life of a query. Query Execution (QE) Reservations or Memory Reservations: When a question needs memory for kind 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 question executes, SQL Server grants that request partially or absolutely depending on memory availability. Ultimately, the question may use a share of the granted memory. Memory Grants: When SQL Server grants the requested Memory Wave to an executing question, it's stated that a memory grant has occurred. There are a number of efficiency counters that use the term "grant." These counters, Memory Grants Outstanding and Memory Grants Pending, show the count of memory grants glad or ready. They don't account for the memory grant size. One question alone may have consumed, for example, 4 GB of memory to perform a type, but that is not mirrored in either of those counters.
Workspace Memory is one other term that describes the identical memory. Typically, you may see this term within the Perfmon counter Granted Workspace Memory (KB), which displays the general amount of memory presently used for sort, hash, bulk copy, and index creation operations, expressed in KB. The maximum Workspace Memory (KB), another counter, accounts for the maximum quantity of workspace memory out there for any requests which will need to do such hash, type, bulk copy, and index creation operations. The term Workspace Memory is encountered infrequently outdoors of these two counters. Usually, when a thread requests memory inside SQL Server to get one thing executed and the memory is not accessible, the request fails with an out of memory error. However, there are a couple of exception scenarios where the thread would not fail however waits till memory does become accessible. One of those scenarios is memory grants, and the opposite is question compilation memory. SQL Server uses a thread synchronization object known as a semaphore to maintain observe of how a lot memory has been granted for query execution.