Post reply

To Include or Not to Include (covering indexes)

  • 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.

  • January 28, 2010 at 1:44 pm

    #1110348

    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

  • January 28, 2010 at 1:59 pm

    #1110356

    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.

  • January 28, 2010 at 2:03 pm

    #1110358

    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

  • January 28, 2010 at 2:13 pm

    #1110361

    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/

  • January 28, 2010 at 10:19 pm

    #1110456

    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

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

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