Stored Procedure AnalysisQUERY_HEALTHY

Executive Summary (Auto-Generated)

Baseline performance for Demo.usp_Test_01_ParameterSniffing is 0.0 ms average duration, 0.0 ms CPU, and 0 logical reads across 0 executions. Canonical decision: query QUERY_HEALTHY, index OVER_INDEXED, stability STABLE, overall risk MEDIUM.

Performance comparison vs baseline window is unavailable from historical trend data, and current primary query/index signals are: no critical sargability flags captured.

Expected improvement is explicitly estimated at 5-14% faster average duration, with no reliable numeric duration baseline available, subject to validation in the post-change test plan.

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: Insufficient variance evidence; risk inferred from plan count
• Observed plan count: 0
• 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.
• 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.)

📊 Recommendation Quality Score: 0/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 2 (critical 0, warning 2, auto-fixed 2, 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.