Inside the Optimizer: Constructing a Plan - Part 2

  • Comments posted to this topic are about the item Inside the Optimizer: Constructing a Plan - Part 2[/url]

    Link to Part 1[/url]

    Link to Part 3[/url]

    Link to Part 4[/url]

  • My sole critisicm of this article is that it's much too short.

    I can't wait to read the parts three and four!


    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/

  • Good article Paul, look forward to reading more on it!

    I also would like to see more articles like this as I had studied general database engine low-level workings and theories (17 years ago I wrote a thesis on using compressed caches in database engines, never thought it would take this long to see even basic compression used - SQL 2008 being Microsoft's first real go at this lol).

    It is great to see how SQL Server applies them, as outside open source code and Microsoft white papers it is rarely discussed now :(.

  • Good article, but there is not enough information.

    How are these done?

    "The first one is achieved by a rule called JNtoIdxLookup. The second requirement is a correlated loops join - also known as an Apply. The rule needed to transform our query to that form is AppIdxToApp."

    "We can remove this Compute Scalar, and the need to compute COUNT(*), by normalising the GROUP BY using a rule called 'NormalizeGbAgg'."

  • Do you have before/after timings?

    Was the query statement unchanged?

    Did you add an index?

    It is not clear how the query was improved.

  • Great series, looking forward to the next two instalments.

    I feel I must point out this great typo though, made me chuckle:

    "1. Convert the naive nested loops join to an index nested loops join "

    :-D:-D

  • bpportman 52825 (9/9/2010)


    Good article, but there is not enough information. How are these done?

    That would leave me nothing to say in Parts 3 & 4! 😉

  • aeyates (9/9/2010)


    Do you have before/after timings?

    No.

    Was the query statement unchanged?

    Yes.

    Did you add an index?

    No.

    It is not clear how the query was improved.

    The series is about how the query optimizer works internally. I am showing some of the plan alternatives it considers on the way to finding the final plan. Part 3 and Part 4 of this series will reveal the magic.

  • Rick-153145 (9/9/2010)


    I feel I must point out this great typo though, made me chuckle:

    "1. Convert the naive nested loops join to an index nested loops join "

    I still can't see a typo there, Rick.

  • Hugo Kornelis (9/9/2010)


    My sole critisicm of this article is that it's much too short.

    I can't wait to read the parts three and four!

    Thank you, Hugo.

  • Once again, great job.

    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

  • Paul White NZ (9/9/2010)


    Rick-153145 (9/9/2010)


    I feel I must point out this great typo though, made me chuckle:

    "1. Convert the naive nested loops join to an index nested loops join "

    I still can't see a typo there, Rick.

    I'm missing it too. Maybe it was read as "Knave" instead as "naive"

    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

  • CirquedeSQLeil (9/9/2010)


    I'm missing it too. Maybe it was read as "Knave" instead as "naive"

    On reflection, I think the problem is simply lack of familiarity with the term. I put a Books Online reference on the same line in the article to explain "naive nested loops join"...:Whistling:

  • Paul White NZ (9/9/2010)


    CirquedeSQLeil (9/9/2010)


    I'm missing it too. Maybe it was read as "Knave" instead as "naive"

    On reflection, I think the problem is simply lack of familiarity with the term. I put a Books Online reference on the same line in the article to explain "naive nested loops join"...:Whistling:

    Possible. The reference should help to clarify.

    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

  • Definately looking forward to parts 3 and 4, thanks for the articles!


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

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

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