Adaptive query processing – Batch mode memory grant feedback

MS SQL has introduced adaptive query processing to get the rid of inaccurate cardinality estimates. It includes batch mode memory grant feedback,interleaved execution and batch mode adaptive joins. I have already written an another blog on Adaptive join, Please click Here to read the blog. In this blog, We will try to understand batch mode memory grant feedback.

Introduction: SQL Server 2017 and Azure SQL Server has a new way to handle the problem of 'Excessive Grant' or 'insufficient Grant' of memory. SQL server keeps the procedure or parametric query plan stores in cache to reuse the plan for the next execution.

Procedures with different parameter values may need a different memory grant, but memory grant won't be updated in SQL 2016 or lower version until the old plan is not changed due to some other reason.In SQL 2017, whenever optimizer finds the problem related to Excessive grant or insufficient Grant, It modifies the plan with appropriate grant values, which helps to solve the problems related parameter sniffing up to some extent.

How it 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 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.

Requirement(s):

1. SSMS 17.0 or newer
2. Database compatibility level 140

DEMO:

I am using AdventureWorks2016CTP3 Database for demo.

Lets create a stored procedure first


CREATE OR ALTER PROCEDURE sp_MemoryGrantFeed
@EventDetails VARCHAR(150)
AS
BEGIN
SELECT
Count(OT.SalesOrderID)
FROM
Sales.SalesOrderDetail SOD
INNER JOIN
sales.OrderTracking OT
ON OT.SalesOrderID=SOD.SalesOrderID
Where
OT.EventDetails=@EventDetails
END

We will check the behavior for compatibility level 130 first (SQL 2016 compatible)


USE [MASTER]
GO
ALTER DATABASE [AdventureWorks2016CTP3] SET COMPATIBILITY_LEVEL = 130
GO

Clear the procedure cache
ALTER DATABASE SCOPED CONFIGURATION CLEAR procedure_cache;

Execute the procedure with a parameter value which will return less number of rows


DECLARE @EventDetails VARCHAR(150)='invalid address, package is undeleverable'
EXEC sp_MemoryGrantFeed @EventDetails


DECLARE @EventDetails VARCHAR(150)='invalid address, package is undeleverable'
EXEC sp_MemoryGrantFeed @EventDetails

It is clear from execution plan that memory grant value is same for both parameter value.

Now, Lets see the behavior for compatibility level 140 (SQL Server 2017 compatible)


ALTER DATABASE SCOPED CONFIGURATION CLEAR procedure_cache;
GO
ALTER DATABASE [AdventureWorks2016CTP3] SET COMPATIBILITY_LEVEL = 140
GO

Lets execute the procedure again.


DECLARE @EventDetails VARCHAR(150)='Order has been processed and packaged for shipment'
EXEC sp_MemoryGrantFeed @EventDetails


DECLARE @EventDetails VARCHAR(150)='invalid address, package is undeleverable'
EXEC sp_MemoryGrantFeed @EventDetails

As you can see that optimizer has modified the memory grant value 37544 KB to 4680 KB. Although , Execution plan is still showing warning of "Excessive Grant" but estimates are much closer to actual value now.

Follow me:

Blog: http://Sqldepository.com

YouTube : Sql depository

Facebook: https://www.facebook.com/sqldepository/

Google+ :https://plus.google.com/u/0/107093915534620159617

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

11 thoughts on “Adaptive query processing – Batch mode memory grant feedback

  1. Hi,

    I hope you are doing well. I am Jerry, product manager at DB Designer and I am reaching out to bloggers and business owners in this space for quick intro.

    We have a very large and loyal user base for our application but have never focused on content and marketing. We are trying to expand our blog so we are interested in exploring any marketing opportunities including partnerships, guest posting, sponsored posts or any other mutually beneficial opportunities.

    I respect your time and prefer to keep my first contact short. If you are interested in working with us, please let me know and I can get back to you with more details or we can schedule a quick intro call to discuss this further. 

    Looking forward to your reply. Thank you

    Best Regards,

    Jerry Ullah
    jerry@dbdesigner.net
    https://www.dbdesigner.net

  2. Hello there, My name is Aly and I would like to know if you would have any interest to have your website here at sqldepository.com promoted as a resource on our blog alychidesign.com ?

    We are updating our do-follow broken link resources to include current and up to date resources for our readers. If you may be interested in being included as a resource on our blog, please let me know.

    Thanks, Aly

  3. Hello! Someone in my Facebook group shared this site with us so I came to give it a look. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers! Exceptional blog and terrific design and style.

Leave a Reply

Your email address will not be published.