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
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
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 (
section in the screenshot above). Mind that all hard coded references to indexes, if any, should be fixed in advance.
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.