When SQL Server compiles the execution plan for a query, it has to choose appropriate operators and eventually memory that is required for the query execution. This memory grant size is based on the Estimated Number of Rows for the operator and the associated average row size. If the cardinality estimates are significantly inaccurate, this can lead to poor performance. Underestimations can degrade the performance of the execution query since intermediate rows cannot fit in memory and must be spilled to the disk. On the other hand, overestimations lead to large memory grants and the wasting of memory, which can affect other queries and overall performance.

In one of the previous sections, you saw that you can address memory grant issues (without significant refactoring of your queries) by using the MIN_GRANT_PERCENT and MAX_GRANT_PERCENT hints. SQL Server 2017 introduces another mechanism that deals with this issue—batch mode adaptive memory grant feedback. It is a part of the adaptive query processing family of features.

Batch mode adaptive memory grant feedback is the feature that can update a (correct) memory grant given for a query after the execution plan is created. Interleaved execution allows Query Optimizer to execute a part of the code during the execution plan; here, the action comes after the plan is created. After query execution, the required memory is recalculated and compared with the memory granted during the plan creation. If the difference between them is significant, the memory grant information in the cached plan is updated. Thus, the execution plan remains the same and in the cache, only memory grant is corrected.

To demonstrate this feature, you will use the same sample table used in the New query hints section. Before you execute the query, ensure that the compatibility mode is 130 and that there are no cached execution plans in the WideWorldImportes database:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 130;

The next step is to create a sample stored procedure:

CREATE OR ALTER PROCEDURE dbo.GetOrders
@OrderDate DATETIME
AS
BEGIN
    DECLARE @now DATETIME = @OrderDate;
    SELECT * FROM dbo.Orders
    WHERE orderdate >= @now
    ORDER BY amount DESC;
END
GO

Now, call the stored procedure with a parameter representing a date in the future and with the activated Include Actual Execution Plan option:

EXEC dbo.GetOrders '20180101';

The execution plan for this query is shown as follows:

You can see that this query has a Memory Grant of 263 MB, although no rows are returned. Prior to SQL Server 2017, this value was fixed, regardless of the number of query executions and required memory for it. You could not change the cached plan without recompiling it. As mentioned earlier, in SQL Server 2017, this is possible. To check how SQL Server 2017 deals with memory grants, clear the cache and enable the compatibility mode 140:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;

When you execute the query again, you will see the same execution plan and the same amount of memory grant (263 MB). Now, turn off the Include Actual Execution Plan option (to allow SSMS to display results faster) and execute the same query 20 times, as in the following code:

EXEC dbo.GetOrders '20180101';
GO 20

Now, execute the initial code again, with the Include Actual Execution Plan option:

EXEC dbo.GetOrders '20180101';

Observe the execution plan shown in the following screenshot:

No, this is not a mistake! The execution plan looks the same, as well as the memory grants. But where is the batch mode adaptive memory grant feedback feature? This feature is available only if the affected table has a columnstore index; without an index, it won't work. So, you need to create a columnstore index:

CREATE NONCLUSTERED COLUMNSTORE INDEX ixc ON dbo.Orders(id, orderdate,custid, amount) WHERE id  = -4;

In this example, you have created a trivial, logically useless index, but you just wanted to fill the requirement about the presence of a columnstore index. Now repeat the previous steps. First, execute the initial query with the Include Actual Execution Plan option:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
EXEC dbo.GetOrders '20180101';

Observe the Execution plan and Memory Grant shown as follows:

You can see the same execution plan (with different percentages near the plan operators) and slightly higher memory grant, 278 MB.

Now, turn off the Include Actual Execution Plan option (to allow SSMS to display results faster) and execute the same query 20 times, as in the following code:

EXEC dbo.GetOrders '20180101';
GO 20

Finally, execute the initial query again, with the Include Actual Execution Plan option:

EXEC dbo.GetOrders '20180101';

Observe the Execution plan and Memory Grant shown as follows:

You can see that after 20 executions of the stored procedure, the Memory Grant has been reduced to 1.7 MB only!

What would happen if you would call the same stored procedure, but with a non-selective parameter? To check this, you can execute the following code:

EXEC dbo.GetOrders '20000101';
GO 2

The execution plan, including memory grant information, is shown as follows:

You can see that after an additional two executions of the stored procedure, with non-selective parameters, the Memory Grant has been corrected again; this time it has increased to 596 MB. For parameter-sensitive plans, if actual calls frequently use (very) different parameters, memory grant feedback will disable itself on a query if it has unstable memory requirements. The plan is disabled after several repeated runs of the query and this can be observed by monitoring the memory_grant_feedback_loop_disabled extended event.

Batch mode adaptive memory grant feedback corrects the initial memory grant as follows:

• Overestimated memory grants: If the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant and update the cached plan.
• Underestimated memory grants: If the granted memory is too small and the operation is spilled to disk, memory grant feedback will calculate a new memory grant.

You can track memory grant feedback events using the memory_grant_updated_by_feedback extended event.