SQL Swiss Army Knife #15 - Handling duplicate indexes

Hello all,

Part of my job is to help customers find and fix performance issues, and this post comes after one such engagement. A customer was using another of the SQL Swiss Army Knife series scripts to find info on indexes on all the databases in a given instance – the one referenced in the Index information galore post.

After that, the customer in question came back to me saying "listen, I have more than 600 duplicate indexes, and your script is suggesting the ones that should be deleted, but you say to be careful of any hard coded references (index hints) that might exist – how can I find those? Do I have to go through all my code?"

Guess what was my answer… "It depends" – followed by a question – "do you have any statements running that are NOT based on SPs, UFNs or other code that sits in SQL Server?"

The answer in this case was one I liked – “No” – that means I could easily help out by checking all the sql modules for such references, and the output will resemble the following:

In this example, I do have several duplicate indexes, of which one has hard coded references in one object (highlight in red). We can also see that it’s one of the indexes that are eligible to be dropped.

In the messages tab, you will have the drop script for all the eligible duplicate indexes that are to be dropped (Duplicate_IX_toDrop section in the screenshot above). Mind that all hard coded references to indexes, if any, should be fixed in advance.

Hope this can also be helpful for your case.

Download script here: view_DupIndexes_HardCoded.sql

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.