SQL Server 2017 Adaptive Memory Grant Feedback for Stored Procedure Performance Improvements

By: Rajendra Gupta | Comments | Related: > SQL Server 2017

Problem

SQL Server database performance optimization is always a key task for DBAs. In my previous tip, we have seen the benefit of Adaptive Query Processing in SQL Server 2017. Since SQL Server stored procedures are compiled and their execution plan is cached then these plans are re-used. Therefore stored procedures are always preferable for optimal performance. Unfortunately, sometimes stored procedures might experience a performance issue or insufficient memory grants due to the size of the result set. In this tip we will see the performance benefit for Adaptive Memory Grant Feedback that is being introduced in SQL Server 2017.

Solution

Introduction to SQL Server 2017

SQL Server 2017 represents a major step towards making SQL Server a platform that gives you choices of development languages, data types, on-premises and in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers and Windows. Microsoft announced the first Community Technology Preview (CTP 1.0) of SQL Server vNext on November 16, 2016, it runs on Windows, Linux (Redhat, SUSE, Ubuntu) and also runs on Docker and MacOS. SQL Server vNext also includes the features added in SQL Server 2016 Service Pack 1. As SQL Server vNext is in preview stage at the time of writing, it can be downloaded as an 180 free evaluation version from the Microsoft website.

In April 2017, Microsoft released Community Technology Preview (CTP) 2.0 of SQL Server vNext. Microsoft officially announced that this Community Technology Preview (CTP) 2.0 of SQL Server vNext will be called SQL Server 2017.

SQL Server 2017 preview can be downloaded from here.

Adaptive Memory Grant Feedback

The SQL Server optimizer generates an optimized query plan for stored procedures and caches the plan. For subsequent stored procedure executions, this precompiled cached plan is re-used. Let's consider a scenario in which a stored procedure is executed the first time with a parameter resulting in a small amount of data, but a later execution of the stored procedure returns a large result set. In the first execution the SQL Server optimizer memory grant is based on a small result set, this might cause performance issues when a large result set is returned.

In SQL Server 2017 with the Adaptive Memory Grant Feedback feature this enables the SQL Server Query Processing Engine to identify if the memory grants are not sufficient and then the modify the cached execution plan with the updated memory grants so that the execution should benefit from the new memory grants.

As per MSDN, this is how batch mode adaptive memory grant feedback works:

For excessive 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. Plans with memory grants under 1MB will not be recalculated for overages.
For insufficiently sized memory grants that result in a spill to disk for batch mode operators, memory grant feedback will trigger a recalculation of the memory grant.

Demo of Adaptive Memory Grant Feedback with Insufficiently Sized Memory Grants

Execution 1 - SQL Server 2016 Compatibility Level with Small Result Set

First we will run the query in SQL Server 2016 compatibility level 130 and observe the memory allocation.

Run the below query with its Actual Execution Plan (Execution No -1)

 --Set the Compatibility level to SQL server 2017 - 130.
Alter Database WideWorldImporters set compatibility_level = 130;

--Clear the procedural cache
Alter Database scoped configuration clear procedure_cache;

USE [WideWorldImporters]
GO

DECLARE @return_value int

EXEC @return_value = [Integration].[GetTransactionUpdates]
  @LastCutoff = '2014-01-01 00:00:00.000000',
  @NewCutoff = '2014-01-02 00:00:00.000000'

SELECT 'Return Value' = @return_value
GO

Now we have the cached execution plan for the stored procedure above with Actual number of rows 114. Now we will execute the stored procedure again with different parameter to have large result set.

Execution 2 - SQL Server 2016 Compatibility Level with Large Result Set

USE [WideWorldImporters]
GO

DECLARE @return_value int

EXEC @return_value = [Integration].[GetTransactionUpdates]
  @LastCutoff = '2014-01-01 00:00:00.000000',
  @NewCutoff = '2016-01-02 00:00:00.000000'

SELECT 'Return Value' = @return_value
GO

The above result set contains 60344 rows, however we can see here the memory allocation is still 1024 KB. So once we have cached the execution plan, it will be used regardless of the result sets in later executions. We might need to recompile the stored procedure to regenerate the execution plan in that case.

SQL Server 2017 Adaptive Memory Grant Feedback

SQL Server 2017 will take advantage of Adaptive Memory Grant feedback as discussed above.

Now we will clear the cache and run the same queries in SQL Server 2017 Compatibility level 140.

Execution 1 - SQL Server 2017 with Small Result Set

--Set the Compatibility level to SQL server 2017- 140.
Alter Database WideWorldImporters set compatibility_level = 140;

--Clear the procedural cache
Alter Database scoped configuration clear procedure_cache;

USE [WideWorldImporters]
GO

DECLARE @return_value int

EXEC @return_value = [Integration].[GetTransactionUpdates]
  @LastCutoff = '2014-01-01 00:00:00.000000',
  @NewCutoff = '2014-01-02 00:00:00.000000'

SELECT 'Return Value' = @return_value
GO

Execution 2 - SQL Server 2017 with Large Result Set

This time we will run the stored procedure with different parameter to have large result set.

USE [WideWorldImporters]
GO

DECLARE @return_value int

EXEC @return_value = [Integration].[GetTransactionUpdates]
  @LastCutoff = '2014-01-01 00:00:00.000000',
  @NewCutoff = '2016-01-02 00:00:00.000000'

SELECT 'Return Value' = @return_value
GO

We can see here that as the result set is increased, memory allocation is also adjusted accordingly to meet the requirements. Increased memory allocation is 13440 KB instead of 1024 KB.

Using the SQL Server compare showplan option, we can compare both the execution plan memory allocations.

Demo of Adaptive Memory Grant Feedback for Excessive Memory Grant Allocations

If a stored procedure is cached with Excessive Memory Grant Allocations, the SQL Server 2017 will adjust the memory allocations towards a lower value so that the remaining memory can be utilized for other operations.

Execution 1 - SQL Server 2017 with Large Result Set

 --Set the Compatibility level to SQL server 2017- 140.
Alter Database WideWorldImporters set compatibility_level = 140;

--Clear the procedural cache
Alter Database scoped configuration clear procedure_cache;

USE [WideWorldImporters]
GO

DECLARE @return_value int

EXEC @return_value = [Integration].[GetSaleUpdates] 
  @LastCutoff = '2014-01-01 00:00:00.000000',
  @NewCutoff = '2016-12-31 00:00:00.000000'

SELECT 'Return Value' = @return_value
GO

If we run the same query again in SQL Server 2017 with different parameters to return less rows, we can see that memory adjustments are made to lower the memory.

Execution 2 - SQL Server 2017 with Small Result Set

USE [WideWorldImporters]
GO

DECLARE @return_value int

EXEC @return_value = [Integration].[GetSaleUpdates] 
  @LastCutoff = '2014-01-01 00:00:00.000000',
  @NewCutoff = '2014-12-31 00:00:00.000000'

SELECT 'Return Value' = @return_value
GO

If we execute the query again, we can see that further memory adjustments are made.

Using the SQL Server compare showplan option, we can see how the memory allocation varies in each run.

The adaptive memory grant feedback feature is one of the Adaptive Query Processing features available in SQL Server 2017 with Database Compatibility Level 140 to properly allocate memory based on the result set. This feature can yield performance gains with an appropriate amount of memory based on the stored procedure result set.

Next Steps

We will explore more about SQL Server 2017 in future tips.
Check out my related tip - Adaptive Query Processing in SQL Server 2017.
Explore SQL Server 2017 preview.
Read more about SQL Server vNext Linux Tips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips