Memory Grant Feedback – SQL 2019 new feature applied to Azure SQL DB

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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 HAVING COUNT_BIG(1) > 1

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 https://support.microsoft.com/en-us/help/3107401/new-query-memory-grant-options-are-available-min-grant-percent-and-max

SELECT object_id, name FROM test GROUP BY object_id, name HAVING COUNT_BIG(1) > 1 OPTION(MIN_GRANT_PERCENT = 0.25, MAX_GRANT_PERCENT = 0.25)

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

ALTER DATABASE [sandbox] SET COMPATIBILITY_LEVEL = 150

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

Conclusion

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

Test 1 : Compatibility level 140 (SQL 2017)

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

Test 3 : Compatibility level 150 (SQL 2019)

Conclusion

Leave a Reply Cancel reply