Indexes - Find unused

To improve the efficiency of a database and the performance of a Sybase server, a stored procedure is provided here that will search for indexes in a database that are not used.

Software download link: http://www.petersap.nl/documents/UnusedIndexes.zip (4 Kb)

How does it work?

Sybase ASE keeps a number of object and index descriptors in memory. Each time an object is used and is not yet in the descriptor list, the object and all its indexes are added to the list. Whenever an index is used a usage counter for the index is incremented. Unused indexes can be tracked by looking for a usage count of zero. However, this only makes sense when your system has been running long enough and all the regular processing has been done so all the objects (and its indexes) have been loaded into the descriptor lists. The Sybase monitoring table monOpenObjectActivity gives access to these descriptor lists.

Build-in features

• A warning is given when the Sybase server is running for less than 7 days. This simple mechanism can be a trigger for a DBA to check if really all the regular processing has been performed.
• A warning is given when the database, object or index descriptors have been been reused. A reuse is an indication that the descriptor lists are to short and need to be increased. You can increase the list by tuning configurable parameters (number of open databases/objects/indexes). Allthough the size of these lists can be increased without a reboot of the Sybase ASE server, the reused indicator can only be cleared with a reboot.
• Common system databases are excluded.

Installation

The stored procedure can only be used for ASE version 12.5.0.3 or later, monitoring tables need to be installed and you need the appropriate permissions. The stored procedure will be loaded into the sybsystemprocs database. Since "grouped stored procedures" are used you need to have the configuration parameter "allow procedure grouping" at its default value. When that's not the case replace all ";2" occurences in the stored procedure with "_2".

Usage

Run the stored procedure with "exec sp_unused_indexes", no parameters are needed.

exec sp_unused_indexes

De-installation

Drop the stored procedure sp_unused_indexes from the sybsystemprocs database. Optionally drop sp_unused_indexes_2 too when you replaced ";2" with "_2".

drop proc sp_unused_indexes