CODEWITHSUNDEEP
sqlsql-servert-sqlindexingsql-server-2012
gotqn
gotqn

Reputation: 43636

For which indexes there are no entry in the Index Usage Stats Table?

I am trying to find which indexes are not used in order to clear them and reduce SQL Server index maintenance.

So, I have managed to extract two queries based on the official documentation and other articles(like this one):

The first one is returning not used indexes based on the sys.dm_db_index_usage_stats:

DECLARE @MinimumPageCount INT = 0

SELECT object_name(IDX.[object_id]) AS [Table]    
      ,IDX.[name] AS [Index] 
      ,PHS.[page_count] AS [Page_Count]
      ,CONVERT(DECIMAL(18,2), PHS.page_count * 8 / 1024.0) AS [Total Size (MB)]
      ,CONVERT(DECIMAL(18,2), PHS.avg_fragmentation_in_percent) AS [Frag %]
      ,PRS.row_count AS [Row Count]
      ,CONVERT(DECIMAL(18,2), (PHS.page_count * 8.0 * 1024) / PRS.row_count) AS [Index Size/Row (Bytes)]
      ,US.[user_updates]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,NULL,NULL) AS PHS
    ON PHS.[index_id] = US.[index_id] 
    AND PHS.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]  
WHERE 
    -- showing only not used indexes 
        US.[user_scans] = 0
    AND US.[user_seeks] = 0
    AND US.[user_lookups] = 0
    AND US.[system_scans] = 0
    AND US.[system_seeks] = 0
    AND US.[system_lookups] = 0
    -- ignore indexes with less than a certain number of pages of memory
    AND PHS.page_count > @MinimumPageCount
    -- exclude clustered and heaps tables
    AND US.[index_id] > 1
    --current db only 
    AND US.[database_id]= DB_ID()
ORDER BY [Page_Count] DESC

The second one is extracting similar information for indexes for which there are no extries in the sys.dm_db_index_usage_stats like this:

DECLARE @dbid INT =  DB_ID()

SELECT DB.Name AS [Database]
      ,OBJ.NAME AS [Table]
      ,IDX.NAME AS [Index]
      ,IDX.INDEX_ID
      ,PHS.page_count AS [Page Count]
      ,CONVERT(DECIMAL(18,2), PHS.page_count * 8 / 1024.0) AS [Total Index Size (MB)]
      ,CONVERT(DECIMAL(18,2), PHS.avg_fragmentation_in_percent) AS [Fragmentation (%)]
FROM [sys].[indexes] IDX
INNER JOIN SYS.OBJECTS OBJ 
    ON IDX.[object_id] = OBJ.[object_id]
LEFT JOIN [sys].[dm_db_index_physical_stats] (@dbid, NULL, NULL, NULL, NULL) PHS
    ON PHS.[object_id] = IDX.[object_id] 
    AND PHS.[index_id] = IDX.[index_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = PHS.[database_id]
WHERE OBJ.[type] = 'U' -- Is User Table
    AND IDX.[is_primary_key] = 0
    AND IDX.[index_id] NOT IN 
    (
        SELECT US.INDEX_ID
        FROM [sys].[dm_db_index_usage_stats] US
        WHERE US.[object_id] = IDX.[object_id]
            AND IDX.[index_id] = US.[index_id]
            AND [database_id] = @dbid
    )
    AND IDX.[index_id] > 1
ORDER BY PHS.[page_count] DESC
        ,OBJ.[name]
        ,IDX.[index_id]
        ,IDX.[name] ASC

Looking the query above, the only filtering that is made is based on if there is a entry in the sys.dm_db_index_usage_stats for the particular index.

So, is there a rule that if there is no entry the index is not used?

Upvotes: 6

Views: 1337

Answers (1)

Pieter
Pieter

Reputation: 420

The first query you gave searches for unused indexes. The sys.dm_db_index_usage_stats function has fields [user_scans],[user_seeks], [user_lookups], [system_scans], [system_seeks], [system_lookups]. If they are all 0 then according to your own link this means the index was never used.

The second query gives the indexes where there are no usage statistics available. According to your link the records for the sys.dm_db_index_usage_stats function are set to zero on every restart and deleted when the database is detached or is shut down. So if the index was never used since a database detach or shut-down there will be no usage statistic row for it.

This link you have allready given but I give it again for completeness of my answer: http://technet.microsoft.com/en-us/library/ms188755.aspx

Upvotes: 1

Related Questions