HOW TO: Identify Unused Database Indexes in RockSolid

RockSolid monitors index usage via the relevant views in SQL Server. Indexes which have never been used, or have not been used for a period of time can be identified via the RockSolid application.

Viewing Unused Indexes in the RockSolid Console

To view unused indexes in RockSolid, navigate to the relevant database. Select the Object tab and choose the Indexes sub tab. Click Search to display the current indexes then in the result set click on the Last Used column to sort by the date the index was last used. Unused and indexes not used for the longest periods of time will be displayed at the top of the sort list. To view more details of the unused index, click the edit button for the relevant index.

Raising Unused Indexes as Service Requests

You can automatically raise the presence of unused indexes within SQL Server databases as service requests. To do this perform the following steps:

Navigate to the relevant level in the database hierarchy and then choose the "Settings" tab to view the database policy.

To enable the raising of unused database indexes, set the Unused Index Recommendations option to enabled. In the "Unused After (days)" text box, enter the number of days after which an index will be considered unused.

Indexes will not be raised as unused indexes until no index usage has been observed until the number of days specified above has lapsed since the last index usage was observed. If no index usage is observed unused indexes will be raised, as soon as the index has been under RockSolid monitoring for at least the specified number of days.