Selected Index Decision
| Field | Value |
|---|---|
| Selected Index | ix_Countries_Archive |
| Table | Application.Countries_Archive |
| Native Classification | UNNECESSARY |
| Score | 50 |
| Native Recommendation | DROP_OR_DISABLE_CANDIDATE, UPDATE_STATISTICS_WITH_FULLSCAN_REVIEW, REVIEW_WRITE_OVERHEAD |
| Authoritative Action | VALIDATE_ONLY |
Maintenance Advisory
- Allowed maintenance: UPDATE_STATISTICS_REVIEW
- Stats age: 3645 days; modification counter: 1.
- Fragmentation: 7.69%; page count: 47.
- REBUILD allowed: No; REORGANIZE allowed: No.
- Executable maintenance:
UPDATE STATISTICS [Application].[Countries_Archive] [ix_Countries_Archive] WITH FULLSCAN; - 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.
Drop Safety
- Decision: VALIDATE_BEFORE_DROP
- Confidence: LOW
- Reason: Drop impact estimate is unavailable; controlled validation is required.
- DROP/DISABLE is not authorized unless drop_safety becomes SAFE_DROP_CANDIDATE.
Telemetry Quality
- Confidence label: Telemetry gap present
- query_store_context_missing
- drop_impact_estimate_unavailable
Evidence
| Signal | Value | Weight | Supports | Caveat |
|---|---|---|---|---|
| native_classification | UNNECESSARY | high | UNNECESSARY | Deterministic classification must be explained, not overridden. |
| classification_reason | NO_READS_OVER_90_DAYS | high | UNNECESSARY | |
| observed_reads_writes | reads=0, writes=0, read_write_ratio=0.00 | medium | workload_value_assessment | DMV counters can reset after service restart, failover, or database detach/attach. |
| drop_safety_decision | VALIDATE_BEFORE_DROP | high | action_policy | Drop impact estimate is unavailable; controlled validation is required. |
| telemetry_quality | Telemetry gap present | medium | confidence_and_caveats | query_store_context_missing, drop_impact_estimate_unavailable |
| computed_recommendations | DROP_OR_DISABLE_CANDIDATE, UPDATE_STATISTICS_WITH_FULLSCAN_REVIEW, REVIEW_WRITE_OVERHEAD | medium | recommended_followup | |
| flags | NARROW_INDEX, NO_NULLABLE_COLUMNS, WRITE_HEAVY, STALE_STATISTICS, ZERO_READS | medium | classification_context | |
| warnings | UPDATE_STATISTICS_RECOMMENDED | medium | risk_context | |
| impact_simulation | status=UNAVAILABLE, queries_affected=0, risk_level=LOW, confidence=LOW | low | drop_risk_assessment | Evidence-based estimate; not an optimizer what-if rewrite. |
Recommended Next Steps
- Apply only the maintenance action permitted by maintenance_policy.
- Verify Query Store status and investigate collection timeouts. Enable Query Store only if it is disabled.
- Re-evaluate drop/disable only after sufficient telemetry and impact evidence are available.
Risks and Caveats
- DMV counters can reset after service restart, failover, database detach/attach, or short observation windows.
- Drop impact is unavailable; do not infer zero impact or confirmed safety.
Decision Contract Guardrails
| Field | Value |
|---|---|
| Authoritative Action | VALIDATE_ONLY |
| Drop Safety | VALIDATE_BEFORE_DROP |
| Destructive Index DDL Allowed | No |
| Maintenance Allowed | UPDATE_STATISTICS_REVIEW |
| Update Statistics Script | Allowed |
| Rebuild/Reorganize | Not allowed |
| Telemetry | Telemetry gap present |
| Telemetry Gaps | query_store_context_missing, drop_impact_estimate_unavailable |