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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!