Automatic Plan Correction

  • Comments posted to this topic are about the item Automatic Plan Correction

    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

  • As always, great article with working examples!  Thanks for taking the time to post it Wayne.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Great article, very helpful. Well done Wayne, 5 stars!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks Jeff and Alan. I'm glad that you liked it.

    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

  • 5 stars from me as well... 
    Legend has it, the new system will also be able to recommend altering to existing indexes and/or suggest filtered indexes... Basically a DTA that doesn't suck...
    So, I'll definitely be keeping an eye out for so see if this becomes a series.

  • Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    ... and the genius index naming convention :hehe:

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B - Monday, November 20, 2017 7:11 PM

    Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    ... and the genius index naming convention :hehe:

    BWAAAA-HAAAAA!!!!!  I was just thinking that and also thinking "thank goodness it wasn't XML". 😉

    I'm also thinking they HAD to name the way they did or their recommendations for keys and includes would very quickly outstrip the 128 character object limit if actual column names were used... especially with how some people name columns.  At least they used something logical that could actually be split and rendered to come up with the column names.

    Of course, Index001, Index002, etc, might not have been the end of the world.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    That was pretty much my reaction when I heard about it about a year ago from our MS Support Engineer... 
    IIRC, it's supposed to be able to implement it's recommendations, including the alter index, automagically. 
    I think my 1st comment was, "Sounds fantastic! ...<something about DTA making equally glorious promise... laughing a little>... What will we need to do to turn it off?"

  • Jason A. Long - Monday, November 20, 2017 7:47 PM

    Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    That was pretty much my reaction when I heard about it about a year ago from our MS Support Engineer... 
    IIRC, it's supposed to be able to implement it's recommendations, including the alter index, automagically. 
    I think my 1st comment was, "Sounds fantastic! ...<something about DTA making equally glorious promise... laughing a little>... What will we need to do to turn it off?"

    Too funny!  After I read Wayne's good article, the first thing that came to mind was "How do I turn it off"? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden - Monday, November 20, 2017 7:53 PM

    Jason A. Long - Monday, November 20, 2017 7:47 PM

    Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    That was pretty much my reaction when I heard about it about a year ago from our MS Support Engineer... 
    IIRC, it's supposed to be able to implement it's recommendations, including the alter index, automagically. 
    I think my 1st comment was, "Sounds fantastic! ...<something about DTA making equally glorious promise... laughing a little>... What will we need to do to turn it off?"

    Too funny!  After I read Wayne's good article, the first thing that came to mind was "How do I turn it off"? 😉

    It's been said before but It's worth saying again... Great minds really do think alike!

  • Jeff Moden - Monday, November 20, 2017 7:53 PM

    Jason A. Long - Monday, November 20, 2017 7:47 PM

    Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    That was pretty much my reaction when I heard about it about a year ago from our MS Support Engineer... 
    IIRC, it's supposed to be able to implement it's recommendations, including the alter index, automagically. 
    I think my 1st comment was, "Sounds fantastic! ...<something about DTA making equally glorious promise... laughing a little>... What will we need to do to turn it off?"

    Too funny!  After I read Wayne's good article, the first thing that came to mind was "How do I turn it off"? 😉

    You did see in the article how to turn it on, right?
    ALTER DATABASE current
    SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

    Well, just set the ON to OFF there.
    ALTER DATABASE current
    SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );

    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

  • Jason A. Long - Monday, November 20, 2017 5:13 PM

    5 stars from me as well... 
    Legend has it, the new system will also be able to recommend altering to existing indexes and/or suggest filtered indexes... Basically a DTA that doesn't suck...
    So, I'll definitely be keeping an eye out for so see if this becomes a series.

    [/quote]

    This is available in Azure SQL Database right now. I would expect to see this in an upcoming version of the on-premises SQL Server. Note that you can disable index creation and/or index dropping separately. See https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

    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

  • WayneS - Tuesday, November 21, 2017 8:04 AM

    Jeff Moden - Monday, November 20, 2017 7:53 PM

    Jason A. Long - Monday, November 20, 2017 7:47 PM

    Jeff Moden - Monday, November 20, 2017 7:04 PM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    Basically a DTA that doesn't suck...

    Heh... THAT, my friend, has yet to be seen both on the regressive plans and the index recommendations. 😉

    That was pretty much my reaction when I heard about it about a year ago from our MS Support Engineer... 
    IIRC, it's supposed to be able to implement it's recommendations, including the alter index, automagically. 
    I think my 1st comment was, "Sounds fantastic! ...<something about DTA making equally glorious promise... laughing a little>... What will we need to do to turn it off?"

    Too funny!  After I read Wayne's good article, the first thing that came to mind was "How do I turn it off"? 😉

    You did see in the article how to turn it on, right?
    ALTER DATABASE current
    SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

    Well, just set the ON to OFF there.
    ALTER DATABASE current
    SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );

    Heh... yeah... Not only did I figure that but made sure by looking it up.  I was just expressing my thought when I first read how to turn it on. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • WayneS - Tuesday, November 21, 2017 8:08 AM

    Jason A. Long - Monday, November 20, 2017 5:13 PM

    5 stars from me as well... 
    Legend has it, the new system will also be able to recommend altering to existing indexes and/or suggest filtered indexes... Basically a DTA that doesn't suck...
    So, I'll definitely be keeping an eye out for so see if this becomes a series.

    This is available in Azure SQL Database right now. I would expect to see this in an upcoming version of the on-premises SQL Server. Note that you can disable index creation and/or index dropping separately. See https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

    [/quote]

    I did read that article previously and was a bit disappointed that they hadn't already pushed it to on-premise without a full blown version change.  I realize this has become the hot new trend for MS release patterns but you would think that their code base would be similar enough that they could push it to on-premise a bit more quickly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 17 total)

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