Adaptive Query Processing – Batch Mode Memory Grant Feedback

Question

Adaptive Query Processing – Batch Mode Memory Grant Feedback

WayneS

SSC Guru

Points: 95409
More actions
February 1, 2018 at 12:07 am

#410163

Comments posted to this topic are about the item Adaptive Query Processing – Batch Mode Memory Grant Feedback
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 1

What happens, if you call the procedure again with an ID which has only one entry (so that the granted memory is now much to high)? Will it lower the memory to grant again (which could lead to a ping-pong effect, if you call it several times with different IDs) or was the query plan marked as plan with increased memory?

And what happens, if I call it again with a really big ID (which would lead to another spill to tempdb), would it increase the granted memory a second (and maybe later a third time)?

God is real, unless declared integer.

WayneS SSC Guru Points: 95409 More actions · Answer 2

t.franz - Thursday, February 1, 2018 1:04 AM
What happens, if you call the procedure again with an ID which has only one entry (so that the granted memory is now much to high)? Will it lower the memory to grant again (which could lead to a ping-pong effect, if you call it several times with different IDs) or was the query plan marked as plan with increased memory?
And what happens, if I call it again with a really big ID (which would lead to another spill to tempdb), would it increase the granted memory a second (and maybe later a third time)?

If the memory grant in the cached plan is 2x > the memory required, then yes, it will lower it. Any spill to disk will bump it up, so it could go up in increasing amounts. Therefore, it is possible that you could have a ping-pong effect - up to a point. If the plan has an unstable memory requirement because of the variance of parameters used, then the memory grant feedback will disable itself for that plan. You can see this with the memory_grant_feedback_loop_disabled XEvent.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jon Patterson Valued Member Points: 74 More actions · Answer 3

Hi WayneS,

1. What kind of variances, and how many, before the SQL Server engine disables memory grant feedback on a procedure?
2. It appears that during the initial execution in your example, the memory grant feedback was displayed.. and then subsequent executions use the feedback. Are there any recommendations to follow to have this memory grant feedback initiated so that the users do not have to endure the less than adequate plan/memory optimization for the initial execution?

Thank you

WayneS SSC Guru Points: 95409 More actions · Answer 4

Jon Patterson - Thursday, February 1, 2018 10:05 AM
Hi WayneS,
1. What kind of variances, and how many, before the SQL Server engine disables memory grant feedback on a procedure?
2. It appears that during the initial execution in your example, the memory grant feedback was displayed.. and then subsequent executions use the feedback. Are there any recommendations to follow to have this memory grant feedback initiated so that the users do not have to endure the less than adequate plan/memory optimization for the initial execution?
Thank you

Hi Jon,

1. All that BOL states is "The plan is disabled after several repeated runs of the query". I'll see if I can find out any further information about this and will report back.
2. Check out Trace Flag 9389 at https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql - it looks like that will do it if memory is available.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jasinski.andrzej SSC Rookie Points: 46 More actions · Answer 5

From what I remember sql will do around 30 (32 to be exact?) rounds before disabling feedback.
There is one pretty important thing - if your query will timeout because of reduced memory grant, SQLServer won't emit new grant and you will be stuck.

WayneS SSC Guru Points: 95409 More actions · Answer 6

jasinski.andrzej - Thursday, March 29, 2018 4:03 AM

From what I remember sql will do around 30 (32 to be exact?) rounds before disabling feedback.
There is one pretty important thing - if your query will timeout because of reduced memory grant, SQLServer won't emit new grant and you will be stuck.

[/quote]

I had asked the SQL team at Microsoft about this. They won't release the number, saying it's internal and subject to change as they see how it is being used.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jasinski.andrzej SSC Rookie Points: 46 More actions · Answer 7

This makes sense, considering how 'fresh' this feature is. I will try to verify the number, it was fairly easy to do.
For me the bigger issue was that grants are updated only after successful execution, and on Azure TF9389 is not available as Db setting (you have to contact support to enable it for whole subscription)