Simple but accurate following script will give you list of all the indexes in the database which are unused. If indexes are not used they should be dropped as Indexes reduces the performance for INSERT/UPDATE statement. Indexes are only useful when used with SELECT statement.
Script to find unused Indexes.
USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
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 = @dbid)
ORDERÂ BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
Note: This is only for SQL Server 2005, it will not run for previous version of SQL Server.
Reference : Pinal Dave (https://blog.sqlauthority.com)
24 Comments. Leave new
Hi,
I have found Unused Indexes for table-wise in particular database, after that have to delete all the indexes? or what.
If i have to delete all indexes means how can I delete all unused indexes at a time.
Common yaar please answer me anybody, i am getting the issue daily like server is solw, so that i have checked indexes but here i found unused indexes, after that what should i do i don’t know.
Hi Pinal,
How could I get Unused Indexes server wide?
Plz suggest me.