Selected Index Decision
| Field | Value |
|---|---|
| Selected Index | FK_Sales_Orders_CustomerID |
| Table | Sales.Orders |
| Native Classification | NEEDS_MAINTENANCE |
| Score | 85 |
| Native Recommendation | UPDATE_STATISTICS_WITH_FULLSCAN_REVIEW, REVIEW_LEADING_COLUMN_SELECTIVITY |
| Authoritative Action | KEEP_REFERENTIAL |
Maintenance Advisory
- Allowed maintenance: UPDATE_STATISTICS_REVIEW
- Stats age: 3649 days; modification counter: 5262.
- Fragmentation: 97.88%; page count: 189.
- Fragmentation is monitor-only because page_count < 1000; REBUILD/REORGANIZE is not actionable for fragmentation alone.
- REBUILD allowed: No; REORGANIZE allowed: No.
- Executable maintenance:
UPDATE STATISTICS [Sales].[Orders] [FK_Sales_Orders_CustomerID] 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: DO_NOT_DROP
- Confidence: HIGH
- Reason: Index is constraint-backed or supports referential integrity.
- DROP/DISABLE is not authorized unless drop_safety becomes SAFE_DROP_CANDIDATE.
Telemetry Quality
- Confidence label: Strongly supported
- None recorded
Evidence
| Signal | Value | Weight | Supports | Caveat |
|---|---|---|---|---|
| native_classification | NEEDS_MAINTENANCE | high | NEEDS_MAINTENANCE | Deterministic classification must be explained, not overridden. |
| classification_reason | FRAGMENTATION_OR_STATS_OR_CONTENTION | high | NEEDS_MAINTENANCE | |
| observed_reads_writes | reads=285160, writes=0, read_write_ratio=9999.00 | high | workload_value_assessment | DMV counters can reset after service restart, failover, or database detach/attach. |
| drop_safety_decision | DO_NOT_DROP | high | action_policy | Index is constraint-backed or supports referential integrity. |
| telemetry_quality | Strongly supported | high | confidence_and_caveats | |
| computed_recommendations | UPDATE_STATISTICS_WITH_FULLSCAN_REVIEW, REVIEW_LEADING_COLUMN_SELECTIVITY | medium | recommended_followup | |
| flags | EXCELLENT_READ_WRITE_RATIO, NARROW_INDEX, NO_NULLABLE_COLUMNS, HIGH_USAGE, LOW_SELECTIVITY, STALE_STATISTICS | medium | classification_context | |
| warnings | UPDATE_STATISTICS_RECOMMENDED | medium | risk_context | |
| impact_simulation | status=AVAILABLE, queries_affected=2, risk_level=HIGH, confidence=HIGH, estimated_workload_impact_pct=21.8 | high | drop_risk_assessment | Evidence-based estimate; not an optimizer what-if rewrite. |
| supports_fk | True | critical | do_not_drop |
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 | KEEP_REFERENTIAL |
| Drop Safety | DO_NOT_DROP |
| Destructive Index DDL Allowed | No |
| Maintenance Allowed | UPDATE_STATISTICS_REVIEW |
| Update Statistics Script | Allowed |
| Rebuild/Reorganize | Not allowed |
| Telemetry | Strongly supported |
| Telemetry Gaps | None recorded |