Stored Procedure AnalysisPOOR_QUERY_DESIGN (POOR_QUERY_DESIGN)

🧾 Executive Summary

• Overall Risk: HIGH — insufficient execution stats; compile-time plan evidence confirms non‑sargable predicates that force full scans, an expensive Sort, and a large memory grant.
• Top Impact Driver: Non‑sargable WHERE clause (YEAR(…), leading‑wildcard LIKE, CONVERT on column) prevents index seeks, causing a 23K‑row scan and a parallel plan with a Sort.
• 24‑Hour Action Focus: Rewrite the WHERE predicates to be sargable; monitor execution stats and memory grant reduction after change.

📊 Classification Summary

Based on available compile‑time plan, code shape, and partial workload‑wide index usage metrics. Index over‑indexing and fragmentation are workload‑wide signals with limited direct SP evidence.

Primary Actionable Findings

Index Recommendation Gate (Enforced)

• Reason: usage_window_baseline_14d_missing
• Covered indexes: 32/32
• Missing data checklist:
• usage_window(14d baseline mandatory)
• usage_window_reliability(reliability_check.status>=MEDIUM)
• fix_sargability(leading_wildcard/function_on_column/computed_predicate/implicit_convert/RBAR)
• plan_signal_normalization(mi_columns_signature)
• missing_index_dmvs(mi_score,mi_columns_signature)

-(not executable):

Contextual Findings

Candidate index signature (contextual-only, not executable)

• Table: Sales.OrderLines
• Key: int
• Include: (none)

Runtime Evidence Limitations

• Execution plan runtime counters are unavailable.
• Any compile-vs-runtime or row-estimation claims should be treated as hypothesis, not confirmed fact.

Parameter Sniffing Analysis

• Risk level: LOW
• Evidence: Single compiled-parameter snapshot observed in plan XML; alternate parameter shapes are not yet verified.
• Observed plan count: 0
• Top parameter samples: @SearchTerm=N'Superhero', @OrderYear=(2015)
• Note: current compiled plan reflects a single parameter snapshot from plan XML; low current risk does not prove that larger or different parameter shapes will behave similarly.
• Note: Runtime counters are unavailable; parameter sensitivity conclusions are low-confidence.
• Note: current parameter_sniffing.risk_level is LOW; treat OPTION (RECOMPILE), OPTIMIZE FOR, local-variable mitigations, plan guides, and plan forcing as contingency options only if later workload testing shows parameter sensitivity and a better alternative plan is observed.
• Verification: compare Query Store plan variance and avg duration/CPU/reads across diverse parameter values.

Test/Validation Plan

• Baseline capture: record avg duration, avg CPU, avg logical reads, execution count, and plan hash.
• Current baseline snapshot: duration 0.00 ms, CPU 0.00 ms, reads 0.00, executions 0.
• Execute representative parameter matrix (low/medium/high selectivity) and compare plan stability via Query Store.
• Validate seek/scan deltas and logical read reduction after each change; rollback if regressions exceed tolerance.
• Monitor 24-72 hours post-change for wait profile shifts, blocking/deadlocks, and plan regressions.

Risks and Caveats

• Gate active (usage_window_baseline_14d_missing): index-related caveats are only partially evaluated in this run because structural index analysis is policy-blocked. Validate all recommendations on a representative workload before production rollout.
• Available index-pressure signals are still partial under the active gate: scan_dominance_score=1.18, join_access_assessment=JOIN_ACCESS_SEEK_FRIENDLY, scan_to_seek_ratio=0.0241.
• Structural coverage note: join-key coverage is 100.00%; this can reduce index-gap confidence even when workload scans exist.
• Runtime evidence is incomplete in the current window; treat causal claims and impact sizing as low-confidence until Query Store/runtime counters are available.
• Parameter evidence comes from a single compiled snapshot; different selectivity patterns may still produce different cardinality and plan-shape outcomes.
• Apply one change at a time, compare Query Store deltas (duration/CPU/reads/plan hash), and keep a rollback path ready for regressions.

Join Signal Reconciliation

• structural_key_coverage_pct: 100.00 (design metadata only; not direct seek/scan efficiency proof)
• structural_coverage_assessment: GOOD_STRUCTURAL_JOIN_COVERAGE
• join_access_assessment: JOIN_ACCESS_SEEK_FRIENDLY
• join_access_scan_to_seek_ratio: 0.0241
• scan_acceptable_small_table_count: 0
• large_table_scan_heavy_count: 0

Key Lookup Signal Reconciliation

• plan_has_key_lookup_operator: false
• dmv_user_lookups_total: 2111872
• mismatch_note: DMV index-usage counters show historical/broader workload context lookup activity while the captured current plan shows no Key Lookup operator. Do not attribute the DMV total to this execution without an aligned plan window.
• Note: workload-wide scan/seek and index-usage counters are contextual signals; do not treat them as direct evidence for this SP.

Canonical Classification (Labels Authoritative, Certainty Evidence-Bounded)

This section is generated from deterministic canonical_decision: class labels are authoritative, but certainty and impact wording must stay evidence-bounded.

Consistency Warnings (Auto-Check)

• Auto-fixed: Softened 1 low-risk parameter-sniffing mitigation line/block(s) to contingency-only guidance. (When parameter_sniffing.risk_level=LOW, keep RECOMPILE/OPTIMIZE FOR/local-variable/plan-guide/plan-forcing guidance as contingency options instead of primary fixes.)
• Auto-fixed: Softened 1 direct-appearing line(s) that relied on workload-wide counters. (Keep workload-wide scan/seek/index-usage counters in contextual tone unless the evidence pointer itself is direct for this SP.)
• Auto-fixed: Suppressed 0 gate-blocked index DDL block(s) and 1 standalone index DDL line(s). (When index gate is blocked, keep only candidate signatures and data-collection guidance.)
• Auto-fixed: Contextualized 1 candidate index signature section(s) (moved to Contextual Findings: 1). (For gate-blocked index suggestions, keep candidate signatures in Contextual Findings only.)
• Auto-fixed: Removed SQL/noise tokens from 3 candidate-signature line(s). (Candidate signature Table/Key/Include lines should contain only object and column identifiers.)

📊 Recommendation Quality Score: 57/100

• Scope: final rendered report after response validation and consistency checks.
• Method: base response_validator._calculate_quality_score + unresolved consistency penalties (critical -30, warning -5; auto-fixed consistency items are tracked separately and do not lower the final score).
• Validation Issues: total 1 (critical 0, warning 1).
• Consistency Issues: total 5 (critical 0, warning 5, auto-fixed 5, remaining 0).
• Blocked Commands Filtered:0.

🟠 Diagnosis Confidence: 79%

• Action Confidence: 55%
• Expected Impact Confidence: 12%
• SQL Validity: Passed
• Evidence Sufficiency: Insufficient
• Production Readiness: Test Required
• Confidence (Query evidence): 0%
• Confidence (Plan evidence): 100%
• Confidence (Index evidence): 80%
• Confidence (Root Cause): 79% (medium)
• Confidence (Expected Impact): 12% (very_low)
• Confidence (Action Safety): 55% (low)
• Confidence (Rollback): 79% (medium)

⚠️ Validation Warnings:

• Action confidence is lower than diagnosis confidence; test proposed changes before production.