Sql-server – Results in sys.dm_db_index_usage_stats

dmvindexindex-tuningnonclustered-indexsql server

I have this query below that I use to clean up indexes that are not being used anymore to reduce runtime and overhead during index maintenance and during inserts. On some tables that I've ran this against, it returns indexes with 0 scans, 0 seeks and 0 lookups and there are some indexes that just returns NULL on the scans, seeks, lookups column. My understanding to those NULL's is that, that specific index has still not been used that's why there is no data for that index in sys.dm_db_index_usage_stats. That being said, whats the difference between these and those indexes that show up in sys.dm_db_index_usage_stats but has 0 scans, seeks and lookups? I appreciate everyone's ideas

DECLARE @tblname VARCHAR(100) =  'Login' --<<<TABLE NAME HERE

SELECT CONCAT(OBJECT_SCHEMA_NAME(i.[object_id]), '.', OBJECT_NAME(i.[object_id])) AS [OBJECT NAME]
       ,CASE
           WHEN I.name IS NULL THEN
               'heap table'
           ELSE
               I.[name]
       END AS [INDEX NAME]
      , i.index_id
      , s.user_seeks +s.user_scans + s.user_lookups AS [Total Scan/seek/lookup]
      ,s.user_seeks
      , s.user_scans
      , s.user_lookups
FROM sys.indexes AS I
     LEFT JOIN  sys.dm_db_index_usage_stats AS S
        ON  I.index_id = S.index_id
        AND I.[object_id] = S.[object_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
      AND OBJECT_NAME(i.object_id) = @tblname
      --AND i.index_id <>1
      ORDER BY s.user_lookups DESC

Best Answer

If an index hasn't been touched at all since start-up, then it isn't in the result from sys.dm_db_index_usage_stats. Doing an outer join to that DMV means columns you pick from it will be NULL. As for those that are 0, they have been touched according to sys.dm_db_index_usage_stats, since it does return a row for that index.

You should be able to verify doing a SELECT * FROM sys.dm_db_index_usage_stats without involving sys.indexes and see that for "the NULLs" the index isn't retured and for "the 0's" the index is returned and potentially some system scans etc.

Related Question