-- Indexes read and write operation specific table to find unused indexes based on read column
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] ,
i.is_Disabled ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor] ,
i.is_primary_key ,
i.is_unique ,
is_unique_constraint
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
and i.is_primary_key = 0
and user_seeks + user_scans + user_lookups = 0
--and OBJECT_NAME(s.[object_id]) in ('dbo.emp_table')
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;
i.name AS [IndexName] ,
i.is_Disabled ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor] ,
i.is_primary_key ,
i.is_unique ,
is_unique_constraint
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
and i.is_primary_key = 0
and user_seeks + user_scans + user_lookups = 0
--and OBJECT_NAME(s.[object_id]) in ('dbo.emp_table')
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;
No comments:
Post a Comment