Inside the Optimizer: Constructing a Plan - Part 4

  • Paul White

    SSC Guru

    Points: 150442

    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]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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

    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

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

    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

    SSC Guru

    Points: 150442

    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

    SSC Guru

    Points: 150442

    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. πŸ™‚

  • TheSQLGuru

    SSC Guru

    Points: 134017

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

    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

  • Christian Buettner-167247

    SSChampion

    Points: 13729

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

    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.

  • Lawrence Moore

    Ten Centuries

    Points: 1203

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

    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 πŸ˜‰

  • Cliff Jones

    SSChampion

    Points: 10517

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

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

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

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