SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Deleting Overlapping Indexes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql-lover
Yak Posting Veteran

89 Posts

Posted - 06/16/2014 :  15:12:40  Show Profile  Reply with Quote
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: Col1
NCI: 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

USA
37287 Posts

Posted - 06/16/2014 :  15:16:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

89 Posts

Posted - 06/16/2014 :  15:21:28  Show Profile  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://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, col4
FROM Table
WHERE col1 = something


and


SELECT col5, col6, col7
FROM Table
WHERE 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?

Edited by - sql-lover on 06/16/2014 15:24:44
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 06/16/2014 :  15:24:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
I am not suggesting touching the PK at all. I was referring to the two non-clustered indexes only.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

89 Posts

Posted - 06/16/2014 :  15:27:10  Show Profile  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://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?

Edited by - sql-lover on 06/16/2014 15:28:09
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 06/16/2014 :  15:33:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

89 Posts

Posted - 06/16/2014 :  15:44:28  Show Profile  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000