Index Usage/Operational Stats, Duplicate/Unused Indexes, Write Amplification, and Portfolio Reviews

Learning outcomes

ServiceHub now has enough indexes that the biggest indexing risk is no longer “missing one index.” It is portfolio drift: overlapping structures, wide indexes added for one incident, indexes that incur writes but have weak evidence of reads, constraint-supporting indexes that look redundant, and counters interpreted without knowing when they reset. A safe review combines usage counters, lower-level operational counters, plans/Query Store, schema dependencies, and a long enough observation window to represent business cycles.

1. Start with an observation window, not a drop query

sys.dm_db_index_usage_stats counts seeks, scans, lookups, and update operations. Microsoft documents that its counters are initialized empty when the Database Engine starts; database detach/shutdown can also remove rows. In SQL Server 2022+ querying the server-level DMV requires VIEW SERVER PERFORMANCE STATE. A zero seek count after a maintenance restart says almost nothing about monthly, quarterly, or year-end workloads.

USE ServiceHubLab;GOSELECT SERVERPROPERTY('ProductVersion') AS engine_build,       SERVERPROPERTY('Edition') AS edition;SELECT sqlserver_start_timeFROM sys.dm_os_sys_info;GOSELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,       OBJECT_NAME(i.object_id) AS table_name,       i.index_id,i.name,i.is_unique,i.is_primary_key,i.is_unique_constraint,       COALESCE(u.user_seeks,0) AS user_seeks,       COALESCE(u.user_scans,0) AS user_scans,       COALESCE(u.user_lookups,0) AS user_lookups,       COALESCE(u.user_updates,0) AS user_updates,       u.last_user_seek,u.last_user_scan,u.last_user_lookup,u.last_user_updateFROM sys.indexes AS iLEFT JOIN sys.dm_db_index_usage_stats AS u  ON u.database_id=DB_ID() AND u.object_id=i.object_id AND u.index_id=i.index_idWHERE i.object_id>0 AND i.type IN (1,2)ORDER BY table_name,i.index_id;GO

Permissions are deliberate. If the learning login lacks VIEW SERVER PERFORMANCE STATE, use an authorized lab/admin connection or review database-scoped evidence available to the role. Do not grant broad production permissions merely to run a tuning script.

2. Operational stats answer different questions—and reset differently

sys.dm_db_index_operational_stats exposes leaf inserts/updates/deletes, scans, singleton lookups, page allocations/splits, latch waits, and lock waits. It is not persistent historical telemetry: its values live only while the heap/B+ tree's metadata cache object remains available and can reset after cache eviction or DDL. Microsoft explicitly says not to use these counters to conclusively decide whether an index was ever used.

DECLARE @db int=DB_ID();SELECT OBJECT_SCHEMA_NAME(os.object_id,@db) AS schema_name,       OBJECT_NAME(os.object_id,@db) AS table_name,       i.name,       SUM(os.leaf_insert_count+os.leaf_update_count+os.leaf_delete_count) AS leaf_writes,       SUM(os.range_scan_count) AS range_scans,       SUM(os.singleton_lookup_count) AS singleton_lookups,       SUM(os.leaf_allocation_count) AS leaf_allocations,       SUM(os.page_latch_wait_in_ms) AS page_latch_wait_msFROM sys.dm_db_index_operational_stats(@db,NULL,NULL,NULL) AS osJOIN sys.indexes AS i  ON i.object_id=os.object_id AND i.index_id=os.index_idGROUP BY os.object_id,i.nameORDER BY leaf_writes DESC;GO

Use these counters as a recent-behavior lens. For durable history, persist snapshots with timestamps or correlate Query Store/monitoring telemetry. Do not build an automatic “drop if seeks=0” job.

3. Detect overlap as a review queue, not an answer

Two indexes can share a leading key yet serve different sort order, included-column coverage, filters, uniqueness rules, partition alignment, or query populations. An overlap detector should therefore produce candidates for human/workload review. Start from metadata and compare ordered key lists, includes, filters, uniqueness, and constraint flags.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,       OBJECT_NAME(i.object_id) AS table_name,       i.index_id,i.name,i.type_desc,i.is_unique,       i.is_primary_key,i.is_unique_constraint,       i.has_filter,i.filter_definition,       STRING_AGG(CASE WHEN ic.is_included_column=0                       THEN CONCAT(c.name,CASE WHEN ic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END)                  END,',') WITHIN GROUP (ORDER BY ic.index_column_id) AS key_columns,       STRING_AGG(CASE WHEN ic.is_included_column=1 THEN c.name END,',')         WITHIN GROUP (ORDER BY ic.index_column_id) AS included_columnsFROM sys.indexes AS iJOIN sys.index_columns AS ic  ON ic.object_id=i.object_id AND ic.index_id=i.index_idJOIN sys.columns AS c  ON c.object_id=ic.object_id AND c.column_id=ic.column_idWHERE i.object_id>0 AND i.type=2GROUP BY i.object_id,i.index_id,i.name,i.type_desc,i.is_unique,         i.is_primary_key,i.is_unique_constraint,i.has_filter,i.filter_definitionORDER BY table_name,i.index_id;GO

STRING_AGG skips NULL expressions, which is useful here but worth remembering. If your target tool/database compatibility differs, use an alternative aggregation method. A generated candidate such as “(A,B) overlaps (A,B,C)” is not permission to drop either one.

4. Protect hidden dependencies and validate real workload evidence

Before dropping an index candidate, check whether it backs a primary key/unique constraint, is the Full-Text key, supports a foreign-key workload, participates in replication/CDC or an HA/migration runbook, is relied upon by forced Query Store plans/hints, or exists for an infrequent business cycle absent from the observation window. Query Store can show plans and runtime history, while schema catalogs show structural dependencies; neither alone is enough.

-- Constraint-backed indexes: do not treat as ordinary drop candidates.SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,       OBJECT_NAME(i.object_id) AS table_name,       i.name,i.is_primary_key,i.is_unique_constraintFROM sys.indexes AS iWHERE i.is_primary_key=1 OR i.is_unique_constraint=1;GO-- Full-Text key check for a specific table/index:-- SELECT INDEXPROPERTY(OBJECT_ID(N'dbo.Documents'),N'UX_Documents_FTKey','IsFulltextKey');GO-- Query Store status before using it as evidence:SELECT actual_state_desc,desired_state_desc,query_capture_mode_desc,       current_storage_size_mb,max_storage_size_mbFROM sys.database_query_store_options;GO

5. Build a reversible portfolio review

A production index decision record should include the exact index definition, observed period and server start time, representative Query Store/plan evidence, read benefit, write/storage cost, dependencies, proposed change, rollback DDL, and acceptance metrics. For a drop candidate, script the exact CREATE INDEX statement before dropping it. Use a deployment window appropriate to object size and topology, and monitor errors, latency, logical reads, waits, Query Store regressions, log growth, replica lag, and maintenance duration.

DROP TABLE IF EXISTS lab09.KnowledgeSnippet;DROP TABLE IF EXISTS lab09.ServiceZone;DROP TABLE IF EXISTS lab09.DeviceDiagnosticXml;DROP TABLE IF EXISTS lab09.FilterProbe;DROP TABLE IF EXISTS lab09.WorkOrderSearch;DROP TABLE IF EXISTS lab09.WideCluster;DROP TABLE IF EXISTS lab09.NarrowCluster;IF SCHEMA_ID(N'lab09') IS NOT NULLAND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab09'))  EXEC(N'DROP SCHEMA lab09;');GO

Authoritative references

• sys.dm_db_index_usage_stats — counter semantics and reset conditions
• sys.dm_db_index_operational_stats — low-level activity and cache-reset semantics
• Index architecture and design guide — avoid duplicate/similar indexes and evaluate workload
• Monitor performance with Query Store — persisted query/plan/runtime evidence
• SQL Server 2025 build versions — servicing baseline