Possible Bad NC Indexes

unused index in SQL database

SELECT SCHEMA_NAME(o.[schema_id]) AS [Schema Name]
	,OBJECT_NAME(s.[object_id]) AS [Table Name]
	,i.name AS [Index Name]
	,i.index_id
	,i.is_disabled
	,i.is_hypothetical
	,i.has_filter
	,i.fill_factor
	,s.user_updates AS [Total Writes]
	,s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads]
	,s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id]
	AND i.index_id = s.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK) ON i.[object_id] = o.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
	AND s.database_id = DB_ID()
	AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
	AND i.index_id > 1
	AND i.[type_desc] = N'NONCLUSTERED'
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND i.is_unique = 0
ORDER BY [Difference] DESC
	,[Total Writes] DESC
	,[Total Reads] ASC
OPTION (RECOMPILE);
Spread the love