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/
I spent a great deal of time to locate something like this
I spent a lot of time to locate something similar to this
I spent a lot of time to locate something like this
It works really well for me
It works really well for me
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
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
Wow! Thank you! I permanently wanted to write on my website something like that. Can I include a portion of your post to my blog?
Sure
Good post. I’m dealing with some of these issues as well..
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.