This command will help you find duplicate and overlapping indexes in one or more databases. It will also tell you how much space you can save by dropping the index.
The command shows additional information such as the the type of compression so you can make an informed decision. Only CLUSTERED and NONCLUSTERED indexes are supported at this time.
You can select the indexes you want to drop in the grid and by click OK the drop statement will be generated.
Output that you can view on grid:
- CompressionDesc (When 2008+)
- IsFiltered (When 2008+)
If you want to DROP the selected indexes right away you can use -Force parameter. Be carefull.
This way after selecting the indexes and click ok the DROP statement(s) will be executed.
To export SQL for the duplicate indexes in server “sql2005” chosen on grid-view and writes them to the file “C:\temp\sql2005-DuplicateIndexes.sql”
Find-SqlDuplicateIndex -SqlServer sql2005 -FileName C:\temp\sql2005-DuplicateIndexes.sql
To find exact duplicate indexes on all user databases present on sqlserver2014a, using SQL credentials to log in.
Find-SqlDuplicateIndex -SqlServer sqlserver2014a -SqlCredential $cred
To find exact duplicate indexes on both db1 and db2 databases
Find-SqlDuplicateIndex -SqlServer sqlserver2014a -Databases db1, db2
To find exact duplicate or overlapping indexes on all user databases
Find-SqlDuplicateIndex -SqlServer sqlserver2014a -IncludeOverlapping