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