Friday, September 16, 2011

Find Unused Indexes In SQL Server

The script below uses dmv, sys.dm_db_index_usage_stats along with a number of system tables to return indexes that are not used since the last instance restart.

Below script which I had prepared for Identifying unused Indexes Since Last Restart of the server.

SELECT
sc.name + '.' + object_name(i.object_id) as objectName,
i.name as indexName,
i.type_desc as indexType

FROM sys.indexes i
INNER JOIN sys.objects o on o.object_id = i.object_id
INNER JOIN sys.schemas sc on o.schema_id = sc.schema_id

WHERE
objectproperty(i.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, indexName ASC

How to find out unused indexes. Removing Unused Indexes. Find unused indexes. Unused Index Script. free download. Script to find Unused Indexes in sql server 2008. Script to find Unused Indexes in sql server 2012. Deeper insight into unused indexes for SQL Server. SQL Queries That Identify Unused Indexes. how to find unused indexes in sql server. in sql server 2005. Index Tuning for SQL Server. how to find unused indexes in sql server 2008. how to find unused indexes in sql server 2005. unused indexes in sql server 2008. SQL Server DBA Scripts. Finding Missing Indexes and Unused Indexes using DMVs. Find Unused Indexes of Current Database. Discovering Unused Indexes tutorial and example. MOST COSTLY UNUSED INDEXES.

0 comments:

Post a Comment