Automatic Plan Correction

Question

Post reply

Automatic Plan Correction

WayneS

SSC Guru

Points: 95460
More actions
November 18, 2017 at 4:50 pm

#340559

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

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

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 1

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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

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

WayneS SSC Guru Points: 95460 More actions · Answer 3

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 5

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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 7

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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

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?"

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 9

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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 10

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!

WayneS SSC Guru Points: 95460 More actions · Answer 11

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

WayneS SSC Guru Points: 95460 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 13

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)

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 14

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)