Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

SQL Server 2019 Memory Grant Feedback Example and Data Collection


By:   |   Last Updated: 2018-11-29   |   Comments   |   Related Tips: More > SQL Server 2019

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:

Execution Plan
Memory grant info

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:

Second execution

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:

first feedback execution

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:

Memory grant feedback second execution

And the memory granted is reduced:

Memory granted second execution

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:

Extended events capture

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

Memory grant feedback first execution

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:

Memory grant feedback second execution

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:

Memory grant feedback stable execution

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


Last Updated: 2018-11-29


next webcast button


next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools