Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

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


By:   |   Read Comments   |   Related Tips: More > 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
SQL Server 2017 Execution Plan

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
SQL Server 2017 Execution Plan

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
SQL Server 2017 Execution Plan

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
SQL Server 2017 Execution Plan

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.

SQL Server 2017 Execution Plan

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
SQL Server 2017 Execution Plan

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
SQL Server 2017 Execution Plan

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

SQL Server 2017 Execution Plan

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

SQL Server 2017 Execution Plan

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


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools