Query AnalysisDataLoadSimulation.InvoicePickedOrders

Executive Summary (Deterministic Snapshot)

Executive Summary

2 issue(s) diagnosed; the most severe is Plan instability with a high-latency plan (HIGH, PLAN_STABILITY). Primary cause assessment: QUERY_ISSUE (confidence: medium). 2 recommendation(s) follow (1 at P1 priority).

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

Primary Bottleneck

Primary cause: QUERY_ISSUE (confidence: medium).

Leading issue I1 — Plan instability with a high-latency plan [HIGH, PLAN_STABILITY, evidence: E2]: Query Store shows two plans for this query: the dominant plan averages 0.08 ms and 2 logical reads, while the alternative plan averages 6.95 s and 5.72M logical reads. The p95 duration of 2347 ms is driven by the slow plan's 132 executions. This is a classic plan-regression or parameter-sensitivity pattern.

Detailed Findings

I1 — Plan instability with a high-latency plan

• Severity: HIGH | Category: PLAN_STABILITY
• Basis: inferred_from_metrics (metric inference)
• Evidence: E2
• Detail: Query Store shows two plans for this query: the dominant plan averages 0.08 ms and 2 logical reads, while the alternative plan averages 6.95 s and 5.72M logical reads. The p95 duration of 2347 ms is driven by the slow plan's 132 executions. This is a classic plan-regression or parameter-sensitivity pattern.

I2 — Row-by-row cursor processing causes high CPU and logical I/O

• Severity: HIGH | Category: QUERY_SHAPE
• Basis: inferred_from_metrics (metric inference)
• Evidence: E1, E2
• Detail: The stored procedure iterates orders with a cursor and performs multiple INSERT/UPDATE statements per row. This row-by-row processing is consistent with the observed avg CPU of 239 ms and avg logical reads of 199,999 per execution.

Checked and cleared (non-issues):

• Missing index candidate E4 on Sales.Invoices is not aligned with this query: The index columns ConfirmedDeliveryTime/InvoiceDate do not appear in the cursor population query's NOT EXISTS on OrderID, so its 82% impact is likely broader workload context rather than specific to query 73.
• Note: workload-wide scan/seek and index-usage counters are contextual signals; do not treat them as direct evidence for this SP.
• Server-level resource pressure is not evident: OS CPU is 3%, PLE is 77,634 seconds, and buffer cache hit ratio is 100%, indicating the problem is query-local rather than a shared-resource bottleneck.

Prioritized Recommendations

R1 (P1) — Force the fast plan (plan_id 2458) using Query Store to eliminate plan regressions.

• Priority: P1 | Risk: LOW | Type: PLAN_MANAGEMENT | Audience: DBA
• Addresses issue(s): I1 | Evidence: E2
• Rationale: Query Store shows two plans with vastly different performance: plan 2458 averages 0.08 ms and 2 logical reads, while plan 2459 averages 6.95 s and 5.72M logical reads. The p95 of 2347 ms is caused by the slow plan's 132 executions. Forcing the fast plan should stabilize latency and reduce average resource usage. Risk is low because the fast plan has been used successfully for 3641 executions.
• Expected effect (Estimated): Eliminate plan regressions and reduce p95 duration from ~2.3 s to near the fast plan's ~0.08 ms average.

R2 (P2) — Rewrite the cursor-based row-by-row processing into set-based operations to reduce CPU and logical I/O.

• Priority: P1 | Risk: MEDIUM | Type: QUERY_REWRITE | Audience: DEV
• Addresses issue(s): I2 | Evidence: E1, E2
• Rationale: The stored procedure iterates through orders with a cursor and performs multiple INSERT/UPDATE statements per row. This pattern is consistent with the observed average CPU of 239 ms and very high logical reads (199,999). Replacing the loop with set-based logic would process all orders in fewer statements, dramatically reducing CPU and logical I/O. Risk is medium due to increased code complexity and testing requirement.
• Expected effect (Directional_only): Significantly reduce average CPU and logical reads per execution, potentially lowering average duration to under 50 ms.

Verification Plan

Verify R1 — Force the fast plan (plan_id 2458) using Query Store to eliminate plan regressions. (effect: Estimated):

SELECT plan_id, is_forced_plan, last_forced_plan_failure_desc FROM sys.query_store_plan WHERE query_id = 73 AND plan_id = 2458;

Risks And Caveats

• Diagnosis confidence is medium.
• Evidence gap — wait_profile.coverage: Recorded waits cover only 1.9% of total runtime, so the CPU-bound classification is uncertain; other bottlenecks may be hidden.
• Evidence gap — plan_xml_operators: The plan XML is truncated and no per-operator runtime counters are present, preventing confirmation of exactly which operators cause high logical reads or why the two plans diverge.
• Evidence gap — missing_index_scope: The missing index candidate E4 is not linked to any predicate in this query, so its relevance to query 73 is unproven.
• R2 carries MEDIUM regression risk; apply with staged validation.

Canonical Classification (Deterministic Candidate, Evidence-Reconciled)

This section is generated deterministically. The candidate is reconciled against stronger direct evidence before priority and action eligibility are assigned.

Consistency Warnings (Auto-Check)

• Auto-fixed: Removed 1 unsupported Key Lookup claim line(s). (Emit Key Lookup narrative only when plan_insights.has_key_lookup=true.)
• 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: Aligned 1 priority tag(s) to authoritative report priority P1. (Keep section and action priority tags consistent with the authoritative report priority.)

🔒 AI Safety Review: NEEDS_REVIEW. Contains operational, review-sensitive recommendations: forcing a Query Store plan and rewriting a stored procedure. No destructive SQL or blocked commands were detected, but these actions affect production behavior and require DBA/operator review.

📊 Recommendation Quality Score: 80/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 0 (critical 0, warning 0).
• Consistency Issues: total 3 (critical 0, warning 3, auto-fixed 3, remaining 0).
• Blocked Commands Filtered: 0.

Deterministic Diagnostics Notes

• Plan Stability Time Window: Last 7 Days (source: query_store_runtime_stats_interval).
• Dominant Wait Category: CPU.
• Representative Wait Types: SOS_SCHEDULER_YIELD.

Plan Stability Action Table

• Worst observed plan: 2459 (6948.93 ms).
• Best force-candidate plan: 2458 (0.08 ms).
• Gap: +6948.86 ms (+8904148.6% slower vs best).
• Stability assessment: PROBLEM (worst/best 89042.49x).

Evidence Appendix

_Click [E#] references in the report to jump here._

E1

• Type: wait_profile
• Details: CPU 82.65%, total_wait_ms 14,634.
• Representative wait types: SOS_SCHEDULER_YIELD.

E2

• Type: metrics
• Details: avg_duration_ms 243.19, p95_ms 2347.20, avg_cpu_ms 239.50, avg_logical_reads 199,999, executions 3,773, plan_count 2.

E4

• Type: missing_index
• Claim: Missing index candidate with estimated impact.
• Data: table=Sales.Invoices, key_columns=['ConfirmedDeliveryTime', 'InvoiceDate'], inequality_columns=['InvoiceDate'], include_columns=[], impact=82.0, redundancy_signals={'clustered_key_columns': ['InvoiceID'], 'is_key_already_clustered': False, 'key_equals_clustered_key': False}.

E5

• Type: server_metrics
• Server metrics: sql_cpu_percent 0.00, ple_seconds 77,634, io_read_latency_ms 3.00, io_write_latency_ms 1.00, signal_wait_percent 23.00.