Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single Column Indexes Expand / Collapse
Author
Message
Posted Monday, June 29, 2009 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 8:11 AM
Points: 2, Visits: 3,214
In the time I have been working at my current company as part of a DBA team, I have noticed that the standard seems to be to have many single column indexes on a table. Most of the larger tables have around 10 non-clustered indexes, each on a single column from the table. All the tables have a primary key (and clustered index) on the Identity column for the table.

The problem with this index setup that I see is very few queries in all the stored procedure code ever use just a single column in the WHERE clause. I have looked at a few query plans, it does appear that SQL Server uses just one of the single column indexes as a seek, then does a bookmark lookup.

To my knowledge, it would be better to find the top 3-5 queries using the table and create covering indexes for those queries. So most likely, each index would have 4-5 columns in it. From what I have seen in query plans, even if I reference only 1 or 2 columns from a 5 column index, it can still use that index.

So, I would welcome comments and opinions about this. Am I right in thinking that 5 indexes with 4 columns covering popular queries is better than 10 indexes each on a single column?

Thanks,

Nathan
Post #743550
Posted Monday, June 29, 2009 6:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 42,844, Visits: 35,972
websites2 (6/29/2009)
Am I right in thinking that 5 indexes with 4 columns covering popular queries is better than 10 indexes each on a single column?


In general, yes. The majority of the time, SQL will only use one index per table for a query, if there are three conditions in the where and 3 single column indexes, it's likely going to use the most selective, then look up to the clustered index, then filter on the other two conditions.

I wrote a blog post about index columns. It doesn't cover exactly this question, but it might help.
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #743555
Posted Monday, June 29, 2009 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 8:11 AM
Points: 2, Visits: 3,214
Hi Gail,

The article was very helpful. I wasn't aware that the order of the columns in the index mattered, but now I understand why.

So, my conclusion from that is, the advantage of having 10 indexes on a single column is that a larger number of queries will find an index it can use. The disadvantage being it will have to do checks against the index seek results for each other column filter in the query.

The advantage of 5 indexes with 4 columns each(the first column being different for each index) is that some queries (using all or some of the columns in index order) can find all the results in a single seek. The disadvantage is that now, for a query to use one of the 5 indexes, it must be referencing one of the 5 first columns, otherwise it cannot use any of the indexes.

Am accurate in saying that?

Also, there is another aspect of this issue we haven't talked about yet: Is there any difference in the maintenance of the two index structures? Will reindexing 10 indexes with single columns take longer than fewer indexes with more columns? Which index structure will slow down inserts more?

Any insight to this side of the issue would be appreciated too.

Thanks,

Nathan
Post #743760
Posted Monday, June 29, 2009 10:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 42,844, Visits: 35,972
websites2 (6/29/2009)
So, my conclusion from that is, the advantage of having 10 indexes on a single column is that a larger number of queries will find an index it can use. The disadvantage being it will have to do checks against the index seek results for each other column filter in the query.

The advantage of 5 indexes with 4 columns each(the first column being different for each index) is that some queries (using all or some of the columns in index order) can find all the results in a single seek. The disadvantage is that now, for a query to use one of the 5 indexes, it must be referencing one of the 5 first columns, otherwise it cannot use any of the indexes.

Am accurate in saying that?


Yes and yes

Also, there is another aspect of this issue we haven't talked about yet: Is there any difference in the maintenance of the two index structures? Will reindexing 10 indexes with single columns take longer than fewer indexes with more columns? Which index structure will slow down inserts more?


Of the top of my head, not sure, but the more places a column appears, the more places if has to be changed and the slower the change can be. That said, test it. I've never noticed a major performance degradation from 5 or 6 indexes. Once that gets to 15 or so, I start worrying.
Your system may be different, so set up some tests and see what the effect is.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #743772
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse