SQL SERVER – 2005 – Find Unused Indexes of Current Database

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)

,
Previous Post
SQLAuthority News – RIP: Ken Henderson, 1967 – 2008
Next Post
SQL SERVER – Get Current Database Name

Related Posts

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.

    Reply
    • 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.

      Reply
  • Hi Pinal,

    How could I get Unused Indexes server wide?

    Plz suggest me.

    Reply

Leave a Reply

Menu