Memory Grant Feed - SQL 2019

Every time you display the Actual Execution Plan, you will have the opportunity to hover your mouse over the operator that is in the beginning of the plan and that you tell you what was the Memory Grant that the engine allocated for every given query. If you click on it and go to Properties you will have even more details, like what was desired memory that the engine wanted, how much it got and how much it was really used.

This doesn’t look like it can affect performance so far, however, by default every query can take up to 25% of memory from your server, that means, that if SQL thinks you have a complex query, you will only be able to run 4 queries at the same time and you will have 100% CPU being used. Some applications need to execute thousands of queries per second, so executing four is not really an option.

However, giving not enough memory to a query is also a problem, this will cause the engine to write pages to disk during query execution, which slows down the query once the disk speed is slower than memory.

Starting SQL 2019 Standard Edition, Microsoft announced a new “tuning robot”, which is called Memory Grant Feedback, which basically means SQL will adjust every query memory grant to avoid giving too much memory to query that are not that complex. It would be useful for queries in which SQL has an awful estimate for the memory needed by a specific query. However, some can make an argument that the name is deceiving.

This robot is enabled on the server level, which means that we can’t ignore it for simpler queries or even queries vulnerable to parameter sniffing. Let me give you all an example.

Imagine you have a stored procedure that returns all sales completed between certain dates, those dates are parameters to this same procedure. So, if you request the sales from only one day, SQL Server will not grant lots of memory because you don’t have that many rows within a day. However, if you execute the same procedure for a month, then you might need more memory and even a different execution plan, but that’s another topic.

The robot does not compile a certain number of execution and try to hit a threshold or even consider the parameters in this procedure example. The robot will simply consider the last execution and try getting closer to that memory grant, so that means that if you have random dates being used as parameters, the robot will continuously adjust the memory grant of that query and that will cause the query to go sometimes fast sometimes slow.

That being said, I haven’t seen a success case with that robot enabled, the community in its majority would recommend to keep this robot disable if you are running SQL 2019 or later.