How to get index usage information in SQL Server

How to get index usage information in SQL Server

Problem

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)?

Solution

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.

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

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

Notes

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.

Indexes That Do Appear in the Usage Statistics Table, But Are Never Used

DECLARE @MinimumPageCount int

SET @MinimumPageCount = 500

 

SELECT  Databases.name AS [Database],

        Indexes.name AS [Index],

        Objects.Name AS [Table],                   

        PhysicalStats.page_count as [Page Count],

        CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],

        CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)],

        ParititionStats.row_count AS [Row Count],

        CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024) / ParititionStats.row_count) AS [Index Size/Row (Bytes)]

FROM sys.dm_db_index_usage_stats UsageStats

    INNER JOIN sys.indexes Indexes

        ON Indexes.index_id = UsageStats.index_id

            AND Indexes.object_id = UsageStats.object_id

    INNER JOIN sys.objects Objects

        ON Objects.object_id = UsageStats.object_id

    INNER JOIN SYS.databases Databases

        ON Databases.database_id = UsageStats.database_id      

    INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS PhysicalStats

        ON PhysicalStats.index_id = UsageStats.Index_id

            and PhysicalStats.object_id = UsageStats.object_id

    INNER JOIN SYS.dm_db_partition_stats ParititionStats

        ON ParititionStats.index_id = UsageStats.index_id

            and ParititionStats.object_id = UsageStats.object_id       

WHERE UsageStats.user_scans = 0

    AND UsageStats.user_seeks = 0

    AND UsageStats.user_lookups = 0

    AND PhysicalStats.page_count > @MinimumPageCount    -- ignore indexes with less than 500 pages of memory

    AND Indexes.type_desc != 'CLUSTERED'                -- Exclude primary keys, which should not be removed   

ORDER BY [Page Count] DESC