Memory Grant Feedback - SQL 2019 new feature applied to Azure SQL DB
Published Nov 21 2019 09:58 AM 4,626 Views

SQL Server 2019 came with a lot of performance improvements that also applies to Azure SQL DB. Find below some tests I made on my own instance at this moment using (S3 tier) to test Memory Grant Feedback


For this test will use table TEST (289.536 rows / 43.144 KB) and for query will use



SELECT object_id, name 
FROM test
GROUP BY object_id, name



When a query like this starts it will need some memory to do the JOINs / SORTs / etc and will request a memory grant. You can understand more about memory grants at Understanding SQL server memory grant 

"For example, let's consider a simple query which needs to sort 1 million rows of 10 bytes each in size. The required memory for this query is 512KB because this is the minimum amount SQL server needs to construct internal data structures to handle one sort. Since it would take 10MB to store all rows, the additional memory would be 10MB (slightly higher when overhead is included). This calculation becomes complicated if the compiled plan has multiple sorts and joins because SQL server also considers the lifetime of each operator for more efficient memory usage. You would generally see smaller estimate than the sum of all sorts and joins"


But what I want you to know is that SQL will just need to estimate how much memory will be needed and sometimes it may request more than needed or it might also request less memory than needed.


You can check the current memory grants for the database using query below



SELECT * FROM sys.dm_exec_query_memory_grants



Find below some tests results on my server


Test 1 : Compatibility level 140 (SQL 2017)

Based on the query plan and the amount of memory you have, SQL will estimate how much memory you may need in this case 9.360 Kb but you can see that was used only 2.608 Kb




Test 2 : Compatibility level 140 (SQL 2017) + QUERY HINT MAX_GRANT_PERCENT

To fix this query, one workaround is to use Resource Governor where we could control the memory grant for a specific workload. However its is not available for Azure SQL DB.


Another solution that requires code change is to use query hints (MIN_GRANT_PERCENT / MAX_GRANT_PERCENT), in this scenario to 0,25% of the available memory.

More info about the hints at




SELECT object_id, name 
FROM test
GROUP BY object_id, name



In this case we can see that SQL wanted 9Mb but only granted 4,5Mb and still using only 2Mb




Test 3 : Compatibility level 150 (SQL 2019)

Now I just changed the database compatibility using SSMS wizard or can also change with command below. There is no need to restart the server







Now using new compat level 150 for first execution it looks like the initial grant actually have increased, for this specific query



But on second execution it already noticed the amount was too high and started adjusting memory grant dynamically




After some executions SQL believe that he have a good amount of memory allocated for this plan




In a scenario / workload where a queries reuses plans with some time the usage of memory will reduce and you will have better overall performance.


You can find more information on Memory Grant Feedback on oficial docs




Version history
Last update:
‎Nov 21 2019 10:04 AM
Updated by: