Finding Unused Indexes on Database

Sometimes the number of indexes on your database may be huge in number and it would be a teadous task to check which are indexes being used and which are not. So here is one of the solution. I found this T-SQL Statement useful which may ease out your task.
Thanks to Search Engines.
USE [YOURDATABASENAME]
GO
SELECT O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME,
I.TYPE_DESC, U.USER_SEEKS, U.USER_SCANS,
U.USER_LOOKUPS, U.USER_UPDATES
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS U ON I.OBJECT_ID = U.OBJECT_ID
AND I.INDEX_ID = U.INDEX_ID
AND U.DATABASE_ID = DB_ID()
WHERE O.TYPE <> ‘S’ — NO SYSTEM TABLES!
ORDER BY (ISNULL(U.USER_SEEKS, 0) + ISNULL(U.USER_SCANS, 0) + ISNULL(U.USER_LOOKUPS, 0) + ISNULL(U.USER_UPDATES, 0)), O.NAME, I.NAME
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s