Free – Duplicate Indexes Report – Updated

Download PDF

After creating and deploying the duplicate indexes report earlier today I discovered that although the report did exactly what it says, it didn’t do what I needed.

The earlier  duplicate indexes report only found indexes that were an exact match on the columns.  And that is useful to track down, it didn’t point out indexes that were similar, but had additional columns.

Given the following sample code CustomersA, and CustomersB are duplicate indexes of Customers1 even thought the columns don’t match exactly.  The new report will catch and display this type of matches.

CREATE TABLE [Customers] (
id INT IDENTITY,
firstname VARCHAR (200),
middlename VARCHAR (200),
lastname VARCHAR (200),
age INT
);
CREATE CLUSTERED INDEX [CustomersId] ON [Customers] ([id] ASC);
CREATE INDEX [Customers1] ON [Customers] ([lastname], [firstname], [middlename]);
CREATE INDEX [Customers2] ON [Customers] ([lastname], [firstname], [middlename]);
CREATE INDEX [Customers3] ON [Customers] ([lastname], [firstname], [middlename]);
CREATE INDEX [CustomersA] ON [Customers] ([lastname]);
CREATE INDEX [CustomersB] ON [Customers] ([lastname], [firstname]);

As shown below the new report now shows the matching similar indexes.

This is part of my growing collection of free SSRS reports to analyze and assess your SQL Server Health.

Download and Enjoy.

 

 

Tagged with: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.