June 29, 2009 at 6:13 am
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
June 29, 2009 at 6:23 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 10:07 am
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
June 29, 2009 at 10:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply