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