Identifying Unused Indexes on SQL Azure

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://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 ac

The problem with Azure SQL Database is that you don't have control over when the SQL Server is re-started or your database shifts over to a different instance - i.e., the index usage collection might be from a much shorter timespan than would be usable for you.

One option is to work with the auto-index management functionality built in to Azure SQL Database.

Another option would be for you to materialize the info from sys.dm_dm_index_usage_stats into your own table so that the info survives these things. I haven't looked for such a script, but there might exists something "out there" already.

One factor that is interesting is when the SQL Server was re-started:

SELECT sqlserver_start_time  
FROM sys.dm_os_sys_info;

I can't say for sure if this includes all cases where usage stats would be wiped, though.

Below are a couple of articles on the subject that adds some information:
https://www.c-sharpcorner.com/article/dropping-unused-indexes-on-an-azure-sql-database/
https://sqlperformance.com/2018/04/azure/automatic-index-management-in-azure-sql-database

StackExchange Database Administrators Q#280647, answer score: 3