Script to Merger duplicate indexes

Question

Script to Merger duplicate indexes

EM2

SSC Veteran

Points: 207
More actions
May 13, 2024 at 1:02 pm

#4420850

Hello,
I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Simply DROP the index you no longer wish to keep.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 2

Okay you need to explain that a bit more clearly -- an Index is an object applied to a table.

So yes you can sort of merge two indexes into one using a script but why would you be doing that?

Do you have like a lot of these duplicated scripts through out your database?

Is there a particular reason you must use a script?

Might you be actually talking about something else and simply calling it an index incorrectly?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

EM2 wrote:

Hello,
I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.

You don't. You have to drop both indexes and create a new one.

BTW... be real careful... if the index is a unique index, it may be the target of an FK. Also, not all supposed "dupe" or "mergable" indexes actually are. As with all else, "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EM2 SSC Veteran Points: 207 More actions · Answer 4

So we have some indexes that are very similar; some of the tables seem a bit over indexed. The real question here is, how can we merge the indexes that are similar (create one index out the two similar indexes). I can try to do this manually but with so many tables, the process will take a long time. Having a script that peform this task will minimize the time required... I hope this gives clarity. Thanks so much.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

You don't actually have to drop both indexes. You would, for example, often drop one index and modify the other one.

I've started on code to do this process but have never finished it. I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

EM2 SSC Veteran Points: 207 More actions · Answer 6

Thank you, Scott. This stored procedure generates code for all indexes or similar/duplicate indexes?

ScottPletcher SSC Guru Points: 101095 More actions · Answer 7

For all indexes, or the 1 you tell it to generate (either by index number OR by index name).

As I said, I've been designing code to do my index checking / consolidation work for me, and generate the needed index statement(s), but I haven't completed the design nor the coding yet.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 8

ScottPletcher you ought to share that here and maybe one of us (or more than one of us) can help you complete that code. That might also help this individual and many others streamline this issue going forward.

As for too many indexes, yeah we ran into that as well on few tables but we did not have an over abundance of scripts that needed to be fixed and as such we simply dropped one and adjusted the other (if needed).

That being said I think they recommend like about 7 or fewer indexes upon a single table in general for efficiency purposes but this kind of also depends on how frequently that table gets added to or updated as that is where a lot of indexes tends to slow things down.

EM2 SSC Veteran Points: 207 More actions · Answer 9

EM2

SSC Veteran

Points: 207

May 13, 2024 at 3:04 pm

#4420887

@dennis Jensen, thank you!

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

ScottPletcher wrote:

You don't actually have to drop both indexes. You would, for example, often drop one index and modify the other one.
I've started on code to do this process but have never finished it. I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.

Seems contrary to the purpose of combining indexes. You still end up with 2 indexes instead of 1.

EDIT, because it came up, there is no way to "modify" an index and that appears to be the point of confusion. There is a way to CREATE a new index with the "DROP EXISTING" option enabled.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 11

Jeff Moden based on what ScottPletcher stated I do not see how one would end up with 2 Indexes as he did state you Drop one of the Indexes and modify the other which to me means you had 2 Indexes you drop one leaving with 1 Index and then you modify that remaining Index to accomodate for whatever extras are needed. So perhaps you misread what was posted, if not please explain?

This reply was modified 6 months, 1 week ago by Dennis Jensen.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 12

As for scripts to "find duplicates", they have this new fangled thing called a "search engine". 😀

Try the following search and see what you come up with. I haven't looked any of them so I can't make a recommendation.

Me? I usually just look for unused indexes. I also have some proof in a presentation that (for example) having a Non-Clustered index being keyed exactly the same as the Clustered Index isn't necessarily the bad thing everyone makes it out to be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 13

Really Jeff Moden that would be interesting to read (or see) -- your proof in a presentation -- as I find that a rather interesting stance and would greatly like to see that (as I am sure others would be as well). I might be good at SQL but I can guarantee you there are always things that I can learn about it and would love to do just that.

I am still hoping to see and help with ScottPletcher script that he talked about.

That being said, I do not see in your response Jeff Moden an answer to the question that I had posed to you about the Index count, but I hope you will expound upon that at some point in time.

This reply was modified 6 months, 1 week ago by Dennis Jensen.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

Are there some people who think that 'duplicate' means 'same or similar'?

If it's a duplicate, it is exactly the same and can be dropped. Otherwise, it is not a duplicate and I cannot imagine the complexity of a script which somehow works out which similar indexes can (and should) be combined and then scripts out the necessary T-SQL to effect those changes.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin