See Resource Pools Max and Min Memory percent Update(9/17): QE Memory is the very type of memory that Resource Governor actually limits, when used. Query Execution Memory (QE Memory): This term is used to highlight the fact that sort/hash memory is used during the execution of a query and is the largest memory consumption that may come from a query during execution. Again, all these describe concepts that relate to the same memory allocations: Let's review the different terms that you may encounter referring to this memory consumer. These memory operations during the execution of a query are what all these many names refer to. If the query happens to be performing any sort operations or hash match (join or aggregates), or inserting into a columnstore index, then it will first reserve and later be granted to use part, or all, of the reserved memory for sort results or hash buckets. Once a plan is cached, the query is ready for execution. For that, the plan will consume plan cache memory and will stay in that cache until SQL Server is restarted or memory pressure occurs. Once the query is compiled that memory is released and the resulting query plan must be stored in cache.
For example, when a query is parsed and compiled initially, it will consume compile or optimizer memory.
#REASONS NOT TO USE A RAM OPTIMIZER UPDATE#
Update (5/2021): Columnstore indexes may use this memory while INSERT-ing into the index because hash dictionaries/tables are used at run time for index building.Īllow me to provide some larger context: during its lifetime a query may request memory from different "buckets" or clerks, depending on what it needs to do. Have you ever wondered what Memory grants are? What about QE Reservations ? And Query Execution Memory ? Workspace memory ? How about Memory Reservations ?Īs with most things in life, complex concepts often reduce to a simple one: all these names refer to the same memory consumer in SQL Server: memory allocated during query execution for Sort and Hash operations (bulk copy and index creation fit into the same category but a lot less common).
0 kommentar(er)
