SDU Tools: List Unused Indexes in a SQL Server Database
SQL Server databases are often littered with indexes that aren’t needed. In many cases, people won’t even remember why they were created in the first place. So I’m often checking out indexes that might be candidates for removal. One of our free SDU Tools for developers and DBAs does just that. It’s ListUnusedIndexes.
You can see how to execute it in the main image above. The procedure takes just one parameter:
@DatabaseName sysname - This is the database to process
One row is returned for each unused index, since the last time that the SQL Server instance was restarted.
The columns returned are SchemaName, TableName, IndexName, IsUnique.
You need to be careful about ever removing unique indexes, so this procedure tells you if they are unique or not. Note that you also shouldn’t be removing indexes that are from 3rd party applications. Doing so might break your vendor support agreements. But this procedure will find you indexes that might be considered for removal.
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
Learn about indexing
Want to learn more about indexes? Take our online on-demand course now: https://training.sqldownunder.com/p/sql-server-indexing-for-developers
2018-12-19