You could see that the values for INDEX ID from 1…4 has incremented by 1 to 21 from the previous value of 20 and for INDEX ID 5 the value has remained unchanged. In this way you can identify which indexes are really helpful and which are very rarely used. If you are interested to know when each of the above indexes were last used then you can use the query below:
USE AdventureWorks
GO
DECLARE @TABLENAME sysname
SET @TABLENAME= ‘HumanResources.Employee’
SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
, OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
, I.NAME AS [INDEX NAME]
, I.INDEX_ID AS [INDEX ID]
, USER_SEEKS AS [NUMBER OF SEEKS]
, LAST_USER_SEEK AS [LAST USER SEEK]
, USER_SCANS AS [NUMBER OF SCANS]
, LAST_USER_SCAN AS [LAST USER SCAN]
, USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
, LAST_USER_LOOKUP AS [LAST USER LOOKUP]
, USER_UPDATES AS [NUMBER OF UPDATES]
, LAST_USER_UPDATE AS [LAST USER UPDATE]
FROM
SYS.DM_DB_INDEX_USAGE_STATS SS
INNER JOIN SYS.INDEXES I
ON I.OBJECT_ID = SS.OBJECT_ID
AND I.INDEX_ID = SS.INDEX_ID
WHERE DATABASE_ID = DB_ID()
AND OBJECTPROPERTY(SS.OBJECT_ID,’IsUserTable’) = 1
AND SS.OBJECT_ID = OBJECT_ID(@TABLENAME)
ORDER BY USER_SEEKS
, USER_SCANS
, USER_LOOKUPS
, USER_UPDATES ASC
GO
Before dropping an index it would be ideal to disable the index on the table and see is there is any performance hit when the index is disabled. Disabling a non clustered index prevents users from accessing the particular non clustered index defined on the underlying table. However, the index definition remains in metadata and index statistics are also kept on nonclustered indexes. You need to keep in mind that if you disable the clustered index by any chance then you will not be able to access the data in the underlying table until the index is dropped or rebuilt.
Disable IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE
GO
Enable IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD
GO
Finally after the analysis when it is discovered that the queries are not using the IX_Employee_ManagerID index then you can execute the below TSQL code to drop the index on the HumanResources.Employee table.
Drop IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
DROP INDEX IX_Employee_ManagerID ON HumanResources.Employee
GO
Conclusion
Using the sys.dm_db_index_usage_stats Dynamic Management View, you can easily identify indexes which are used often by queries and also ndexes which are rarely used. Once you have identified the indexes, the best approach is to disable the indexes for some time and see is there is any performance degradation once the indexes are disabled. If there is no performance degradation then you can go ahead and drop the index there by saving disk space and improving performace during Insert, Update and Delete operations.
]]>