Execution Plans

Question

Execution Plans

Viewing 15 posts - 16 through 30 (of 53 total)

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

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

Excellent Article.

I Like the way you explain, example helps more than 100' s of lines of description. Obviously the target audience is sql server dba/developer so it's always good not to start every article with how to create indexes. More articles are expected from you.

Thanks,

Sameer Raval

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

buktseat SSChasing Mays Points: 611 More actions · Answer 2

Clearly the writer is knowledgable, but this cat definitely needs a proofreader or a ghost-writer or something... Very hard to understand and follow at times.

William Kimball Grasshopper Points: 24 More actions · Answer 3

In reply to: "I would also like to see some examples of when to use clustered index and when to use non-clustered index if possible."

Start with the behavior of the two different index types and the answer starts to become clear.

1. The "natural order" of records in a table is the physical order of the records in the table as they would be scanned from first to last without an index or sort order.

2. A "clustered index" physically reorganizes every record in the table such that the "natural order" of the records in the table becomes the logical sort order of the clustered index. A seperate index records at which record the selected column value(s) change. This is why there can be only one "clustered index" per table.

3. A "non-clustered index" leaves the records as they are in the table and creates a seperate lookup tree of only the column(s) for which the index is built. Each node of this tree points to the location in the table of the corresponding record value.

In short:

1. Use a "clustered index" only on values that are highly repetetive in a table and for which you often select sequential ranges of data. The "clustered index" will then very quickly return entire blocks of matching records.

2. Use a regular index on values that are generally distinct and for which you often return individual records.

Rule of Thumb: If you seek values from a table based on a WHERE clause of a single column, be sure to have an index on it. If the values of the column are highly repetitive, use a clustered index. A column that will have a distinct value for every record is a poor candidate for a clustered index.

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

here's a link to Chris's article on cluster that index

**ASCII stupid question, get a stupid ANSI !!!**

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 5

You can force a Clustered Index Seek by adding a seemingly superfluous WHERE clause that searches for values in that index between two values.

For example, if the clustered index was on a field called StateId and StateId was a TINY INT then we could write add a WHERE clause that says

WHERE StateId BETWEEN 0 AND 255

You can get a performance boost this way but a clustered index seek DOES NOT always result in a lower cost query. You have to experiment with your application to see what the affect is.

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 6

I felt that this could be confusing for a beginner because the author was trying to optimize a simple query that couldn't be optimized.

Instead the author changed the requirements of the query to:

1. put all the data into a "covering index", so you get another full table scan, but in this case the table is known as an index (it's still a table)

2. put some constraints on the result set to allow the use of indexes that had been created

I really thought that this was backwards to change requirements to fit the technological implementation.

SyedAli SSC Rookie Points: 47 More actions · Answer 7

SyedAli

SSC Rookie

Points: 47

October 9, 2005 at 1:59 am

#596165

I like this type of Article. Excellent.

T.P.ANNAPURNA SSC Rookie Points: 37 More actions · Answer 8

Excellant article. I think theme of the article is more important than his English. Hope to c some more articles from Vijay Kumar

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 9

Nice article. Vijay, keep writing. Take this article to next level (explain different types of joins hints , covering index). I wish I get time to write on these lines.

Kindest Regards,

Amit Lohia

Glenn-266811 Old Hand Points: 362 More actions · Answer 10

Glenn-266811

Old Hand

Points: 362

October 12, 2005 at 4:00 pm

#596949

Good, informative and simple.

Cheers

Glenn

Anand Jeyapaul-229078 SSC Rookie Points: 41 More actions · Answer 11

Excellent article. Simple and Practical.Followup to this article necessary on when to use various scans etc.

Anand

Gilbert Grant SSChasing Mays Points: 619 More actions · Answer 12

This article was overly laborious and not cutting to the point. It would be nice to see articles on this site rated on level of expertise - e.g. beginner, intermediate etc. in relation to the subjects they discuss - so that one has an an indication as to whether or not one will learn anything from reading them. This would be a beginner's article.

shardul shah SSC Journeyman Points: 91 More actions · Answer 13

Thanks so much ...

Article has cleared all the doubt.

bijulsoni SSC Journeyman Points: 98 More actions · Answer 14

hello sir,

I read your article and found it was so much facinating.

i was familiar with the word Execution plan but not knowing much about it.

Your article give me brief information about How to utilize performance using clustered index seek Thanks a lot

Dejan Stamenkovic SSC Journeyman Points: 88 More actions · Answer 15

Found this article very average and a bit too self congratulating.