SQL SERVER – 2008 – Find If Index is Being Used in Database

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.

Before dropping the index it is important to check if index is being used or not. I have wrote quick script which can find out quickly if index is used or not. SQL Server 2005 and later editions have Dynamic Management Views (DMV) which can queried to retrieve necessary information.

We will run SELECT on Employee table of AdventureWorks database and check it uses Indexes or not. All the information about Index usage is stored in DMV – sys.dm_db_index_usage_stats. Let us run following query first and save our results.

USE AdventureWorks
GO
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('AdventureWorks') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee');
GO

Now let us run following two SELECT statement which will utilize Indexes on table Employee.

USE AdventureWorks
GO
SELECT *
FROM HumanResources.Employee WITH (INDEX = 1)
WHERE EmployeeID = 1
GO
SELECT *
FROM HumanResources.Employee WITH (INDEX = 2)
WHERE LoginID = 'adventure-works\guy1'
GO

Note : WITH (INDEX  = Number) is not required but I have used it to make sure that first query uses Index 1 and second query uses Index 2. Both the query will return the same result. Now once again we will run our initial query  getting data from sys.dm_db_index_usage_stats and compare our result with initial data.

(Click on image to see larger image)

It is clear from comparing both the result set that when running query on tables it updates sys.dm_db_index_usage_stats and increment column user_seeks.

Above whole process explains that any index usage is stored in the sys.dm_db_index_usage_stats. DMV sys.dm_db_index_usage_stats stores all the usage since SQL Server is restarted. Once SQL Server service is restarted sys.dm_db_index_usage_stats is reset to zero but over the period of the time it updates the values in the columns. If we run our initial query without WHERE condition we can get many rows which contains IndexName and their usage. That will give us idea how many indexes are heavily used. If using WHERE condition we do not find our index in the table it is clear indication that Index is not used much.

If SQL Server services are not restarted in reasonable amount of time and if any index usage is not found, the index should be dropped. Again, make sure you have test your performance after dropping the index. If it gets worst put that index back and continue exercise.