Inside the Optimizer: Constructing a Plan - Part 4

  • 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]

  • 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/

  • 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

  • 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

  • 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.

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

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

  • 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

  • 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

  • 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

  • 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.

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

    πŸ˜‰

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

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

  • 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 15 total)

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