Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-02-16 : 08:34:44
|
| Hi Team,I am working with SQL Server 2005. I am trying to learn the art of ‘query execution plan analysis’ to find bottlenecks. Though I am good in writing SQL, I have never looked execution plans. Can you please suggest some articles that give STEP-BY_STEP guidelines for learning execution plan. It will be good if it is an article that starts with simple examples and build progressively.ThanksLijo Cheeran Joseph |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 09:03:14
|
Here's my two-pennywortrh:Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats-- SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ON... *** YOUR QUERY HERE *** ...SET STATISTICS IO OFF; SET STATISTICS TIME OFFGOSET SHOWPLAN_TEXT OFFGO Check the Logical I/O and number of scans first (ignore the physical I/O - that will change each time you run the query, the Logical I/O is therefore more representative)You can re-check the Logical I/O and Scans when you make a change to see if it has improved thingsFocus on any table that has large numbers of scans and/or logical I/ONOTE: Using Statistics actually RUNS the query - be careful if using an UPDATE or DELETE etc., or use BEGIN TRANSACTION ... ROLLBACK so you can repeat it any number of times.Then run the SHOWPLAN (this will NOT actually run the query)Look for Table Scan, Index Scan or Index Seeko Table Scan and Index Scan "thumb through all the pages" looking for the entry.o Index Seek goes straight to the entry(ies) - and thus is better.Also check that the Index being used for each part of the query is the one you expect. In particular, the Clustered Index (usually the Primary Key) is used when there is no other choice.Examples:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138788#542010http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138385#540109http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83395#306129http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84347#310486 |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 10:18:09
|
Personally I think there is too much information in the Graphical Plan for a first-cut, and its particularly hard to wade through for an SProc that starts off with a few SET NOCOUNT type statements and a few Assignments etc. Each gets its own tiny graphical window with scroll bars all around - or is there a way to explode a single part out of the Execution Plan tab that I haven't found yet?IMHO First priority is:1) Get a baseline Logic I/Os and Scans to know if enhancements improve things or not (percentages are not much use for basep-line comparison)1) Is the query using Table or Index Scans, or is it using Index Seek.2) Is it using the expected index (or, indeed "No index"  )The graphical query is laid out in a way that looks extremely impressive, but it is hard to see those things without mouse waving to get the mouse-over yellow data-tips, and Horizontal / Vertical scrolling; plus all the Sorts / Filters etc. make it even more spread outThe Sorts / Filters may, themselves, indicate inefficiency (and thus can be helpful of course), but mostly that is easily spotted when No Index, or an Unexpected-index, is seen to be used.But that's just how I go about it ... |
|
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
|
|
|
|
|
|
|
Execution Plans - Step By Step