How to Spot an Unused Index?

Who cares whether the Index is used or not? You company cares and the person who manages the database cares. Index is a database object. Therefore, it occupies the disk space. Disk space is getting cheaper each year but the cost of the maintenance of those disk is getting costly each year. There was an article not too long ago on the cost of maintenance over the years for 1 TB was around 1 million dollar. Well, this may be hard for anyone to believe but the author showed the actual math to prove the cost which was very convincing.

One way to save the company cost is to drop unwanted Tables, data, and Indexes and re-claim the disk space. This also makes the database lighter, more manageable and performs faster. Finding unwanted database objects is challenging but it is totally doable when you partner with the developer or the owner of the data. I recently reclaimed over 15 TB of space from the data-warehouse which is like saving over $10 millions as per the paper. This article focus only on finding an unused Index. Index can be as big as the table size or greater. When you notice the size greater than the table, that is a red flag which needs immediate attentions.

Why are there unused Indexes in your database? There could be several reasons as to why the Index once used are no longer in use today or never been used once.

1. The data on the Index column changed greatly.
2. Incorrect query design.
3. The application retired.
4. Incorrect design of database.
5. Change in Stats.
6. Change in query execution plan.

It could be any of those listed above. Some understanding of why it is not  being used is helpful when searching and dropping the unused Index. Let's see, how we can spot the unused Index on Oracle and SQL Server database.

SQL SERVER:

dm_db_index_usage_stats is a system view on SQL that stores the Index usage stats.  Total Indexes  - Total used Index gives us the list of Index not used. This exactly what that below query is doing for you. The dm_db_index_usage_stats table data refreshed whenever the database is restarted therefore, to get the most accurate result, the database should be running for a longer period of time.

SQL Syntax:

SELECT Object_schema_name(I.object_id) AS SchemaName, 
       Object_name(I.object_id)        AS ObjectName, 
       I.NAME                          AS IndexName 
FROM   sys.indexes I 
WHERE  -- only get indexes for user created tables 
  Objectproperty(I.object_id, 'IsUserTable') = 1 
  -- find all indexes that exists but are NOT used 
  AND NOT EXISTS (SELECT index_id 
                  FROM   sys.dm_db_index_usage_stats 
                  WHERE  object_id = I.object_id 
                         AND I.index_id = index_id 
                         -- limit our query only for the current db 
                         AND database_id = Db_id() 
                         AND ( user_seeks > 0 
                                OR user_scans > 0 )
   and i.is_primary_key = 0
   and i.is_unique = 0) 
ORDER  BY schemaname, 
          objectname, 
          indexname 

ORACLE:

Oracle doesn't have silver bullet like that of SQL Server dm_db_index_usage_stats view that stores index usage stats. Oracle has multiple steps process to find out the index usage.

Step 1: Enable Monitoring Index usage.

SQL Syntax:

ALTER INDEX index_name MONITORING USAGE;

Step 2: Wait for few days and run the query below to check the usages status. This query will tell the index usage.

SQL Syntax:

select index_name, table_name, monitoring, used from v$object_usage where used ='NO';

Step 3: Drop the unused Index. If the Index is being used, there is no need to drop the index. The monitoring should be disable.

SQL Syntax:

alter index index_name NOMONITORING USAGE;

The above steps is best when the database has hand full of Indexes but impossible to work when there are thousands of Tables and Indexes. To enable motioning Index usage on multiple indexes at once, you will need to use Brute Force Techniques.

SQL Syntax:

SPOOL monitoring_index_enable.sql
SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@monitoring_index_enable.sql

Note: Never drop Index that were created with Constraints. This applies to any DBMS not just Oracle & SQL Server.

Any company loves to hear the re-claim of space after dropping unwanted objects. Your boss will be proud and your name will be announced in the next town hall meeting. This is something every DBA should utilize their downtime. Find those dead Index and drop them after taking their DDL backup.