May 13, 2024 at 1:02 pm
Hello,
I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.
May 13, 2024 at 1:06 pm
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
May 13, 2024 at 2:10 pm
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?
May 13, 2024 at 2:26 pm
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
Change is inevitable... Change for the better is not.
May 13, 2024 at 2:32 pm
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.
May 13, 2024 at 2:44 pm
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".
May 13, 2024 at 2:50 pm
Thank you, Scott. This stored procedure generates code for all indexes or similar/duplicate indexes?
May 13, 2024 at 2:54 pm
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".
May 13, 2024 at 3:02 pm
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.
May 13, 2024 at 4:28 pm
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
Change is inevitable... Change for the better is not.
May 13, 2024 at 4:33 pm
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?
May 13, 2024 at 4:35 pm
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
Change is inevitable... Change for the better is not.
May 13, 2024 at 4:41 pm
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.
May 13, 2024 at 4:45 pm
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
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply