INDEX ANALYSIS REPORTix_Cities_Archive

Selected Index Decision

Payload type B (Object Explorer). The index Application.Cities_Archive.ix_Cities_Archive shows zero DMV reads (seeks 0, scans 0, lookups 0) and zero updates since the last counter reset, with a 28-row table and only 1 page. Telemetry is limited: no Query Store context and no drop-impact estimate, so the usage evidence is insufficient for a high-confidence drop decision. Action is limited to statistics review and monitoring under the deterministic VALIDATE_ONLY policy.

The classification is driven by execution_stats.metrics showing no reads (UserSeeks=0, UserScans=0, UserLookups=0) and no writes (UserUpdates=0) since the DMV counter reset, combined with a low row count (28) and a single 0.01 MB page. Query Store also reports no dependent queries in the 30-day window, reinforcing the zero-read pattern. However this is based on available metrics only; the DMV counters may have been reset, and the statement's source is an index-level snapshot, not query runtime evidence.

Maintenance Advisory

Fragmentation is 0.0% on a 1-page index, so fragmentation is monitor-only. Statistics are stale (3772 days, 102 modifications); UPDATE_STATISTICS is allowed. No DROP, DISABLE, or CREATE INDEX action is permitted by action_policy. ALTER INDEX maintenance is not permitted unless maintenance_policy explicitly allows it. UPDATE STATISTICS maintenance is allowed.

• Authorized maintenance: UPDATE STATISTICS (UPDATE_STATISTICS_REVIEW).

Drop Safety

[Executable INDEX DDL suppressed by validation gate: usage_window_baseline_14d_missing.]

Telemetry Quality

• Telemetry quality is limited: Query Store context is missing and drop-impact estimate is unavailable.
• Baseline reliability is LOW: usage_window baseline < 14 days, days_available=0.01; DMV counters could have been reset.
• No execution plan or runtime counters are available; findings are based on direct object state and index metadata only.

Evidence

Contextual Table Notes

• The table has only this one visible index; there are no sibling indexes to compare.

Recommended Next Steps

_Actions passed an independent critic review (2 approved / 0 revised / 0 rejected)._

Next Step #1 (P3, Medium Risk) — Refresh stale index statistics

• Action Type: UPDATE_STATISTICS
• Expected Impact: Refreshes statistics after 3772 days; may improve cardinality estimates for any future queries on this archive table.
• Script:

UPDATE STATISTICS [Application].[Cities_Archive] [ix_Cities_Archive] WITH FULLSCAN;

• Verification (STATISTICS_ONLY):
• Success criteria: last_updated is within the current maintenance window (ideally < 7 days old) and modification_counter resets.

SELECT name, STATS_DATE(object_id, stats_id) AS last_updated FROM sys.stats WHERE object_id = OBJECT_ID('Application.Cities_Archive') AND name = 'ix_Cities_Archive'

Next Step #2 (P3, Medium Risk) — Monitor 14-day usage before any drop validation

• Action Type: MONITOR
• Expected Impact: Establishes a reliable usage window for re-evaluating whether the index is truly unused or if DMV counters were masked by a reset.
• Verification (MANUAL):
• Success criteria: Collect daily for 14 consecutive days; if user_seeks and user_scans remain 0 over that window, the unused indication strengthens (but does not by itself authorize drop). If any reads appear, re-run drop-safety assessment.

SELECT user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('WideWorldImporters') AND object_id = OBJECT_ID('Application.Cities_Archive') AND index_id = INDEXPROPERTY(OBJECT_ID('Application.Cities_Archive'), 'ix_Cities_Archive', 'IndexID');

Risks and Caveats

• Contradiction: native classification marks the index as drop/disable candidate, but drop safety requires validation and the action gate blocks executable DDL; follow VALIDATE_ONLY.
• DMV usage counters can be reset by service restart, failover, or cache reset; zero reads may be a false negative for a short observation window.
• Flag WRITE_HEAVY appears despite UserUpdates=0, possibly based on modification_counter=102; this soft conflict reduces confidence in write-overhead conclusions.
• Query Store status is unverified; if disabled, enable it only after confirming the environment policy.
• Page_count < 1000, so fragmentation is monitor-only; rebuild/reorganize not recommended.

Notes / Assumptions

• Analysis is based on a single selected-index snapshot from the same refresh cycle; index_analysis_dataset_v2 is used only as workload context, not as a separate historical source.
• Interpretation confidence label: Telemetry gap present.
• Database name assumed from object_resolution: WideWorldImporters; object name remains Application.Cities_Archive.

Recommended Action Script(s)

Copy-ready command(s) authorized by the decision contract for this index. Review the caveats above before running in production.

• Recommended action — refresh statistics:

  UPDATE STATISTICS [Application].[Cities_Archive] [ix_Cities_Archive] WITH FULLSCAN;

Verification & Validation Scripts (safe to run)

Read-only checks you can run to confirm the decision above before changing anything. Replace the database context as needed and run each query in the target database.

• 1. Confirm how the index is actually used — Compares reads (seeks + scans + lookups) against writes since the last service restart. Confirms whether the index is genuinely write-heavy / rarely read.

  SELECT DB_NAME() AS [database], OBJECT_SCHEMA_NAME(s.[object_id]) AS [schema],
         OBJECT_NAME(s.[object_id]) AS [table], i.name AS index_name,
         s.user_seeks, s.user_scans, s.user_lookups,
         (s.user_seeks + s.user_scans + s.user_lookups) AS total_reads,
         s.user_updates AS total_writes,
         s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update
  FROM sys.dm_db_index_usage_stats AS s
  JOIN sys.indexes AS i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
  WHERE s.database_id = DB_ID() AND s.[object_id] = OBJECT_ID(N'[Application].[Cities_Archive]')
    AND i.name = N'ix_Cities_Archive';

• 2. Confirm fragmentation and size — Reads live fragmentation and page count (LIMITED mode is cheap). Confirms whether a REBUILD/REORGANIZE is warranted right now.

  SELECT i.name AS index_name, ps.index_type_desc,
         ps.avg_fragmentation_in_percent, ps.page_count, ps.record_count
  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[Application].[Cities_Archive]'), NULL, NULL, 'LIMITED') AS ps
  JOIN sys.indexes AS i ON i.[object_id] = ps.[object_id] AND i.index_id = ps.index_id
  WHERE i.name = N'ix_Cities_Archive';

• 3. Confirm statistics freshness — Shows when statistics were last updated and how many modifications have accumulated, so you can decide if a statistics refresh is needed.

  SELECT st.name AS stats_name, sp.last_updated, sp.[rows], sp.rows_sampled,
         sp.modification_counter
  FROM sys.stats AS st
  CROSS APPLY sys.dm_db_stats_properties(st.[object_id], st.stats_id) AS sp
  WHERE st.[object_id] = OBJECT_ID(N'[Application].[Cities_Archive]') AND st.name = N'ix_Cities_Archive';

• 4. Confirm which queries depend on the index — Lists Query Store queries whose execution plan references this index, ordered by average duration. Run it in the target database. Confirms the dependent-query risk behind a DO_NOT_DROP decision.

  SELECT TOP (20) q.query_id, p.plan_id, qt.query_sql_text,
         rs.count_executions, rs.avg_duration, rs.avg_logical_io_reads
  FROM sys.query_store_plan AS p
  JOIN sys.query_store_query AS q ON q.query_id = p.query_id
  JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
  WHERE CAST(p.query_plan AS NVARCHAR(MAX)) LIKE N'%' + N'ix_Cities_Archive' + N'%'
  ORDER BY rs.avg_duration DESC;

• 5. Controlled validation before any removal (requires change approval) — This is a procedure, not an auto-generated script: record the baseline from step 1, then disable the index and monitor the workload for at least one full business cycle (e.g. 14–30 days). Re-run steps 1 and 4: if no critical query regresses, the index can be removed; if regressions appear, re-enable or rebuild it. Do not skip the monitoring window.

Decision Contract Guardrails