Analysing a T-SQL Query

  • JayK

    SSCrazy

    Points: 2679

    Comments posted to this topic are about the item Analysing a T-SQL Query

  • This was removed by the editor as SPAM

  • Kari Suresh

    Hall of Fame

    Points: 3712

    Good Question to analysis..

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • SQLRNNR

    SSC Guru

    Points: 281205

    This is a good question. Thanks. One other method one can use to demonstrate this is by evaluating the actual execution plan.

    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

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Really interesting question and explanation, thanks.

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    Great question, made me work for my points.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

    CirquedeSQLeil (5/5/2010)


    This is a good question. Thanks. One other method one can use to demonstrate this is by evaluating the actual execution plan.

    Very good question, thank you. I did not try running the sample yet but I suspect that because the query is pretty simple, highlighting it in SSMS and hitting CTRL + L (to let SSMS show estimated plan) will do the trick as well because there should not be any differences between actual and estimated plans in this case.

    Oleg

  • JayK

    SSCrazy

    Points: 2679

    Just noticed a typo in the question.

    The first line of text should read "non clustered index on EmailAddress" and not "non clustered index on LastName".....this script itself is OK....

  • SQLRNNR

    SSC Guru

    Points: 281205

    Oleg Netchaev (5/5/2010)


    CirquedeSQLeil (5/5/2010)


    This is a good question. Thanks. One other method one can use to demonstrate this is by evaluating the actual execution plan.

    Very good question, thank you. I did not try running the sample yet but I suspect that because the query is pretty simple, highlighting it in SSMS and hitting CTRL + L (to let SSMS show estimated plan) will do the trick as well because there should not be any differences between actual and estimated plans in this case.

    Oleg

    That is likely true - haven't tried. I am just accustomed to asking for the actual execution plan that it seemed the safer route for this too.;-)

    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

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    Wonderful question and equally wonderful explanation. The basics is hit in the nail! Thanks for this!

  • adalberto-339588

    SSC Veteran

    Points: 261

    I think the Answer is ( 0 ) Bz the field QOTDID don't exist, there no Update.

  • Chris Stewart-397033

    SSCrazy

    Points: 2040

    An unusual situation occurred for me. The first time I ran the SELECT statement the plan was a Clustered Index Scan. Subsequent runs were the expected NC Index Seek & Key Lookup. It took me a moment to realize the cause - it was due to having the following database settings set:

    Auto Create Statistics - On

    Auto Update Statistics - On

    Auto Update Statistics Asynchronously - On

    Testing with the various settings yields these results:

    If you do not Auto Create stats you only get a clustered index scan.

    If you Auto Create but do not Auto Update you also only get a clustered index scan.

    If you Auto Update stats (synchronously) you get the results in the answer (NC Index Seek & Key Lookup).

    And finally if you Auto Update Statistics Asynchronously the first run is clustered index scan with subsequent runs NC Index Seek & Key Lookup.

  • Tom Garth

    SSCertifiable

    Points: 6173

    Got lucky and got the points, but mainly I learnt something.

    Good QotD.

    Thanks,

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Joseph D. Marsh

    SSC Veteran

    Points: 257

    DAMN -- got it wrong! Know why? Because I over-thought the answer: I though that since the counter (@i) started at 0 and incremented to 499, there would never be a record with QOTDID = 500.

    Unfortunately, I forgot that the QOTDID field started at 1, so the very last record gets an id of 500.

    Damn, Sam!

    Thanks,
    - Joseph Marsh

  • UMG Developer

    SSChampion

    Points: 13482

    Great question, gets you to actually thinking about what SQL Server is doing in the background.

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

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