Thursday, February 4, 2021

SQL Server Search for index usage

 SELECT 

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

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND i.name='IX_WIPIN' -- your index name here

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);

No comments:

Post a Comment