Analysing a T-SQL Query

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

  • This was removed by the editor as SPAM

  • 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

  • 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

  • Really interesting question and explanation, thanks.

  • Great question, made me work for my points.

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

  • 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

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

  • 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

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

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

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

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

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

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

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