search

hung-doan / .bookmarks

My life bookmarker
0 stars 0 forks source link

Find unused SQL indexes #54

Open hung-doan opened 5 years ago

hung-doan commented 5 years ago 
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

NOTE: the statistic is base on the statistic from SQL Server. These records/statistic will be cleannedup/reset if sql server restart