Memory!! Our servers need memory to store our data, execution plans, storing temporary data and to do some system operations as well. Memory is needed for Query compilation and to stored the plan in the cache once compiled and for processing our queries for sort and hash operations.
Now, you may have a question. Why should I care about knowing the Internals of memory Grants?
Memory is like a set of toys we give to few kids in the party. Everything is well and fine as they generously share the toys with each other. Meanwhile, many kids joined the party and now everyone wants to play with the same set of toys. You know what will happen, screaming and fighting everywhere. No kid is happy until they get a chance to play with set of toys. What do we say to kids fighting for the toys?? “We need to share…”
Same thing happens with memory. Every query needs a memory grant (Query memory Grant) that needs to be allocated before going to the execution phase. Memory grants allocated are based on the hash, sorts operations and based on the cardinality estimates.
Too much memory grant allocated to a query than it requires causes concurrency issues, too little memory on the other hand causes pages spilling to disk (tempdb). Too much is bad and too little is bad too. We need to make sure we are sharing the memory the right way!
By the end of this series Memory grant internals, you will be able to understand how you can effectively manage the memory grants by using the Resource governor. We will also learn about the memory grant feedback feature(SQL Server 2019) and how it is useful in allocating memory grants preventing hash and sort operations spilling to disk.
Introduction to Memory Grants
SQL Server have different types of components (Plan Cache, Buffer pool and other memory components) in the memory area and requires memory for many things. We also require memory to process our queries. Each operator in the execution plan requires memory to process the rows from one operator to the other operator. Some of the iterators need to be buffered as the rows come into them before sending them to the next iterator in the execution plan. These iterators needs more memory. These are called memory consuming iterators. The grants that we given to these memory consuming iterators are called Query memory grants.
There are three main memory consuming iterators:
- Hash Match Iterator: Takes the input rows and build the hash table before the hash table can be probed.
- Sort Iterator: Takes the rows and sort in a specific order required.
- Exchange Iterator: Takes the sets of rows coming in and has to exchange in the buffers to send to the next iterator.
Memory is not free! So, from where do these memory grants come from?
There is a component known as Fixed memory in the memory architecture of SQL server. This memory is “fixed” because it cannot be expanded when it is in use. This memory holds the memory grant connections we use for hashing and sorting for our queries. Like the other caches like plan cache and the buffer pool which can easily expand and shrink when there is a memory pressure, fixed memory cannot expand and shrink when in use. Once a memory is granted to a query for its execution, memory grant cannot be released until the query completes whether the granted memory is used by the query or not.
Fixed memory gets the memory grants from the buffer pool and the plan cache. When more and more queries are requesting for memory grants, sql server will go ahead and use the buffer pool memory. Maximum utilization is approximately 75% of total buffer pool memory.
Every query that sends to the SQL Server will be first go to the pre-optimization stage which is parsing and binding. Then the optimizer comes up with an ideal grant. Ideal grant is the total amount of memory the query needs to process all the operations (memory consuming iterators) to do in memory without having to spill to disk. If the ideal grant is very high then the memory is leveled down based up on the resource pool settings.
We are going to learn about the resource governor and resource pool in coming parts of the series.
How do compiler gets the Ideal grant?
- Statistics: Optimizer will trust the statistics information to estimate the number of rows coming out of the iterators inside the execution plan. Optimizer looks for the row count and row size estimates.
- Hash and Sort memory consuming Iterators: Based up on the hashes and sorts in the execution plan and depending up on the number of row count and row size of input sets. These iterations can actually share the memory grants between them which is known as memory fractions.
Sharing the memory:
Memory consuming iterators (hash and sort) have two phases. Build phase and Utilization phase. For the sort operator, build is to taking the rows and sorting them. Once they are sorted they are scanned. For the sort build phase, the memory is about 200% of the input data size, which is huge. After the build phase, the memory drops to 100%. For the hash iterator, build is to take the rows and build a hash table. Then the hash table will be probed. After the build phases, once the memory is released at the operator level, the next operator downstream can use the same memory.
In this series, we have learned
- What are memory grants?
- What are the main memory grant iterators?
- From where do these memory grants come from?
- How the compiler gets the ideal grant for any query?
- How hash and sort operators share the memory?
In the part 2 of the series, we are going to learn about how resource governor take a part in managing these memory grants.
Thank you for reading!
Stay tuned for the second part of the series….