Problem
One of the new features in SQL Server 2019 is the row mode memory grant feedback, that expands the functionality introduced in SQL Server 2017 for batch mode. The memory grant is used by the database engine to allocate how much memory you will use for any query, it can allocate more or less than the actual required. If it uses more memory, the available memory can be less for other processes, and if it allocates less, then there will be memory spills to disk.
With the existing memory grant process, the allocation is the same every time you execute a query, but for this new memory grant feedback, the memory grant is adjusted for subsequent query executions, improving memory usage for most of the database environments.
Solution
In this tip I will show you how to enable the row mode memory grant feedback and how to capture it to verify if there is any improvement for your existing queries.
For it to work, your database must be in compatibility mode 150, you can enable it by running the following T-SQL:
ALTER DATABASE <YOUR_DATABASE> SET COMPATIBILITY_LEVEL=150;
GO
Row Mode Memory Grant – Prior to Compatibility Level 150
For this example, I will show you how the current memory grant works we will use the WideWorldImporters database available at GitHub, so for our first example we will put the database compatibility level at 140 as follows:
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL=140;
GO
After that, we proceed to execute a simple query that will use a high memory grant:
SELECT
o.PurchaseOrderID,
o.OrderDate,
o.Comments,
o.InternalComments,
ol.PurchaseOrderLineID,
ol.LastEditedBy,
ol.Description
FROM Purchasing.PurchaseOrders O
INNER JOIN Purchasing.PurchaseOrderLines OL ON O.PurchaseOrderID = OL.PurchaseOrderID
ORDER BY O.Comments
Checking the execution plan, we can see a warning and a high memory grant for this query:
We can see that 97,720 KB were granted, but just 2,496 KB were used, so in this case we have allocated more memory than needed.
If we execute the query again, the same memory is granted:
So, as we can see, prior to compatibility level 150, no feedback is performed for row mode grants.
Row Mode Memory Grant – How it Works with Compatibility Level 150
For the next example, we will change the database compatibility level to 150 as follows:
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL=150;
GO
Now we proceed to execute the exact same query from previous example, we can see a change in the memory granted:
This time, 109,000 KB were granted for the first execution (more than with the previous example), and the warning remains, we can see the used memory is the same 2,496 KB.
If we execute the query a second time, we now can see the warning is cleared:
And the memory granted is reduced:
This time, SQL Server only allocated 3,760 KB, much better than the previous execution.
Capturing Memory Grant Feedback using Extended Events
For the previous examples, we determine that a memory grant change was performed as we check the memory used manually, but for busy environments with a lot of queries, you need a way to determine if a memory grant adjustment was performed.
At the time of this writing this tip, there is no way to detect if there was a memory grant change for any given query using execution plans, so if you want to determine this, you must use Extended Events (XE) to capture this information.
For this case, the event to be captured is query_post_execution_showplan event, for SQL Server 2019 two new attributes were added to the <MemoryGrantInfo/> XML element:
- IsMemoryGrantFeedbackAdjusted: Indicates if the memory grant was adjusted or not. In this link from SQL Server Blogs we can find the possible values and their description.
- LastRequestedMemory: Indicates in KB the amount of memory of the previous execution, or 0 for the first one.
In the following T-SQL, I share the code to create the Extended Event:
CREATE EVENT SESSION [Memory grant_feedback_XE] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
WHERE ([sqlserver].[database_name]=N'WideWorldImporters'))
ADD TARGET package0.event_file(SET filename=N'Memory grant_feedback_XE')
WITH
(
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON
)
GO
ALTER EVENT SESSION [Memory grant_feedback_XE] ON SERVER STATE = START;
Of course, you must filter the Extended Event to capture just the queries you want, because right now, all database activity will be captured.
Now, if we do the same steps we did before with the Extended Event running and we browse the captured events then browse the first query execution:
We double click the showplan_xml field, and we browse the MemoryGrantInfo tag. We can find the 2 new fields we discussed before (output was formatted for easier reading):
As we can see, it indicates that this is the first query execution and no memory adjustment was made yet.
If we browse the second query execution, we can see the info captured:
This time the memory granted was updated and properly captured on the Extended Event, we can also see that the previous memory allocated match with the first execution.
If we execute the same query a couple of times more, we can see that the last requested memory and the granted memory will match:
The XML info now indicates that the memory grant is stable and will not require more adjustment for subsequent executions, unless the required memory changes abruptly on newer executions due to data modifications/additions/deletions or cache cleanup.
Enabling or Disabling Row Mode Memory Grant Feedback Without Changing Compatibility Level
You can enable or disable the row mode memory grant feedback via database scoped configurations executing the following T-SQL:
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = [OFF|ON]; --select ON or OFF
This way you don’t have to change the compatibility level to disable this option if your application doesn’t work as expected.
Next Steps
- You can check SQL Server 2019 new features here.
- Here are some more SQL Server 2019 tips.
