SQL SERVER – Row Mode and Memory Grant Feedback

3 years ago

Earlier this year, I had a great time writing on the Memory Grant Feedback. I have received a lot of questions about this feature and I have tried my best to answer each of them. One of the questions, which really caught my attention did this feature work with Row Mode like it does with Batch Mode. This question indeed threw me off because nowhere I had mentioned earlier that this is Batch Mode only feature. You can read more about Memory Grant Feedback over here, here, here, here, here, here and here.

However, I can clearly see where actually the confusion started with the blog readers that Memory Grant Feedback may be the Batch Mode feature and not Row Mode feature. This is because, in all the above blog posts, I used the example which was the table that contained the ColumnStore Index, which eventually displayed Batch Mode execution on the operator.

Honestly, the Memory Grant Feedback feature works equally well on the RowMode as well as on the BatchMode. Click here to see the example of the Batch Mode and Memory Grant Feedback.

Row Mode

Here is a very simple example where I have not used stored procedure or anything. Just a simple select query and I will execute it two times. There is no column store index or anything on it.

USE WideWorldImporters
GO
SELECT *
FROM Sales.Orders
WHERE PickedByPersonID = 10
ORDER BY SalespersonPersonID
GO

It is a Row Mode query and when you run it the first time there is a memory grant issue and when we run it the second time the memory grant issue disappears.

This simple example proves that the Memory Grant Feedback feature is equally helpful in the Row Mode and Batch Mode process.

If you want to disable Batch Mode Memory Grant Feedback, you can follow this blog post and disable it: Disable Memory Grant Feedback at Database Level and Query Level. However, if you want to disable row Mode Memory Grant feedback, here is the example for the same.

Disable Memory Grant Feedback at Database Level

You can easily disable this feature at the database level by running the following command.

ALTER DATABASE SCOPED CONFIGURATION
SET DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

We did this first on our client and realized that it has negatively impacted our server so we immediately turned this one back on.

ALTER DATABASE SCOPED CONFIGURATION
SET DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

The next task was to disable this feature at the query level.

Disable Memory Grant Feedback at Query Level

As disabling memory grant at the server level was not a great idea, Sr DBA and I decided to disable it at the query level.

For example, if the following is my query, I would disable the memory grant feedback by adding the option hint at the end of the query.

SELECT *
FROM Sales.Orders
WHERE PickedByPersonID = 10
ORDER BY SalespersonPersonID
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'))

Please note that do not use either of this option without proper consultation of a SQL Server Performance Tuning Expert in your organizations. It is quite possible without proper diagnosis and investigation, this option may reduce your SQL Server performance rather than increasing it. I often help to check this process in my Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)