SDU Tools: List Disabled Indexes in SQL Server Databases

I carry out a lot of basic health checks on SQL Server databases. One issue that I come across fairly regularly is that the databases contain indexes that have been disabled.

Most of the time, when I investigate why that’s happened, the customer has used some sort of ETL process and has disabled the indexes to improve loading speed. But then something went wrong, and the indexes didn’t get rebuilt (i.e. re-enabled).

To make it easy for you to find any disabled indexes in a database, in our free SDU Tools for developers and DBAs, we added a stored procedure ListDisabledIndexes to help. It takes three parameters:

@DatabaseName - the database to check @SchemaName - either ALL (default) or a comma-delimited list of schemas that should be checked @TableName - either ALL (default) or a comma-delimited list of tables that should be checked

You can see the output columns in this image:

As well as identifying the indexes, it shows the key and included columns.

You can see the procedure in action in the image above, and in the video here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

2019-05-15