To Include or Not to Include (covering indexes)

Question

To Include or Not to Include (covering indexes)

Apollo74x

Ten Centuries

Points: 1250
More actions
January 28, 2010 at 1:18 pm

#220135

I ran a query to determine missing indexes.
One of the fields showed a huge amount of user seeks on a column that already has an index on it. The only difference, is the missing index report suggested that I create a covering index.
I created the index and low and behold, by the end of the day, there were over 90,000 seeks.
However, I didn't drop the original non-clustered index and the number of seeks on this index still goes up. This means that most of the time, the optimizer chooses the covering index but it still chooses to use the non-covering index in other queries.
Now I have two indexes on the same column and I'm not sure if I should drop one. Furthermore, if I drop the non-covering index, will that lead to a clustered index scan if a query does not use any of the included columns or if a query contains more columns than what was included?
Please enlighten me.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Apollo74x (1/28/2010)
One of the fields showed a huge amount of user seeks on a column that already has an index on it. The only difference, is the missing index report suggested that I create a covering index.

Are the columns of the two in the same order?

Now I have two indexes on the same column and I'm not sure if I should drop one.

Providing the columns of the index key are indeed in the same order, drop the one that's not covering

Furthermore, if I drop the non-covering index, will that lead to a clustered index scan if a query does not use any of the included columns or if a query contains more columns than what was included?

No. There's never a problem if an index contains more columns than a query requires (providing the key order is correct for the query. )

Worth looking at: http://sqlinthewild.co.za/index.php/2008/09/23/do-wide-indexes-slow-queries-down/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Apollo74x Ten Centuries Points: 1250 More actions · Answer 2

GilaMonster (1/28/2010)
Apollo74x (1/28/2010)
One of the fields showed a huge amount of user seeks on a column that already has an index on it. The only difference, is the missing index report suggested that I create a covering index.
Are the columns of the two in the same order?
Now I have two indexes on the same column and I'm not sure if I should drop one.
Providing the columns of the index key are indeed in the same order, drop the one that's not covering
Furthermore, if I drop the non-covering index, will that lead to a clustered index scan if a query does not use any of the included columns or if a query contains more columns than what was included?
No. There's never a problem if an index contains more columns than a query requires (providing the key order is correct for the query. )
Worth looking at: http://sqlinthewild.co.za/index.php/2008/09/23/do-wide-indexes-slow-queries-down/

I'm slightly confused on your question of if the columns are in the same order. I probably confused you in my description.

Only one column was returned by the missing index report which is the same column that already has an index on it. However, the missing index report shows that I should include a few other fields.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Apollo74x (1/28/2010)
I'm slightly confused on your question of if the columns are in the same order. I probably confused you in my description.

I missed that the index was on a single column.

My concern was that an index on Col1, Col2, Col3 is not the same as one on Col3, Col2, Col1.

If it's a single column index vs a wider index (with the same column as the left-most) then the narrower one is redundant and can be dropped.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Apollo74x Ten Centuries Points: 1250 More actions · Answer 4

GilaMonster (1/28/2010)
Apollo74x (1/28/2010)
I'm slightly confused on your question of if the columns are in the same order. I probably confused you in my description.
I missed that the index was on a single column.
My concern was that an index on Col1, Col2, Col3 is not the same as one on Col3, Col2, Col1.
If it's a single column index vs a wider index (with the same column as the left-most) then the narrower one is redundant and can be dropped.

Thank you sir

Also - nice blog:

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Apollo74x (1/28/2010)

Also - nice blog:

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/%5B/quote%5D

Thanks. (It's my blog in case you didn't realise)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass