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
OPTION (MAXDOP 1)
GO

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
OPTION (MAXDOP 1)
GO

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
go
select top (5000)
* from
(select top(1000)
p.*, b.Actualcost
from dbo.bigProduct as p
CROSS APPLY
( Select count(*) as actualcost
from dbo.bigTransactionHistory as b
where
b.ActualCost between p.productid-1000 and p.productid+1000
)as b
)as x
ORDER BY name
OPTION (MAXDOP 1)
GO

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
(
--default=0
request_memory_grant_timeout_sec=10
)
go

Alter resource governor reconfigure
go

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
from
(select top(1000000)
productid from dbo.bigTransactionHistory
order by actualcost) as p
CROSS APPLY
(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
OPTION (MAXDOP 1)
GO

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).

Summary:

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!

One thought on “Memory Grant Internals Part 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s