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 |
|
sql-lover
Yak Posting Veteran
99 Posts |
 Posted - 2014-06-16 : 15:12:40
|
I am doing proactive work on my databases and found lot of dup and overlapping Indexes. Dups are easy to drop, as they are basically identical (same column) but different names. But overlapping can be more tricky.With three Indexes like this:CI/PK: Col1NCI: Col1, Col1, Col3 including (col4, col5, col6)NCI: Col1 including (col7, col8, col9) Which one should be dropped? 2nd or 3rd NCI?Or ... would be better to drop all of them, make a meaningless surrogate key on Colx, and keeping 2nd Index only?The included part of the two NCIs are different. So technically speaking, they are not the same. Am I right about that? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-16 : 15:16:29
|
| I would add col7-9 to the first one and then drop the second one, BUT!BUT you have to performance test any queries that were previously using the second one to see if there is any degradation. The index is now wider, so it can negatively impact those queries. This performance impact might be negligle. If it is, then you can drop the second one.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2014-06-16 : 15:21:28
|
quote:
Originally posted by tkizer
I would add col7-9 to the first one and then drop the second one, BUT!BUT you have to performance test any queries that were previously using the second one to see if there is any degradation. The index is now wider, so it can negatively impact those queries. This performance impact might be negligle. If it is, then you can drop the second one.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Interesting approach. In other words, are you suggesting making the PK bigger?I don't remember very well. I think that the combo was not unique and that's why we ended with the single column. But "my databases are very sick of duplicates". It was the old fashion way to do Indexes here at work and I am now fixing it.Going back to my initial question. If you have two Indexes like this:NCI: col1 including (col2, col3, col4)NCI: col1 including (col5, col6, col7) I know we have queries like this:SELECT col2, col3, col4FROM TableWHERE col1 = something andSELECT col5, col6, col7FROM TableWHERE col1 = something What would be the recommended solution? col1 is definitely a dup. But the included portion on each one is different. How they can be consolidated? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-16 : 15:24:41
|
| I am not suggesting touching the PK at all. I was referring to the two non-clustered indexes only.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2014-06-16 : 15:27:10
|
quote:
Originally posted by tkizer
I am not suggesting touching the PK at all. I was referring to the two non-clustered indexes only.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Something like this?NCI: Col1, Col1, Col3 including (col4, col5, col6, col7, col8, col9) Damn! lol ... that's a huge one! The columns order in the included part does not matter, does it? I mean, is that Index equal to:NCI: Col1, Col1, Col3 including (col5, col4, col6, col7, col9, col8) Is it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-16 : 15:33:00
|
| Why do you say it's huge? I've got indexes with many more include columns. What matters is the size. Size does matter when it comes to indexes, but especially the size of the clustered index.The ordering of the include columns does not matter, so yes those two are the same.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2014-06-16 : 15:44:28
|
quote:
Originally posted by tkizer
Why do you say it's huge? I've got indexes with many more include columns. What matters is the size. Size does matter when it comes to indexes, but especially the size of the clustered index.The ordering of the include columns does not matter, so yes those two are the same.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Sorry, English is my second language. What I meant was size.But what you are suggesting may be the best approach. I still need to revise what uses those columns before dropping any Index. But thanks again for the tip. |
|
|
|
|
|
|
|
|
Deleting Overlapping Indexes