Analysing a T-SQL Query

Question

Analysing a T-SQL Query

JayK

SSCrazy

Points: 2679
More actions
May 4, 2010 at 10:39 pm

#234655

Comments posted to this topic are about the item Analysing a T-SQL Query
May 5, 2010 at 12:26 am

This was removed by the editor as SPAM

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

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

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 1

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: 281334 More actions · Answer 2

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 More actions · Answer 3

Really interesting question and explanation, thanks.

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 4

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: 5276 More actions · Answer 5

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 More actions · Answer 6

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: 281334 More actions · Answer 7

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: 39972 More actions · Answer 8

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

adalberto-339588 SSC Veteran Points: 261 More actions · Answer 9

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

Chris Stewart-397033 SSCrazy Points: 2040 More actions · Answer 10

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: 6193 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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