October 7, 2008 at 2:44 pm
hi guys i came across this article of how to identify partial duplicate indexes in my serverhttp://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx.
My question is are these partial duplicate indexes always considered bad? for all of the ones i get from that query I should delete one of the duplicates? or is there cases where i should keep duplicate indexes? .
October 7, 2008 at 3:13 pm
What the article describes as exact duplicates are definately wastefull, as they will slow your inserts and updates without being able to give you any benefit whatsoever.
The overlapping indexes will require some analysis on your part. If it's a full-partial match, e.g. index1 = (col1, col2), index2 = (col1), then index2 would most likely be wastefull, since a query's WHERE clause condition on col1 can use the leading col1 of index1 without any problems. If the difference is in INCLUDE columns, they are only stored in the leaf nodes of your index B-tree so those wouldn't hurt to add to the index you keep and will help that index be the covering index for a query that only SELECTs columns in the INDEX columns and INCLUDE columns. If the overlap is only partial-partial, e.g. index1 = (col1, col2), index3 = (col1, col3), then you could end up hurting performance by removing one of these indexes, depending on the selectivity of col1. Also consider the article's point about the width of the INDEX columns, because an index that has 12 columns in their example (not counting INCLUDE colums) would require significantly more non-leaf pages than an index with only 1 or 2 columns which would hurt performance.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply