Adaptive Query Processing – Batch Mode Memory Grant Feedback

  • WayneS

    SSC Guru

    Points: 95342

    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

  • Thomas Franz

    Hall of Fame

    Points: 3565

    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: 95342

    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

    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: 95342

    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

    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: 95342

    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

    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)

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

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