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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
HI Pinal
Nice article.
Just a recommendation, most of your images come from .
In our organization “blog” is filtered, and thus I cannot see the screen shots.
I guess it is in most of the companies.
Can u do something about it, may be changing the folder name, or the virtual directory name.
Thanks.
This is what I saw elsewhere for similar idea
It shows indexes NOT USED
SELECT
OBJECTNAME = OBJECT_NAME(I.OBJECT_ID)
, INDEXNAME = I.NAME
, I.INDEX_ID
FROM
SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE
OBJECTPROPERTY(O.OBJECT_ID, ‘IsUserTable’) = 1
AND I.INDEX_ID NOT IN ( SELECT
S.INDEX_ID
FROM
SYS.DM_DB_INDEX_USAGE_STATS S
WHERE
S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = DB_ID() )
ORDER BY
OBJECTNAME
, I.INDEX_ID
, INDEXNAME ASC
GO
Sir
In clusterd and Nonclustered indexe
which one is faster…??
sir
is it possible to create temporary columns………..
Is there any other cases where we would use the hint (index = number) ?
FOr example I have Index 1 = Clustered (ID)
Index 2 = NoneClustered(Date)
Then I run the follwing a query and I forced it to use index=2, Would I reduce the amount of page scans???
Select ID from MYtable (index =2)
where date < = ’11/12/2008′ ????
Hi,
How to do the same in SQL 2000?
By the way, I’ve on more query.
Is it possible to list the Indexes at the time of executing an SQL Query? Means, any way to list the Indexes associated with an SQL Query when that particular SQL Query is executed? Hoping, I havent confused you much. :-)
Thanks.
hi,
HOW to Find If Index is Being Used in Database in MSSQL 2000
Thanks.
Cleto
Thanks excacly what I was looking for.
best regards
Hi,
It is very useful for me.
Thanks
The image does not work.
Hi Sir,
I am newbie in SQL Server.I was going through above mention query. I was able to achieve the least used indexes as
select OBJECT_NAME(u.object_id) TableName,i.name Indexname,u.user_seeks,u.user_scans,u.user_lookups,u.user_updates
from sys.dm_db_index_usage_stats u inner join sys.indexes i on u.object_id=i.object_id
I don’t get why did you included sys.Indexes_columns and sys.columns table in your above query
Awesome feature. The only issue is that I don’t think the distinct makes sense in your query.
when will idexes not work ? how it affects with large data?