4

When I look at the execution plan for a particular query I find that 77% of my cost is in a Clustered Index seek.

Does the fact that I'm using a clustered index mean that I won't see performance issues due to the columns that I am outputting?

Would it be better for me to create a Non-Clustered version of this and include all of the columns that are being output?

UPDATE: The clustered index uses a composite key. Not sure if this makes a difference.

Improve this question
10
  • this is way too little information to be able to give you a good recommendation. What do your tables look like? What fields? Which fields are indexed? What do your most common queries look like?? Commented Jan 22, 2010 at 19:27
  • cost is relative: Could you give us some more info? Commented Jan 22, 2010 at 19:27
  • Hrrm, what other info are you looking for? The subtree that takes up 77% of the cost uses about 97% of the elapsed time and about 60% of the CPU time. It ends up returning close to 600,000 records. Commented Jan 22, 2010 at 21:36
  • Hmmm, does a subtree of the query plan take up 77% or is it the clustered index seek itself taking up 77%? Your query is returning 600,000 rows; and I presume you're concerned about how long it takes (whatever that time is)? 600,000 rows is a fair amount of data. It will take a while to send that to the client. Have you confirmed whether it's execution time that's a problem, or transmission time that's a problem? You wouldn't want to waste too much time trying to improve a query when that's not the problem. Commented Jan 22, 2010 at 22:45
  • It is the index seek itself. How would I confirm that it's execution time and not transmission time? Commented Jan 22, 2010 at 22:55

3 Answers 3

Reset to default
4

The reason you use include columns on a non-clustered index is to avoid "bookmark-lookups" into the clustered data. The thing is that if SQL Server could theoretically use a particular non-clustered index but the Optimiser estimates there will be 'too many' bookmark-lookups then said index will be ignored. However, if all selected columns are accessible directly from the index, there'll be no need for a bookmark-lookup.

In your case the fact that you're accessing the data via "clustered index seek" is very promising. It will be very hard to improve on its performance. A non-clustered index including all selected columns may be slightly faster, but only because the raw data is a little less. (But don't forget the cost of increased insert/update time.)

However, you should check the detail...

  • If you're using a composite key and the seek is actually only on the beginning of the key, you might not be so lucky. You may find the seek is only narrowing down to 500,000 rows and is then searching that based on other criteria. In this case experiment with some non-clustered indexes.
  • The clustered index seek itself may be fine; but if it is being done 100,000 times in your query because some other aspect is inefficiently returning too many rows - then you won't gain much by improving the performance of the clustered index seek.

Finally, to elaborate on davek's comment: "cost is relative". Just because the clustered is 77% of your query cost doesn't mean there's a problem. It is possible to write a trivial 1 table query that returns a sinlge row and clustered index seek cost at 100%. (But of course, being the only 'work' done, it will be 100% of the work... and 100% of instant is still instant.
So: "Don't worry; be happy!"

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You're comment regarding the composite key is very interesting. This particular index is a composite key. Could you elaborate a little bit on what you mean by "experiment with non-clustered indexes"?
Let's suppose your clustered index is on (A, B, C) but your query filters on A and C. Then the clustered index can seek, but won't be used at optimal efficiency (i.e. it will only seek on A). A non-clustered index on (A,C) or (C,A) may prove more effective. If the filter returns a small number of rows; then great - a few bookmark-lookups and your query will run quite efficiently. However, if it returns many rows then book-mark lookups are no longer efficient, and the index will be ignored. So try adding include columns. Which ones to try first will depend on the rest of your query.
1

You have a seek already, so benefits may be minimal.

You can try it though. Options:

  • 2nd non-clustered index, keep original
  • move clustered index to another column(s)

Are there other good cluster candidates? Note, you always need a unique clustered index (because of uniquifiers + here SO + here). And of course, what is your PK please?

Improve this answer

1 Comment

I have a combo PK. The two columns that make up the PK are the two columns in the Clustered index that is being used.
0

It depends on how many columns you are talking about, if a couple then yes, a non clustered index will perform better, if you are selecting most of the columns the clustered index is better.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.