Analyzing Index Usage and Deciding to Remove Index

In my previous post Rebuilding Indexes for better Performance I talked about how to rebuild indexes for better performance. Here, we will see how to identify indexes which are not used.

Databases have two primary storage needs; data pages and index pages.  Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored.  On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used.  So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?

In SQL Server, many new dynamic management objects have been created that allow you insight into a lot of data that was not accessible or just difficult to get in previous versions of SQL Server.  One new function and one new view that provide data about index usage are sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats.

sys.dm_db_index_operational_stats

This function gives you information about insert, update and delete operations that occur on a particular index.  In addition, this view also offers data about locking, latching and access methods.  There are several columns that are returned from this view, but these are some of the more interesting columns:

• leaf_insert_count - total count of leaf level inserts
• leaf_delete_count - total count of leaf level deletes
• leaf_update_count  - total count of leaf level updates

Here is a sample query that provides some of the key columns that are helpful to determine insert, update and delete operations.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Here is the output from the above query.  From this function we can get an idea of how many inserts, updates and delete operations were performed on each table and index. From this view we can get an idea of how many inserts, updates and delete operations were performed on each table and index.



sys.dm_db_index_usage_stats

This view gives you information about overall access methods to your indexes.  There are several columns that are returned from this DMV, but here are some helpful columns about index usage:

• user_seeks - number of index seeks
• user_scans- number of index scans
• user_lookups - number of index lookups
• user_updates - number of insert, update or delete operations

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Below is the output from above query. From this view we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred.
From this view we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred.



Now there is one catch here, the values for these counters get reset each time you restart SQL Server.  In addition, the values for the data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available.

Stay connected for more :) Happy Learning.