Memory Grant Internals Part 4

This part is the continuation of the series to the postย Memory Grant Internals part 3

In our previous part of the series, we have learned about what are these query memory/work space memory and how it works, what happens when the memory allocated to the queries is not sufficient and how the queries have to wait before they get the memory grants. In this part4 of the series, lets learn about how queries will spill to disk when the grants allocated are not enough and how to make sure we address and solve these issues.

If your tables are huge and you are sorting them in your queries, then it will take enormous amount of memory to do the sorting task and it is very hard to allocate memory for such sorts unless you have a huge amount of memory. In such cases, data have to spill to disk. Spilling to disk meaning using the “SQL server scratch pad” which is tempdb to do these sorts and as these spills directly to tempdb on disk, our queries become very slow to run.

As we learned in the first part of the series, memory is needed by these memory consuming iterators: Hash match, Sorts and Exchange iterators. There are two phases for each of these iterators. A build phase and a probe phase. Lets go and learn how these three iterators work in detail:

Hash Match: At the build phase, it will take the outer input and build the hash table. It builds a set of hash buckets. Now what are these hash buckets? Each hash bucket is like a memory location which is a pointer to the linked list of rows. Any rows that will hash to the bucket go into the linked list and be placed there. So, the input side is scanned, buckets are created, rows are hashed and then these rows are placed into the linked list in the respective buckets using a Hash function and during this phase, we have a hash table created. Next, the match operation will start where the inner set will be scanned and the rows on inner set are hashed and go to the respective buckets as well. Then they iterate over the linked list to find the match rows.

How do the hash match, spills to disk?

During the build time, the memory is allocated based on the cardinality estimates and the estimates are based on the input size and the probe. The buckets are created in the memory and we place the rows in those respective buckets. If the grant is exceeded then some of the buckets will be send to the disk. As some of the buckets are already in memory and some in disk, the initial probe of the data using the inner set. The probe rows will be scanned if the row hash to the in memory bucket the match is done for those rows. If the rows match to the on disk bucket, the probe row will be return to the disk along with the outer side bucket. So, because of this we have more disk writes to tempdb at the probe time. This is a build side spill.

There is also probe side spills that can occur at the second phase of the hash match iterator. The buckets that has spilled to disk from the first phase should be now moved to the memory back again and then rebuild the hash table, then the spilled inner probe rows will be read back to the memory to match. In this situation the memory is exceeded again so the buckets have to be rewritten to disk. This is called recursion. So this will be the cycle that the plan will not complete making the plan to bailout. Bailout meaning the plan will use the unoptimized nested loop which cannot complete as it uses inner table multiple times. This plan will use little memory but it can run forever.

Sort: Sort will begin sorting in the memory space that was allocated using the quicksort algorithm (The memory requirement is of at least 200% of the input size). If the memory grant is exceeded even a little bit then the entire intermediate set will spill directly to disk and the sort will continue on the disk completely using the merge sort algorithm. . For the Hash match, if the memory grant is exceeded then few of the buckets will go and spill to tempdb.

Exchange: Exchange iterators will deal with the parallel set of rows from multiple threads. There can be three types of exchanges. Distribute(one stream comes in and multiple streams comes out), repartition(depending on the DOP, n streams can come in and n streams can come out and distributed through a different algorithm) and Gather(DOP n streams comes in and 1 stream comes out). For the distribute and the gather, we have DOP set of buffers but for repartition, there is DOP on each side (DOPx2 sets of buffers). There are two sides for the exchange iterator. Producer and consumer side. These sides of the iterator will communicate with each other using buffers/packets of rows. Each side has DOPx2 per stream sets of buffers.

Gather and repartition iterators can come as merging or order preserving meaning if all the streams that are coming in are ordered then the iterators can output the data in the same order. If there is a data skew and if the huge amount of data is flowing in and flowing in very fast then the merging iterators can not keep these as the same order. In such cases, all the data will be spilled to disk and resorted. This situation is called as intra query parallel deadlock which will extremely slow down the queries.

How to check for these spills?

sys.dm_os_waiting_tasks where all the spills will be waiting on the wait IO_completion which means the slow disks. We can also use the SQL Trace to monitor these spills by capturing the Exchange events Hash warning, sort warning and exchange spill. We can also use the sys.dm_db_[task|session]_space_usage which will have the columns for user object allocations like temp tables and table variables and have internal object allocations column which gives information about anything that spills out to tempdb from the workspace memory.

Lets see how these spills can be monitored with the examples (Credits: Adam Machanic)

Create an Extended event filtering the database and choosing the events for Hash and Sort warnings by running below query:

-- Create Event Session SortandHashWarning
ADD EVENT sqlserver.sort_warning(
WHERE (sqlserver.database_id=(8))), -- replace database_id
ADD EVENT sqlserver.hash_spill_details(
ADD EVENT sqlserver.hash_warning(
ADD TARGET package0.event_file(SET filename=N'C:\Spills.xel',max_file_size=(50),max_rollover_files=(2))

Enable the event and watch live data by right clicking on the extended event we created and choose watch live data.

Sort warnings:

Run the below query to see if the data spills to disk:

select top(1000) * from 
select top (668935)
from bigTransactionHistory
) as x
order by 
x.ActualCost desc

When you observe, no data has actually spilled to disk. What if we go ahead and change the rows and see if data spills to disk:

select top(1000) * from 
select top (668936)
from bigTransactionHistory
) as x
order by 
x.ActualCost desc

When you observe, we have 8 sort warnings as this query went parallel (DOP 8 plan) so sort warning was fired on each thread. If you check the duration, it is 0.3sec but previous query is 0.2seconds. There is only some difference in the duration because we only bought one row extra when compared with previous query. By the time the sort spilled to disk, most of the data is already sorted in memory, but if the getting lot and lot of data need to be spilled then the duration will be more as more data needs to be sorted in disk.

Hash spill warnings:

Lets look at the Hash spill with an example. Run the below query and watch live data in extended events.

select count(*) from 
select top (3800000) *
from bigTransactionHistory
) as bth
Select top(10000)
from bigTransactionHistory
) as bth1 on

This query did not spill to disk. Now lets increase the number of row we get and see the event information.

Now, you can see the hash warnings and the duration is 0.84sec. With out the spill, the duration was 0.84sec. There is no difference in the duration without the spill and with the spill. Due to the Grace hash algorithm used by the hash match iterator, we only spills some of the buckets to the disk. Hash match spills much less unless it hits the bailout stage. Did you observe, we have only 3 threads spilling to disk for the hash warning but not all 8 threads. That is because if one thread have more data then the other thread, that thread might not spill but the threads having more data can spill. In this scenario, it spilled three threads. This happens when you have skew in your data.

Lets go ahead and see that information in the execution plan for the same query by running it again.

When you observe the input number of rows passing through the Hash match iterator, by looking at the properties (F4), the number of rows for each thread is not even, the last three threads are having more rows compared to other threads. These three threads have spilled to disk and that is the reason we have seen only three hash match warnings in the event information.

Exchange spill:

Lets go ahead and add two more events to capture the exchange spill: Exchange_spill event and Lock_deadlock_chain event.

Now lets run a query that will have an exchange spill (Credits: Erik Darling)

Creating the table first

  (RN - 1) / 12500
, REPLICATE('Z', 100)
       SELECT TOP (50000) ROW_NUMBER()
       FROM master..spt_values t1
       CROSS JOIN master..spt_values t2
       CROSS JOIN master..spt_values t3
) t

Now, run the below query which can give the expange spills:

       SELECT 1
       WHERE t1.ID = t2.ID

Due to the data coming to the final parallelism iterator and the merging iterator as you can see the order by at the last parallelism iterator. If we have these inter query parallel deadlocks, we can go ahead and see if we have any of these merging iterators in our execution plan.


In this part 4 of the series, we have learned about types of spills that can happen from these memory consuming iterators when the grants allocated are not enough and how to make sure we address these issues. In the next coming part, we will learn about how to solve these issues.

Thanks for reading!

Memory Grant Internals Part 3

This part is the continuation of the series to the post Memory Grant Internals part 2

We have 1gb of memory. 1GB is 128000 pages. Each page is 8kb. Multiplying 8×128000 gives 1GB. There are two default resource semaphore pools. Query memory objects (default) available and small query memory objects (default) available from dbcc memorystatus. when we add these both values 87823+4619=92442 which divided by 128000 gives 0.72 which is the percentage of the total available buffer pool.

Lets go ahead and run a query and check the execution plan for the memory grant

select top (1000) * from
select top (10000000)
* from 
[Sales].[SalesOrderHeaderEnlarged]) as a
order by
TotalDue desc

When you observe the memory grant, its 183MB. What if we go and get more rows this time

select top (1000) * from
select top (15000000)
* from 
[Sales].[SalesOrderHeaderEnlarged]) as a
order by
TotalDue desc

If you run the query select * from sys.dm_exec_query_memory_grants and see the ideal memory which sql server wanted to allocate for this query, we see 1022120kb (1GB). Depending on the 1GB of memory set as Max server memory, the requested_memory_kb is 183496kb (183MB) and used_memory_kb is 183496kb (183MB)

The memory grant for the above query is 183496/8=22937 pages of memory grant. The total available grant for the queries is 92442. We can run the above query 4 times to hit the total memory grant (22937*4=91748). We can use the SQLQueryStress tool to run the query and see the query memory grants. Let us run the query with 100 number of iterations and 10 number of threads.

While the query runs, check the memory grants by running select * from sys.dm_exec_query_memory_grants and see the queries running.

Three queries are running and others are waiting for the memory grants which is represented as the grant_time as NULL as they are waiting for their grants to be released so they can run. Only three queries are running as each query requires 150% of the requested memory grant. Once these queries completes, the other queries gets the memory grants.

As the workload groups has three importance queries. Low queries (0-4), medium queries (5-9) and high queries (10-14). By default, the default workload group has importance of medium queries.

Adam Machanic has explained with some awesome query examples of how the high cost query with low memory grants and high cost query that requires high memory grant works. Lets go ahead and look at these examples. (Credits: Adam Machanic)

Lets go ahead and run the below query which have high query cost but low memory. Run the sqlquerystress at the same time

use Adventureworks2016
select top (5000)
* from
(select top(1000)
p.*, b.Actualcost
from dbo.bigProduct as p
( Select count(*) as actualcost
from dbo.bigTransactionHistory as b
b.ActualCost between p.productid-1000 and p.productid+1000
)as b
)as x

When you check the actual execution plan, the estimated cost is very high, 11479.3 sec to run this query.

But when you check the input to the sort, it only have estimated data size as 263kb which should be very less memory.

While you run the above query, run the sqlquerystress again to see the running queries and queues

When you do the select *from sys.dm_exec_query_memory_grants and see the spid number 56 is waiting and shows the grant_time as null which means the query is waiting. This query is waiting for the 1.1MB memory though the requested_memory is very less. This is due to the high query cost. Look at the queue id is shown as 9. So, the low cost queries which have queue id of 8 should all run through before spid 56 can run. The timeout in seconds is 86400 which is long time for the timeout to occur. SQL Server prioritize low cost queries to run first but as this query is high in cost and low in memory, the query still have to wait until all the low cost queries run.

Now if we have lot and lot of queries having high query cost but only need some memory to run, how do we resolve this issue? We can solve this issue by altering the default workload group and set the request_memory_grant_timeout_sec. Lets go ahead and alter the default workload group to set the timeout to 10 sec.

Alter workload group [default] with

Alter resource governor reconfigure

Now run the same query again with the other queries running with the SQLQueryStress and look the results by running select *from sys.dm_exec_query_memory_grants. When you observe, the timeout_sec on all queries has changed to 10sec. Now this query with spid 56 has to only wait 10sec to get the memory granted to run the query.

After 10 seconds, if you run select *from sys.dm_exec_query_memory_grants again, now the query should be running and the grant_time is no more null.

We can also view these waits for the memory grants by viewing the sys.dm_os_waiting_tasks. You can see all the spids waiting for their resource semaphore grants and waiting to run.

select * from sys.dm_os_waiting_tasks
where wait_type='resource_semaphore'

Lets run a high cost and high memory grant query and check the execution plan for the query cost (129778 sec) and memory grant (566MB). Remember, we have the total available memory only 1gb. So this query needs the entire available memory to run.

select top(5000)
* from
(select top(1000)
p.*, b.thecount
(select top(1000000)
productid from dbo.bigTransactionHistory
order by actualcost) as p
(select count(*) as thecount
from dbo.bigTransactionHistory as b
where b.ActualCost between p.productid-1000 and p.productid +1000
)as b
order by b.thecount desc
)as x
order by ProductID

Lets execute this query along with the other queries running through the SQLQuerystress. When you check the sys.dm_os_waiting_tasks requested memory grant, its 185MB but the query need 150% of requested memory grant to get the memory grant and to be run.

Now, when I go and run the sys.dm_os_waiting_tasks again and check the granted memory, now the query has the granted memory because we hit the timeout of 10sec and as it completed this timeout, it received the granted memory of 640KB to start running the query.

So, once the query time out occurs (here in the query timeout meaning not timeout of the query but instead it waits for the query timeout seconds which is 10sec in this case and then go ahead and grant the memory as required memory to run the query. So, once the query needs to run after the timeout period, it will have to do one of these three things. If there is enough memory to run, it will run. If there is not enough memory to run even for the minimum grant we will get an error. If we have memory for the minimum grant but not enough memory for full grant, then it will scale all the way back and just grant that minimum memory to run. So this query waits until the 10sec timeout and then run with the required memory grant which is the minimum required grant to start the query to run (640kb).


In this part 3 of the memory grant internal series, we have seen about how the query memory/work space memory works. We have also learned what will happen if we do not have enough memory. That’s when things can get complicated and now the sorts and hashes have to spill to disk in tempdb.

In the next part of the series, we will learn about how queries spill to disk when the query memory is not granted as required for the query to sort in memory. When the query spill to disk, queries runs very slower. Lets learn about these in the next part of the series.

Thank you for reading!

Memory Grant Internals Part 2

This post is the continuation of the series to the post Memory Grant Internals part 1

Once the query optimizer finish its job, the query processor will start its job. Query processor will first looks for the importance of the query based on the work load group. So, what is this workload group? To know what it is, we need to Dive deep into Resource Governor. As we are dedicating this series to dive deep into the memory Grant internals, lets focus this post on Resource governor which handles the memory grants for our queries.

Resource Governor

Resource Governor is introduced in SQL Server 2008. Resource governor job is to control and manage the consumption of the CPU, Memory and IOPS resources used by the incoming requests. Resource governor will ensure that one run away query is not consuming the entire memory.

Resource Governor

There are three different components in the resource governor.

  1. Resource Pool: Small percentage of CPU, Memory and IOPS resources are collectively called as the resource pool.
  2. Workload Group: You can define the connections based on the priority is known as Workload group.
  3. Classifier: Classifier is a User defined function created in the master database in which we mention the predefined criteria to classify the incoming requests to respective workload groups is known as classifier. Classifier function can classify the incoming connections by using the application name, database name, login name etc.;

If your SQL server handles many database applications connecting to single database server instance, requests made from all the applications will compete to get enough resources to process the requests which will cause some serious performance issues. With the help of resource governor, we can distribute the assigned resources to specific workloads based up on the low, medium and high priorities. SQL Server can create up to 64 user defined resource pools.

When we install SQL Server, two pools are automatically generated. The Internal pool and Default pool. Internal pool is used for the internal background processes like DAC connections. Internal pool cannot be altered. Internal pool will be the priority when compared with the user defined resource pool. Default pool will handle all user connections that are not sent to the respective user-defined resource pool.

So, when a request is sent to the SQL Server, that session will be diverted to the associated workload group. This workload group will use the resources provided in the respective resource pool.

Now, lets get to the query processor. First the query processor will categorize the query we are sending to the SQL Server based on the workload group, then next it looks at the estimated cost and the size of the memory grant and categorize the query will be placed in the set of queues and these queues are known as Resource semaphores. These queues force queries to wait to get into the semaphore so they can get some memory to run. Resource semaphore is a gateway where SQL queries have to wait until they get the memory to even start processing the query. If SQL Server ran out of memory and queries waiting for the memory to be released to be processed, during this time we see resource semaphore wait types.

Resource pool have two primary resource semaphores. A small and large semaphores. Both have individual memory grants. Small semaphores are for queries with cost less than 3 and grant size less than 5MB. These queries use smaller pool of memory. The large semaphores are for larger queries and use different set of queues for large memory grant.

Each of these semaphores three sets of queues. They have low, medium and high priority sets. The high priority set queries will get importance and first get the memory grants and low priority queries have to wait until they get the memory. The large semaphore have five queues per set. Here the priority is different. Low cost queries gets the higher priority. Low cost queries will run faster than the high cost queries and that is the reason SQL server will prioritize the low cost queries first because low cost queries run pretty quick when compared with high cost queries which need more memory grants.

Queries doesn’t start unless available memory is 150% of requested query memory because sql server will always allocate a little bit of additional buffer memory. Lets take the 100MB query which will get 150MB with additional buffer. What if this query only get 100MB and doesn’t get that additional buffer in the beginning to run the query? In that case, the query will wait until the 25x the query cost which is in seconds. When the timeout occurs and if the exact grant (100MB) is available without the buffer amount then the query starts to run this time. In other scenario, even after waiting for so long and after the timeout, the exact memory (100MB) without the memory grant is not available and less than 100MB is available, in that case the grant can be decreased to the minimum required grant. What if even the minimum required grant is not available, in that case we will receive an error 8645. We can configure the timeout by using the resource governor.

Configuration options for Resource Governor

sp_configure: You can set the options at the server level. You have the option Max server memory (MB). The query memory will be taken out of the buffer pool which is within the max server memory. We also have the Server level default query wait time which is in seconds. Zero is the default value (25x cost of the cost of the query). We also have server level option min_memory_per_query (KB) which is 1MB by default.

Resource pool: There are two main settings. Min_memory_percent and Max_memory_percent. These are the settings to determine how much buffer pool grant can be used by the resource pool.

Workload Group: Within each workload group, we can have the importance level. We have the option request_memory_grant_timeout_sec where we can set the timeout specifically for the workload group. We have the option request_max_memory_grant_percent where we can set the memory grant percent of the resource pool grant that this workload group can take. When you have several different types of workloads on the servers, we can classify and give the workloads a very specific memory utilization.

We can check the memory status by using these commands:

  1. DBCC MEMORYSTATUS (looking at the current and max values of Small Query memory objects, Query memory objects) with give you the information about the maximum workspace/query memory. These two are the default resource semaphore pools.
  2. DMV’s like sys.dm_exec_query_resource_semaphores (information about all the resource semaphores and queues), sys.dm_exec_query_memory_grants provides lot more information to check when you have memory issues (provides one row each for query that has the memory consuming iterators. Gives the information on the whether the query is waiting for memory or granted the memory, how much memory, required memory and ideal memory information). Other DMV we can use is sys.dm_os_waiting_tasks gives the rows for the requests that are waiting for memory.

Lets start doing a hands on lab:

Let me set my max server memory to 1GB so we it will be even for us to play with

sp_configure 'show advanced options', 1;
sp_configure 'max server memory', 1000;

Run a simple query by enabling the actual execution plan to see how the memory grants are allocated for a query:

USE [Adventureworks2016]
Select top (1000)
order by ActualCost

We are choosing the option MAXDOP 1 because we want to run this in a single thread and if we use parallelism, then the memory grants are allocated for each thread. To be simple, we used the maxdop value as 1. Now when you observe the execution plan and see the memory grant on the select, you see the value 10776kb

Now see the estimated data size by choosing the arrow pointing to the sort operator. The estimated data size is 4707kb. The input size for the sort is 200% which will be double of the estimated data size. multiply, 4707kbx2 =9414kb plus additional buffer to make the memory grant 10776kb. That is how the memory grants are calculated based of the estimated data size.

Now, lets go ahead and see how memory is shared between the iterators. Lets run this below query and see the execution plan.

use adventureworks2016
select top(20)
* from
select top (50)
* from
select top (100)
* from
order by ActualCost desc
order by ActualCost
order by ActualCost desc

Press F4 to see the properties of first sort and look into the memory fractions for this sort. The memory fractions input shows as 1 which means the input phase took all the granted memory available and once the build phase is completed, half the memory is available which is represented as 0.5 in the memory fraction output. This memory available will be send to the next iterator.

Now, press F4 on the next sort iterator and check the memory fraction values. The total available memory now is 0.5 as it passed from the previous sort. During the build phase, it used 0.5 and after the build it has same 0.5 available. Now this memory will be passed to the next sort iterator.

Lets go and see for the third sort iterator, press F4 to see the values. It will use the same memory from the previous iterator to build phase 0.5 and after the build phase it released the total memory granted to this query.

This is how we will analyze the memory utilization of a given plan is to look for the memory grant for the final iterator and look for the memory fractions down the stream to know how much each iterator is trying to use.


In this series of memory grant Internals part 2, we have learned about resource governor, components we have in resource governor, configuration options for the resource governor, how to monitor the memory status and how we can see the memory being used by each iterator and share the memory between them before releasing the memory.

In the Part 3 of the series, we are going to learn how to monitor the memory grants by using the DMV’s with examples and much more…

Thank you for reading!

Memory Grant Internals Part 1

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:

  1. Hash Match Iterator: Takes the input rows and build the hash table before the hash table can be probed.
  2. Sort Iterator: Takes the rows and sort in a specific order required.
  3. 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?

  1. 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.
  2. 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

  1. What are memory grants?
  2. What are the main memory grant iterators?
  3. From where do these memory grants come from?
  4. How the compiler gets the ideal grant for any query?
  5. 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….

T-SQL Tuesday #134

T-SQL Tuesday #134

With the Invitation of James McGillivray for this month T-SQL Tuesday about taking a break when youโ€™re stuck at home, I would like share some tips of how I manage stress by taking breaks.

It is a challenging time for each one of us during this pandemic. It is very important to be mentally strong during these hard times. Below are some of the tips I followed to take a break when we are stuck in home

  1. Reading moral stories to kids. Kids really enjoy me reading for them. As I explain the moral values to them, I learn along with them. This will instantly calm me down and make me think the wider perspective of life.
  2. I love arts and crafts. I love shopping for the material to make any new crafts from the ideas I have since long time. Putting something in action and seeing the craft coming out the way you saw in your mind gives so much satisfaction. Below picture is one of the crafts I made “3D wall Garden”.

3. I listen to David Goggins (Former navy seal) podcasts. He explains how a person can be mentally strong and how much a human being can accomplish just by changing the way we look at life. I started reading his book “Can’t Hurt Me”. This book has already changed me. I learned how to be mentally strong during the hardest times of life.

4. Watching thriller movies. I love watching thriller movies as they take me to a complete different world for few hours. Its a true refreshment.

5. Spending some time myself with a cup of coffee. No distractions with any social media, mobile phones or TV. Solely, its a self time. This is the best time to analyze how our mind wanders around. Controlling mind chattering is very important for mental health. Watching the thoughts pass through the mind freely and with mindfulness is like a meditation.

These are some of the tips I follow to take a break, I would love to see the tips from other participants for this month T-SQL Tuesday!

T-SQL Tuesday #133: What (Else) Have I Learned from Presenting?

Thank you Lisa Griffin for the invitation to December month of T-SQL Tuesday.

After I started presenting, I learned many technical concepts but here I would like to quickly mention couple of them:

  1. Memory grant connections are stored in a memory component in the SQL Server which is called as Fixed memory. As the fixed memory cannot be expanded or shrinked when there is a memory pressure, SQL server requests the other components of memory like for example, evict the plans out of the plan cache and dirty pages out of the buffer pool to release the memory as memory grants for executing the queries. These memory grants are allocated before the query goes to the execution phase. Memory grants for any query are allocated based on the hashes, sorts in the execution plan and Cardinality estimates. When there are many queries requesting more memory and when the Fixed memory is already full, these other memory components releases the memory as there is an internal pressure with in the SQL Server. Once the memory grant is allocated to the query, the memory will only be released once the query execution completes. Resource Semaphore is responsible for satisfying memory grant requests while keeping overall memory grant usages within the server limit.
  2. Redundant joins between the tables will be only removed by the SQL Server automatically on the background only if the referential integrity is maintained by the foreign key constraint defined between both the tables. If there is no Constraint defined, then as SQL Server doesn’t know about the relationship between two tables, SQL Server will perform a join scanning one table to find matching values from other table.
  3. SQL Server needs a NOEXPAND hint to create a query plan that uses an indexed view. When NOEXPAND is specified, automatic stats are created on index view same as it happens with the ordinary tables.

I would also like to hear from other community members of what else they have learned from presenting!

Take that First step in Faith, rest will pave its path to your Speaking Journey!

Konstantin Yuganov/Adobe Stock

Do you have a Dream to become a Great Speaker? Do you have that one Dream you wish to see your words changing people lives in a positive way?

You may absolutely have that strong will in your heart to succeed but something inside you might be stopping you, the self doubt.

I struggled with self doubt for a long time and I still have it within me. Trust me, I exactly know how it feels. I have that negative chattering in my mind all the time telling me its not worth it, not to try in the fear of failure.

While I was vigorously searching for a solution to this self doubt, I came across some motivation which completely changed the way I look at life.

“If you are not uncomfortable, then there will be no change in your life! Stop judging yourself and take that First step in Faith. Get ready to get uncomfortable because it takes a lot of work. As you move beyond your Comfort zone and accomplish something, that’s when you realize there is next version of yourself. That’s when you will be able to see and feel the other side of the world you thought never existed.”

This motivation really changed the way I think about life. I learned that all the limitations that exist are the ones we impose on ourselves but in actual reality, there are no limits.

Once I understood that, I seriously started searching for an opportunity that can show me the next version of myself as a Speaker. One lucky day, I saw a twitter post from Ben Weissman:

” Call for Speakers open for New Stars of Data. New Stars of Data is an event to help find and promote new speakers in the Microsoft Data Platform arena. Speakers submit their presentation ideas and are assigned to a seasoned speaker, who will mentor the newcomer in all aspects around delivering a presentation.”

New Stars of Data is organized by Ben Weissman and William Durkin, sponsored by Redgate, SOLISYON and DATAmasterminds to help promote new speakers and pave a path to their speaking Journey. Organizers, Sponsors and Mentors spend their valid time and put so much effort in organizing the event just to see other professionals grow in their career as speakers. I was so lucky to know about the New Stars of Data event at the right moment in my career. I am very thankful to Ben Weissman and William Durkin for introducing this wonderful event helping many Data professionals to kick start their career as speakers.

There is a saying “You can only see what you are looking for!” I absolutely felt amazed and happy to see this post as I was looking for a good opportunity to start my speaking career. I still had self doubt but then I remembered, I cannot move ahead in life if I don’t make a change. I had many thoughts running in my mind continuously but I decided to take that first step anyway. I went ahead and submitted a session. I waited for the speaker selection results. Luckily, I was selected. I was very happy to be part of the event.

I was assigned to a Mentor, Deborah Melkin. I am very lucky to have a wonderful person as my Mentor, Deborah. She was very patient with me during the process. She spend hours of time in helping me during my preparation process. She build confidence in me and changed my life in a very positive way. Her valuable suggestions brought life to my presentation. Without Deborah as my Mentor, this would not have been possible.

There is so much support throughout the process. Both organizers and mentors were always ready to help anytime we need them. The constant support and encouragement built immense amount of confidence in me. I was more than ready to give my first presentation on August 14th. Andy Yun moderated my session. Andy made sure I was comfortable during my entire presentation time. I am very thankful to Andy. Deborah and Ben were also with me online to support me during my presentation. With the help of all these wonderful people, I was able to successfully complete my first presentation. All credits goes to Ben, William, Deborah, Andy and all sponsors.

I never thought I would be able to speak at conferences until I had my first presentation for New Stars of Data.

Received valuable feedback from Ben, William, Deborah, Andy and Jeff Iannucci after the presentation. Attendees were very supportive as well and provided valuable feedback. Feedback from all these amazing people gave me enough confidence and boost to present at different events and conferences.

Since then, I spoke for twenty eight events in total till date which includes three SQL Saturdays across United States and other countries, Dataweekender, DBCC International, GroupBy conference, Drupaljam International Conference, Google Dev Fest Conference and several PASS User Groups across United States and other countries like Canada. I am going to speak for Women In Technology Summit 2021, SQL Saturday Bangladesh and SQL Saturday Vienna 2021, Festive Tech Calendar 2020 in near future.

All this is possible only because of New Stars of Data event. My speaking Journey all started with this wonderful event. As New Stars of Data event paved a path for me, I was able to move in the path, improve, learn, share and continue to grow as a speaker.

Ben and William helped many professionals during the New Stars of Data event. As they continue to help many others, they came up with the second New Stars of Data event.

If you have self-doubt in you, please try to put your self doubt aside for a while and take your first step in faith. Once you take your first step, you will have a chance to see the other side of the world which you never knew existed. You will be able to introduce yourself to a new version of yourself. If you have a Dream to become a public speaker, I really suggest you to apply for New Stars of Data event. It’s not just an event, its a very strong supportive system you build and the friendships you build through the process which is more valuable than anything. These friendships are for life ๐Ÿ™‚

Trust me on this one. Go ahead and apply for New Stars of Data, you will thank me later ๐Ÿ™‚

Call for speakers is open until December 20, 2020. Event is on March 12, 2021. Here is the sessionize link for your session submissions for New Stars of Data. You will be assigned a Mentor who will support you in every step of the process and make your first speaking session successful.

I wish you all the best and see you at the event on March 12, 2021!

T-SQL Tuesday #132: Coping with the pandemic times in year 2020

With the Taiob Ali invitation for November month of T-SQL Tuesday, I would like to share how I am managing and coping with the pandemic times. Thank you Taiob for the invitation on this topic.

There is so much stress in everyone’s life right now. There is so much going on this year that we need to keep ourselves busy hoping for the good days to come. It is a challenging time for everyone of us.

Since the pandemic started, every home turned in to a school, day care, play area and office for all of us. Thanks to all the wonderful kids around the world coping with their parents during these situations. Its really hard for kids to stay away from their outdoor activities for so many months.

It is mentally challenging during these times to mainly focus. As a mother of two kids (4 and 7 year old), I know how hard it is to all parents around the world to work and take care of the kids at the same time. It’s really tough to have our own self time anymore. I took these hard times as really challenging. I followed some of the tips to keep myself positive:

  1. I regularly listen to motivational speeches on youtube to keep myself motivated.
  2. Planning for the weekly goals and trying to accomplish them.
  3. Helping kids on their homework which gives me enough time to spend with them.
  4. Reading moral stories to kids as I feel this is so important to learn moral values. I am learning while I read to my kids. This helps me to see positivity in negative situations.
  5. Gardening gives me peace as I spend some time with nature.
  6. No matter how bad the situation might seem, I am believing that something better is going to come. Hope is all we need right now.

On the positive side, the best thing that happened to me during these times is I had the opportunity to start my career as a speaker. I was able to participate in several conferences starting with New stars of Data, SQL Saturdays, Dataweekender, DBCC International conference, GroupBy conference, Drupaljam reboot 2020, Google Dev Fest Tanzania 2020, and going to present for Granite state code camp, Women in Technology Summit 2021 and many other local user groups across united states and other countries like Canada.

This is only possible because of many wonderful people from SQL Family. I am very thankful to everyone for helping me during my journey as a speaker.

There is so much positivity in SQL Family and I feel very happy to be part of the community.

I wish and pray for all the families who have lost their loved ones due to COVID. May God heal you and give you peace in your lives. I am praying for all of you.

I hope there will be vaccine soon and I wish to see our lives going back to normal days as before.

One last suggestion, please wear a mask, do not go out in public until it is really necessary, spend as much time as possible with your loved ones as they are in home with you all the time during this pandemic. Try to listen to motivation and do meditation/deep breath exercises when ever you feel down. Never ever forget you are impacting someone lives with your positivity around and you might not even realize that you are creating an impact on others. You, me and we all are valuable and loved by God. We all are in this together and we will pass this pandemic together.

Please take care of yourself and your loved ones.

T-SQL Tuesday #131: SQL Server Statistics Analogy

T-SQL Tuesday Logo - A blue database with a calendar showing days of the week wrapped artfully around the database.

With Rob Volk Invitation to October month of T-SQL Tuesday for the Data Analogies, I tried thinking of an analogy for SQL Server Statistics.

Here is my analogy:

For example, One of our friends requested us to volunteer for organizing their kid first birthday party. You and me, we both are more than happy to help. We are so excited to celebrate it with all our friends inviting as many people as possible.

We gathered invitee list together making thousand people in total including adults and children. We planned to create a RSVP list with some beautiful invitation link requesting all the invitees to submit the RSVP with head count number for girl kids and boy kids along with the head count number of families without kids. We sent the invite two months before the event date expecting everyone would submit the RSVP. Based up on the number of head count (girls, boys and families without kids), we want to purchase the return gifts.

Girls get Barbie dolls, boys get Car toy and families without kids get a nice home decor set.

We waited for the RSVP submissions and gathered the results five days before the event date so we can shop for the return gifts. Out of thousand invitees, we received only three hundred RSVP’s. We believed that is the only head count we will see on our event date and we just bought return gifts and food items for three hundred people.

On our surprise, we see thousand people on the event date at the party since the party is on a weekend. We now understood that many people were actually willing to come to the party but for some reason, they did not had chance to do the RSVP. Now, we are all tensed up not knowing what to do.

This is not at all what we expect would happen. We expected something and what actually happening at this event is something else. Everything got messed up now. There is stress everywhere more than fun in the party. We immediately requested some of our friends to order for more dishes, asked some other friends to grab some random gifts from near by store. Many invitees were waiting for their turn to get the food on their plate. People frustrations were clearly seen on their faces. One situation carried to next situation in the event making the whole event a disaster. We anyway passed the event but with no satisfaction. We should have had good estimates of the head count to have the good party plan.

Instead, if we have got the good percent of RSVP’s like around 80% of the total thousand invitees, we would have been in the better shape with the plan. 80% of thousand is 800 people. This would have given us the better estimate close to the actual estimate of the head count. What if 100% of the invitees submitted the RSVP, that would be wonderful, right? Estimation would have been exact match with our actual head count in the party. WOW, that is so good to be true. There is no one single party I have seen where all invitees submitted their RSVP’s. There are always people missing RSVP’s. It is always still good to have the hundred percent estimate though. From invitees point of view, it takes time and effort to make sure they all update the RSVP’s to get the exact estimate.

For making this event successful, we should have been a little cautious while planning for getting these RSVP’s updated by the invitees. We would have send them the notification for every few days or once every week depending up on the number of invitees list. The more invitees we add to the event and the more updates we do to the list, the more frequent notification emails for our RSVP’s should be.

Now we know what we have missed. As we always learn from our past mistakes, we now learn we would have had send the notifications regularly so we get the RSVP’s updated and so we get close estimation which ultimately produces a good party plan.

What if the same concept applies to the SQL Server statistics?

SQL Server optimizer always get the estimate from statistics for how many number of rows (Invitee column statistics) it might expect to get. How does it do that? It will gather and trust the estimates being correct from statistics. Invitees is the column statistics here and RSVP’s are our statistics updates. Our notifications to update the RSVP’s is same as updating our Invitees column statistics using sql agent update stats job or through AUTO_UPDATE_STATS option enabled or through Ola hellengren scripts.

From the estimates of our RSVP’s, we ordered food and gifts for only 300 people out of 1000 people. In the same way, SQL optimizer will allocate less memory based up on the estimates. Because of the less number of the gifts we bought, we requested other friends to go and purchase gifts immediately from the near by gift store. This caused stress on our friends and they did not find all the gifts they need anyway. This made the whole party plan go bad. In the same way, due to the bad statistics estimation, Sql optimizer allocates less memory to process the query which is not sufficient and so it will finally have to spill to the disk. One bad column statistics estimate carries the stress to other operators in the query plan effecting the other operators in the plan and eventually effect complete execution plan.

To solve this issue, Sql Server invitees column stats should be updated regularly.

Depending up on the number of inserts and updates happen to this invitees column in the table, we have to plan for updating these statistics as well. As it takes energy and time for most of the invitees to go and update the RSVP’s, it takes resources and time for updating the invitees column statistics as well.

In order to get good estimates, RSVP’s should be updated as much as possible. If there are not many invitees that are being updated frequently, decent percentage of RSVP’s should be always good for good enough party plan. In the same way, decent amount of percentage stats update should be good enough to generate good plan. If updates happens regularly to the invitees column, frequent statistics updates is recommended.

It is always best to set the AUTO_UPDATE_STATISTICS option enabled. With this option enabled, Sql Server will go ahead and update these statistics automatically in the background.

For the versions SQL Server 2014 and below, the auto update stats kicks in when the threshold hits when 20% of the records gets updated+500 record change but for the later versions SQL Server 2016 and above, auto update stats kicks in when the threshold hits sqrt(1000 * total number of rows) which is way small than the older threshold.

Bottom line from this post is,

The better the estimates, the better the party plan will be ๐Ÿ™‚

T-SQL Tuesday #130 โ€“ Automate Your Stress Away

T-SQL Tuesday Logo - A blue database with a calendar showing days of the week wrapped artfully around the database.

With Elizabeth Noble Invitation, this will be my first post for T-SQL Tuesday. I am very excited while writing this blog post. Thank you so much Elizabeth for sending the invite.

With the current COVID situations, I believe there would be no one on this planet without stress. I wish we had a fast forward stress relief button which can automatically remove the stress from our lives.

With new challenges we face every day comes with new opportunities. While we travel the journey making these challenges into the opportunities, we will have chances to take some smart decisions and actions making our lives easier. Automation is one of them.

As new technologies emerge, automation becoming the key. The tasks which take longer time before can be completed in just few minutes by using automation.

In my everyday job, there are many tasks that I have to do repeatedly across many servers. These tasks can be very simple but time consuming when you are dealing with some hundreds of servers. Automating these tasks can be very helpful. Below are some of the simple tasks which I automated and saved lot of time:

  1. We have several databases that are being replicated between different SQL server versions. This includes SQL server 2008, all the way up to SQL server 2016 versions. There are some databases still on SQL 2000 compatibility level on SQL 2008 servers. Sometimes, replicating these databases to other versions of SQL servers is a challenging task. When ever we had to take a fresh snapshot and replicate, distribution job fails with errors related to default setting options(example, ANSI_PADDING set to OFF when the option should be ON) on the .sch files the snapshot generates (.sch file contains the script to create the table and the replication specific stored procedures on the subscriber). It is a very tedious task to go find all .sch files having this setting as OFF and manually change the setting to ON. So, I used a powershell script in the snapshot job added as a second step after the first snapshot creation step that changes the ANSI_PADDING setting from OFF to ON on all .sch files. When we had many databases having the same issue, automating this task saved me from so much stress.
  2. Once, I had to change the owner of the SQL agent jobs to sysadmin account on around 300 servers at a time. This is a stressful task if I had to do it manually one by one server. This will take me at least few days to complete for all 300 servers. I used powershell script by using commands to get the server list from the text file so the same SQL query runs on each SQL server mentioned in the list. This took me few minutes of time to complete for 300 servers. Imagine how much time this process would have saved me.
  3. There are other instances where I use powershell automation. If I had to give permissions for logins on multiple servers at a time, automating this task using powershell really helps.
  4. I had to convert around 200 servers between SQL server editions with out losing data and the databases. I used powershell script to do this task. If I had to do this manually by uninstalling and installing different edition takes me so much time and effort.
  5. In my work environment, there are many non-production servers. We do make sure all these databases are in simple recovery mode. Developers do create and drop databases on non-prod environments for their testing purposes with full recovery mode. I created a SQL agent job which will check the database recovery modes and send me a report of database names which are not in simple recovery mode. This job runs every day at the scheduled time.
  6. I use automation for documentation purposes too. For example, gathering all SQL agent jobs, gathering login permissions etc; from multiple servers using SQL agent jobs.

These are some of the examples of how I use automation in my every day work life. I would like to know how automation is helping others in their day to day work life.