Identify Unused Index - SQLServer

Identify Unused Index

One of the housekeeping tasks of a DBA is to look for ways to save disk space. Dropping or rebuilding indexes is one way to save a disk space. In my career, I have seen index space bigger than table itself. This blog does not talk about index maintenance, it talks about how to identify unused index and you know what to do with that!

Run the below queries and check for user_scans and user_seeks. If both are zero, it is safe to drop the index.

-- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE
-- Author John Pasquet
DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500

SELECT Databases.name AS [Database], 
object_name(Indexes.object_id) AS [Table],
Indexes.name AS [Index],
UsageStats.user_scans,
UsageStats.user_seeks,
PhysicalStats.page_count as [Page_Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Frag %],
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.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
-- ignore indexes with less than a certain number of pages of memory
AND PhysicalStats.page_count > @MinimumPageCount
-- Exclude primary keys, which should not be removed
AND Indexes.type_desc != 'CLUSTERED'
ORDER BY [Page_Count] DESC

Alternatively, you can run Index Usage Statistics report on each database as well. The Key is to check for User_Seeks and User_Scans. If they both have Zero value, the Index was never used and it is safe to drop it. Remember, these numbers will reset to zero when you re-start the database. Therefore It is important to run the above query few days after db restart. I hope you will clean your dead index.