Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2011-09-06 : 19:19:23
|
| I have three indexes..._dta_index_Activity_9_29243159__K9_K2_K8_dta_index_Activity_9_29243159__K2_K9_K8_dta_index_Activity_9_29243159__K2_K8_K9They all reference the same columns but in a different order. I read that this isn't necessarily duplication since the order of the columns in the index can influence their use and effectiveness. In your opinion does the possible benefit out-weight the insert/update and database size cost? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2011-09-06 : 20:47:09
|
quote:
Originally posted by tkizer
If those indexes are being used, then you should keep them. Have you run the DMV reports to see if they are being used?And yes if the order of the columns is different, then these are not duplicates.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
They are being used. I guess the question is - will they use the other very similar index if I delete the one currently being used without much performance difference? The problem (and maybe it isn't a problem really) is that I currently have 13 indexes on my largest table (20 million records). That table with the indexes uses about 15 gigs of space. It is a heavily read and write table so there is a cost to having so many indexes. Just trying to find a balance... |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
Index Duplication?