[dba-SQLServer] Large cover index slows things down

I had an interesting occurrence today.  I am processing an order on the
database from hell.  As a result of my research a few weeks ago I discovered
that cover indexes on a field which includes the PK goes a long way in
speeding things up.  So I learned to create "single field" indexes where I
index just a specific field (golf or walking for example) and adding in the
PKID.  In this case I need to process eight "hobby" fields, selecting
records where any of these fields are 'Y'.  So I created 8 indexes on these
8 fields.  

I then created a single query that pulled in all eight hobby fields in an OR
such that any 'Y' in any field pulls the PKID.  I then created a count of
the PKID to see how many I got (7,704,210).  I decided to go back in and
create cover indexes on a group of the fields.  These fields are consecutive
in the table and tend to be called out in orders together, IOW Golf or
running or skiing etc.  In order to cover the 18 fields I created two cover
indexes grouping the first 9 and then the second 9 (and the PKID in each
index).  The query actually slowed down significantly, going from 2 minutes
36 seconds when there was no larger cover indexes to 4 minutes 12 seconds
after building these larger cover indexes.

Understand that I was not using every single field from these 18 fields in
the where clause, in fact only about 8 of them, but the 8 fields just happen
to be split across the two larger cover indexes so that both would be used.
understand too that I still have the "single field" cover indexes for all of
the fields needed in my where clause.

So it appears that the presence of a cover that included several of the
fields in the where clause caused SQL Server to use the larger 8 field cover
indexes rather than the individual "single field" cover indexes, and in so
doing slowed down the query by about 40% or so.

Hmmmm.

John W. Colby
Colby Consulting
www.ColbyConsulting.com