Sql-server – Identifying Unused Indexes on SQL Azure

azure-sql-databasedatabase-sizeindexindex-maintenancesql server

I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information.

See: https://gist.github.com/eoincampbell/3fe775d43e86ad342f9c6eba10f350f9

  • Index Stats gathered from sys.dm_db_index_physical_stats joined to sys.tables, sys.schemas and sys.indexes
  • Index Usage gathered from sys.dm_db_index_usage_stats

I have a small concern around the accuracy of sys.dm_db_index_usage_stats. It's not clear from the documentation when/if the following occurs in a SQL Azure environment (as compared to a single instance MSSQLServer.)

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=azuresqldb-current The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

Here's the query I'm using to subsequently identify the unused indexes. It

  1. Gets the latest index information for all indexes in the database (812 records)
  2. Gets the latest usage information for all indexes (558 records)
  3. LEFT OUTER JOINs them together
  4. Excludes any Clustered/PK Indexes
  5. Returns anything with no usage stats OR anything with zero user-read stats.

The total returned row count is ~219 rows

Does this approach seem valid?

Query


WITH MostRecentStats (
    SchemaName, TableName, IndexName, IndexType, AllocUnitType, Pages, MostRecentAt
)
AS (
    SELECT      SchemaName, TableName, IndexName
                , IndexTypeDescription, AllocUnitTypeDescription
                , Max(PageCount) , Max(RecordDate)
    FROM        DBStats.IndexStats
    GROUP BY    SchemaName, TableName, IndexName
                , IndexTypeDescription, AllocUnitTypeDescription
    -- ****** Returns 812 Indexes across all tables ******
)
, AllCombinedUsage (
    SchemaName, TableName, IndexName
    ,user_seeks, user_scans, user_lookups, user_updates 
    , system_seeks, system_scans, system_lookups, system_updates
)
AS (
    SELECT      SchemaName, TableName, IndexName
                , sum(user_seeks), sum(user_scans), sum(user_lookups), sum(user_updates)    
                , sum(system_seeks), sum(system_scans), sum(system_lookups), sum(system_updates)
    FROM        DBStats.IndexUsage
    GROUP BY    SchemaName, TableName, IndexName
    -- Only Returns 558 Index with Usage Statistics... 
)
SELECT      a.SchemaName, a.TableName, a.IndexName, a.Pages
            , b.*
FROM        MostRecentStats a
LEFT JOIN   AllCombinedUsage b
            ON a.SchemaName = b.SchemaName 
            AND a.TableName = b.TableName
            AND a.IndexName = b.IndexName
WHERE       a.IndexName NOT LIKE 'PK_%' --Filter out all Primary Keys
AND         a.IndexType <> 'CLUSTERED INDEX' --And Clusted Indexes
AND (
            b.IndexName IS NULL --Include everything that has no index usage data
            OR
            (b.user_seeks + b.user_scans + b.user_lookups) = 0
            --Include everything with 0 User Reads on the data
)
ORDER BY    a.Pages DESC

Best Answer

You may also need to exclude unique indexes

Related Question