SQL SERVER – Disable Memory Grant Feedback at Database Level and Query Level

4 years ago

If you are a regular reader of this blog post, you probably know that I have been recently writing a small series on my experience with the Memory Grant Feedback feature of SQL Server 2019. I feel so lucky that in the release of just 3 weeks, I got the opportunity to tune the latest version of SQL Server while working on a Comprehensive Database Performance Health Check. Today we will see how to disable memory grant feedback at Database Level and Query Level.

Brief History

Three weeks ago, SQL Server 2019 released and one of my clients upgraded to SQL Server 2019. As soon as they upgraded to the latest version of SQL Server, they had performance regression. They immediately hired me and we worked together Comprehensive Database Performance Health Check and improved the performance of the server. While working together we noticed a very interesting pattern for one particular query which kept on giving us unexpected performance problems.

Here are a few blog posts which you may find interesting about Memory Grant Feedback:

Senior DBA of the organization where I was consulting and I had spent some really good time understanding the query pattern and heuristics. After a while, we figured out the problem and it was related to brand new feature Memory Grant Feedback.

Now don’t get me wrong this feature is a great feature that worked very well for 99% of the queries in our system. Just one stored procedure was acting weird with this query. As this was a brand new feature it took us a while to figure out what to do with this feature. Eventually, we decided to disable it.

There are two different ways to disable memory grant feedback, we will see the syntax of both the methods here.

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_BATCH_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_BATCH_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 ol.OrderID,ol.StockItemID,ol.Description,
ol.OrderLineID,
o.Comments, o.CustomerID
FROM Sales.OrderLines ol
INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
WHERE ol.StockItemID > @StockItemID
ORDER BY ol.Description, o.Comments
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'))

When you run the above query, it had disabled the memory grant feedback and used one static memory for the query. Once we use this option for a specific query, our performance for that one query was restored.

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.

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