Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1875
Members Online:
3
Guests Online:
98

Total Post History
Posts:
81447
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
konam534As

Other

FAQ
All times are UTC [ DST ]

Google Ads

SQLCop check for duplicate indexes.

Please wait...

SQLCop check for duplicate indexes.

Postby gmmastros on Wed Apr 11, 2012 10:24 pm

I'm thinking of adding a SQLCop check for duplicate indexes and would like some help with it. I can write the query, that's not a problem. The part I need help with is determining how to define duplicates.

For example, if you have a clustered index on col1 and col2 and a non-clustered index on col1 and col2, would you consider that a duplicate?

If you had a non-clustered index on col1, col2 and another non-clustered index on col1, col2, col3 -- would you consider that a duplicate?

If you had a non-clustered index on col1, col2, col3 and another index on col1, col2 include(col3), would you consider that a duplicate?

Whatever query I write, I want it to make sense and I want to be able to defend it. I wouldn't mind writing 2 checks for SQLCop, one for exact duplicates and another for possible duplicates.

Thoughts???
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm

Re: SQLCop check for duplicate indexes.

Postby Remou on Wed Apr 11, 2012 11:18 pm

As a person who would be using SQLCop as a learning tool as much as anything else, I would like to be told that I had possible duplicates so I could think about whether I needed both indexes or not.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: SQLCop check for duplicate indexes.

Postby SQLDenis on Thu Apr 12, 2012 1:20 am

For example, if you have a clustered index on col1 and col2 and a non-clustered index on col1 and col2, would you consider that a duplicate?


I would say so unless your table is very wide and you have specific reasons to have the NC index on the same columns (maybe used in a bunch of group by queries)

If you had a non-clustered index on col1, col2 and another non-clustered index on col1, col2, col3 -- would you consider that a duplicate?

Pretty much since the only difference is the extra column, with the 3 column index you will satisfy all the queries from the 2 column index as well


If you had a non-clustered index on col1, col2, col3 and another index on col1, col2 include(col3), would you consider that a duplicate?

Yes, this is the same IMHO
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQLCop check for duplicate indexes.

Postby tracymckibben on Thu Apr 12, 2012 1:46 am

For example, if you have a clustered index on col1 and col2 and a non-clustered index on col1 and col2, would you consider that a duplicate?

If you had a non-clustered index on col1, col2 and another non-clustered index on col1, col2, col3 -- would you consider that a duplicate?

If you had a non-clustered index on col1, col2, col3 and another index on col1, col2 include(col3), would you consider that a duplicate?


In my environment, if I were doing index analysis, I would consider all of these to be dupes.
tracymckibben
Newbie
Newbie
 
Posts: 1
Joined: Thu Apr 12, 2012 1:44 am
Unrated

Re: SQLCop check for duplicate indexes.

Postby SQLSoldier on Thu Apr 12, 2012 2:34 am

I would count all of those as duplicates as well. Definitely, I think it should flag those so they can be checked.
SQLSoldier
Newbie
Newbie
 
Posts: 2
Joined: Thu Apr 12, 2012 2:33 am
Unrated

Re: SQLCop check for duplicate indexes.

Postby tarwn on Thu Apr 12, 2012 2:50 am

The order of the keys is significant, though I'm not sure in the first case that you gain much over just having the clustered index and a single nonclustered on col2 (size-wise it's about the same if the NC is not unique).

Case 1: If they're in the same order it's a dupe in my book

Case 2: Not a dupe, the additional column might make the NC index more selective for some queries and the size should be close (+ the third column) so not much lost and actually kind of a neat gain if you only need that 3rd column sometimes but don't want it added to the size of any other non-unique NC indexes on the table by making it part of the clustered index

Case 3: Not really a dupe since they have different levels of usefulness, but the 2nd case is wasteful and probably doesn't gain anything


It seems to me that the classification for each case is necessary both from the learning standpoint and from helping people understand what may or may not be relevant in their environment unless you start with the most specific set you can think of and then add cases from there. In my mind the only absolute dupe would be a case where they listed exactly the same key columns in exactly the same order and their include + clustered columns matched, anything else could have a purpose.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: SQLCop check for duplicate indexes.

Postby SQLDenis on Thu Apr 12, 2012 2:55 am

When George asked he did mention that the ordering had to be the same to be considered a duplicate example 3 is a duplicate. In my book
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQLCop check for duplicate indexes.

Postby SQLDenis on Thu Apr 12, 2012 2:57 am

Of course we didn't even mention ordering
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQLCop check for duplicate indexes.

Postby grrlgeek on Thu Apr 12, 2012 2:11 pm

Example 1: Yes, dupe.
Example 2: No.
Example 3: Yes.

I agree that we need to consider the order of the columns in the index.
Why why why why why why WHY?
User avatar
grrlgeek
Senior Apprentice
Senior Apprentice
LTD Bronze - Rating: 49
 
Posts: 138
Joined: Fri Mar 26, 2010 2:34 am
Unrated

Re: SQLCop check for duplicate indexes.

Postby SQLDenis on Thu Apr 12, 2012 2:20 pm

when I mean order of columns I mean this

(lastName ASC, FirstNAme ASC)
vs
(lastName DESC, FirstNAme DESC)


(lastName ASC, FirstNAme ASC)
vs
(FirstNAme ASC, lastName ASC)


all these examples have the same column, in one the column order is different, in the other the column order is the same but the sorting is different
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQLCop check for duplicate indexes.

Postby ScaryDBA on Thu Apr 12, 2012 2:22 pm

2 is absolutely a duplicate.

1 & 3, arguments can be made that they're not duplicates. For example, 1, a non-clustered index with the same key as a clustered index will, depending on the other columns in the table, be smaller. The optimizer might choose to use the smaller index for some queries that don't need the other data. So, from a tuning stand-point, this could be a valid design. But, most of the time, it's probably just a duplicate.

I'd say that you should write it up, as you're writing it up, but describe it as identifying "potential" duplicates so that you're not misconstruing what you're displaying.
ScaryDBA
Newbie
Newbie
 
Posts: 1
Joined: Thu Apr 12, 2012 2:18 pm
Unrated

Re: SQLCop check for duplicate indexes.

Postby gmmastros on Thu Apr 12, 2012 2:33 pm

I think I will make several variations of this query.

1. Exact duplicates (only looks for non-clustered indexes with the same keys in the same order).

2. Partial exact duplicates. This one would be for non-clustered indexes where one has the same columns as another plus extra columns.

3. Partial duplicates where the clustered index key matches the non-clustered index keys (with the same order).

This will allow me to write separate blogs for each, explaining why partial duplicates may be OK, and also mention that not each partial should be deleted.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm