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 tosys.tables
,sys.schemas
andsys.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://learn.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
- Gets the latest index information for all indexes in the database (812 records)
- Gets the latest usage information for all indexes (558 records)
- LEFT OUTER JOINs them together
- Excludes any Clustered/PK Indexes
- 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