Index statistics in a database

Below query use and DMV and shows you the general state of the indexes in a database. It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.



SET Transaction Isolation Level Read Uncommitted


SELECT CONVERT(VARCHAR(30),so.name) AS TableName,
       COALESCE(Unused.IndexCount, 0) AS IndexCount,
       COALESCE(Unused.UnusedIndexCount, 0) AS UnusedIndexCount,
       COALESCE(Missing.MissingCount, 0) AS MissingIndexCount,
       COALESCE(CONVERT(DECIMAL(6,1), (CONVERT(DECIMAL(10,2),Unused.UnusedIndexCount)/CONVERT(DECIMAL(10,2),Unused.IndexCount)) * 100), 0) AS UnusedPercent


FROM sys.objects so
LEFT JOIN
    (SELECT s.OBJECT_ID, COUNT(*) AS IndexCount, SUM(CASE WHEN s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 THEN 1 ELSE 0 END) AS UnusedIndexCount
            FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
            ON s.OBJECT_ID     = i.OBJECT_ID AND s.index_id = i.index_id
            WHERE s.database_id   = DB_ID() AND OBJECTPROPERTY(s.OBJECT_ID, 'IsMsShipped') = 0
            GROUP BY s.OBJECT_ID
        ) AS Unused
ON Unused.OBJECT_ID = so.OBJECT_ID
LEFT JOIN (SELECT  d.OBJECT_ID, COUNT(*) AS MissingCount
            FROM sys.dm_db_missing_index_groups  g JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
            JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
            WHERE d.database_id = DB_ID()
            GROUP BY d.OBJECT_ID
            ) AS Missing
ON Missing.OBJECT_ID = so.OBJECT_ID
WHERE so.type_desc = 'USER_TABLE' AND (Missing.MissingCount > 0 OR Unused.UnusedIndexCount > 0)
ORDER BY UnusedPercent DESC 


SET Transaction Isolation Level Read Committed