Inside the Optimizer: Constructing a Plan - Part 2

  • Paul White

    SSC Guru

    Points: 150442

    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]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • skyline6969

    SSC Veteran

    Points: 276

    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 :(.

  • bpportman 52825

    SSC Eights!

    Points: 864

    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'."

  • aeyates

    Valued Member

    Points: 70

    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.

  • Rick-153145

    SSCrazy

    Points: 2713

    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

  • Paul White

    SSC Guru

    Points: 150442

    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! 😉

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Paul White

    SSC Guru

    Points: 150442

    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:

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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