Inside the Optimizer: Constructing a Plan - Part 4

Question

Inside the Optimizer: Constructing a Plan - Part 4

Paul White

SSC Guru

Points: 150468
More actions
September 15, 2010 at 10:49 pm

#229303

Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 4[/url]
Link to Part 1[/url]
Link to Part 2[/url]
Link to Part 3[/url]
Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Thanks, Paul, for an excellent series! I rarely give the full five stars to an article (yes, I am criticall), but you have absolutely deserved them.

I don't think I'll ever want to actually disable any optimizer rules - but as you said at the end of the article, a better understanding of internals helps me become a better SQL Server professional.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 2

Awesome, just awesome.

Plus this:

In SQL Server 2005, SHOWOFFRULES displays a list of rules that are ON, and SHOWONRULES displays rules that are OFF, which is actually quite funny.

Just made my day! 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

This is really great stuff... As already mentioned a few times by others, 5 stars aint enough for this series...

Edit: The link back to the article from the forum actually points to part 3 of the series and not part 4.

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150468 More actions · Answer 4

Christian Buettner-167247 (9/16/2010)
This is really great stuff... As already mentioned a few times by others, 5 stars aint enough for this series...Edit: The link back to the article from the forum actually points to part 3 of the series and not part 4.

Fixed the link! Thank you for the kind words too.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 5

Matt Whitfield (9/16/2010)
Awesome, just awesome.
Plus this:
In SQL Server 2005, SHOWOFFRULES displays a list of rules that are ON, and SHOWONRULES displays rules that are OFF, which is actually quite funny.
Just made my day! 😀

Yep it still makes me smile 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 6

Hugo Kornelis (9/16/2010)
Thanks, Paul, for an excellent series! I rarely give the full five stars to an article (yes, I am criticall), but you have absolutely deserved them.

Fantastic! Thank you. BTW Steve Jones encouraged me to post these articles on SSC - you can find the originals on my blog, together with some other optimizer-related stuff people might find interesting...or not!

I don't think I'll ever want to actually disable any optimizer rules - but as you said at the end of the article, a better understanding of internals helps me become a better SQL Server professional.

Precisely. 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

From whence did you acquire this information Paul? Neat stuff!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150468 More actions · Answer 8

TheSQLGuru (9/16/2010)
From whence did you acquire this information Paul? Neat stuff!

Hey Kevin,

The four parts cover a lot of ground - did you have a particular piece of information in mind?

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 9

You already told us, you stole from your own blog 😛

But as an honest guess, I think you were just exploring SQL Server, and eventually found the DMV for the rules. Of course one important piece of information is the DBCC command list, maybe you got that from http://www.novicksoftware.com/tipsandtricks/tip-sql-dbcc-help.htm or just from http://msdn.microsoft.com/en-us/library/ms176040.aspx.

In general I assume there is no single source of information, just lots of experience & knowledge built over the years, and a lot of exploration to build that knowledge over time.

Or do you just have some really good contacts with MS?

I am curious 🙂

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150468 More actions · Answer 10

Christian Buettner-167247 (9/16/2010)
In general I assume there is no single source of information, just lots of experience & knowledge built over the years, and a lot of exploration to build that knowledge over time.

That's a pretty fair assessment.

Or do you just have some really good contacts with MS?

Sadly, no.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Lawrence Moore Ten Centuries Points: 1203 More actions · Answer 11

Excellent and very throught provoking series.

Do we think the mix up of RULEOFF/RULEON in SQL 2005 was a deliberate ploy or purely accidental by the MS developers?

😉

Paul White SSC Guru Points: 150468 More actions · Answer 12

Lawrence Moore (9/16/2010)
Do we think the mix up of RULEOFF/RULEON in SQL 2005 was a deliberate ploy or purely accidental by the MS developers?

It's usually safe to assume cock-up over conspiracy 😉

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Cliff Jones SSChampion Points: 10547 More actions · Answer 13

This is an outstanding series, thanks Paul for sharing this information.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Matt Whitfield (9/16/2010)
Awesome, just awesome.
Plus this:
In SQL Server 2005, SHOWOFFRULES displays a list of rules that are ON, and SHOWONRULES displays rules that are OFF, which is actually quite funny.
Just made my day! 😀

Agreed - quite funny.

ShowOffsRule

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events