Scenario

Demo.usp_test_0004 returns order-line detail filtered by an optional customer, a date range, and a minimum line amount. It is called constantly from a reporting screen with wildly different parameter shapes — sometimes a single customer over a few weeks, sometimes every customer over a full year. Under mixed load it burned far more CPU and I/O than its result sizes suggested, and the team could not point to a single "slow query" because the same procedure was sometimes fast and sometimes catastrophic.

Symptoms

Single-customer calls were cheap (~149 logical reads, tens of ms), but "all customers" calls blew up to 306,471 logical reads on OrderLines and 1.64 s elapsed for one execution.
Execution time was highly unstable: duration CV ~126% and CPU CV ~146%, with the worst execution ~15× slower than the best.
AI analysis classified the object as POOR_QUERY_DESIGN, OVER_INDEXED, CPU-bound, overall risk HIGH.

How We Analyzed It

Each step maps a concrete question to the module and signal used to answer it — all read-only against production.

1
SQLQueryStress
Ran the procedure with randomized parameters (~25% NULL @CustomerID to exercise the "all customers" path) at 1000 iterations × 4/8/16/32 threads, capturing STATISTICS IO/TIME for a selective and a NULL call.
Signal: Selective path ~149 logical reads, tens of ms. NULL path: OrderLines scan count 23,347, 306,471 logical reads, 1,542 physical reads; Customers 46,694 reads; 625 ms CPU / 1,640 ms elapsed. The optional filter, not data volume, drove the blowup.
SQLQueryStress — 1000 iterations × 32 threads; avg logical reads / CPU sec / client sec per iteration.
2
AI Tune (Object Analysis)
Ran AI Performance Analysis on Demo.usp_test_0004 directly from Object Explorer.
Signal: Baseline 102.8 ms avg, 19.5 ms CPU, 8,392 logical reads across 32,000 executions. Canonical: POOR_QUERY_DESIGN, OVER_INDEXED, risk HIGH, CPU_BOUND. Primary pathology COMPUTED_PREDICATE_NON_SARGABLE, with warnings for an implicit conversion and a key lookup. Parameter-sniffing rated LOW from a single compiled-plan snapshot.
AI Tune report header — risk HIGH / CPU bound, 1 Critical / 3 Total Actions, diagnosis confidence 99%.
3
Plan verification (SSMS, read-only)
Re-compiled the cached plan and scanned its XML to confirm each AI claim rather than trusting the report alone.
Signal: Implicit conversion confirmed — CONVERT_IMPLICIT(decimal(10,0), ol.Quantity) inside LineAmount. Non-SARGable predicate confirmed — a Compute Scalar builds LineAmount and a Filter applies >= @MinLineAmount on top of a full columnstore scan of all 1.5M OrderLines rows. The key lookup the report cited was NOT in this compile (columnstore + hash joins) — the cost is plan-dependent.
SSMS execution plan — Columnstore Index Scan → Compute Scalar → Filter, then Hash Match joins and a Sort (40% of cost).
4
Index verification (SSMS, read-only)
Counted indexes and measured fragmentation/usage on the three driving tables to test the OVER_INDEXED claim.
Signal: OrderLines 13 indexes, Customers 10, Orders 8. Several never serve reads but carry heavy write cost. Average fragmentation 28.65% matched the tool — but inflated by tiny 2-page indexes; the genuinely fragmented object is PK_Sales_Orders (92.8% over 13,734 pages).
5
AI Tune (re-analysis)
Applied the tool's P1 (OPTION RECOMPILE) unchanged, re-ran the workload, and re-ran AI Performance Analysis to validate before/after.
Signal: Workload avg logical reads fell from 8,392 to 415 (60,004 executions), but avg duration rose 102.8 → 144.9 ms and CPU 19.5 → 27.5 ms from per-call recompile cost. Risk stayed HIGH, pathology unchanged, and plan-variance signals climbed (plan count 3 → 10, reads-ratio 0.6 → 188.8).
AI Tune Actions (P1) on the RECOMPILE version — root cause (Key Lookup + CONVERT_IMPLICIT), baseline avg 415 reads / 145 ms.
6
Sargability rewrite + AI re-analysis
Implemented the tool's deeper P1 option as usp_test_0004_v2: parameterized dynamic SQL that adds the @CustomerID predicate only when supplied and skips the LineAmount filter when @MinLineAmount = 0, removing the CAST wrapper from the predicate.
Signal: Reads stayed low (420), but every instability signal collapsed — duration/CPU variance 126% → 0%, plan-count variance 10 → 0, implicit-conversion signal gone, index health OVER_INDEXED → BALANCED. Warmed plans reused with 0 ms compile. Risk stayed HIGH / POOR_QUERY_DESIGN — the broad scan-and-sort cost is structural.
AI Tune v2 — Test Plan baseline 130.5 ms / 420 reads / 60,006 executions, plans reused with 0 ms compile.

Evidence

Evidence — Non-SARGable computed predicate

CAST(ol.Quantity * ol.UnitPrice AS DECIMAL(18,2)) >= @MinLineAmount is evaluated per row in a Filter above a full columnstore scan of 1.5M rows. The optimizer cannot seek on a computed expression. This is the tool’s authoritative primary pathology (diagnosis confidence 99%), confirmed in the plan XML.

Evidence — Implicit conversion

The plan contains CONVERT_IMPLICIT(decimal(10,0), ol.Quantity) before the multiply, exactly as the tool warned, degrading cardinality estimation around LineAmount.

Evidence — Plan instability is the real pain

The same NULL call produced two completely different plans: a row-mode nested-loop + key-lookup plan at 306,471 reads (the baseline, and source of the key-lookup signal), versus a batch-mode columnstore + hash-join plan at ~436 reads on a fresh compile.

Evidence — OVER_INDEXED confirmed

13 indexes on OrderLines, several with zero seeks/scans but millions of updates (pure write overhead). Average fragmentation 28.65% matched the tool, but its REBUILD list omitted the largest fragmented index — PK_Sales_Orders (92.8% / 13,734 pages). Rank maintenance by page_count, not percent.

Note

The tool rated parameter sniffing LOW because it saw a single compiled snapshot — its own report flags the caveat. Empirically the risk was real: the heavy path was trapped on a sniffing-victim plan.

Watch out

The tool’s maintenance DDL had a bug — it emitted ALTER INDEX [...] ON [Sales.Customers] (schema and table in one bracket pair), which SQL Server rejects. The index choices were sound once corrected to [Sales].[Customers].

AI Tune v2 canonical classification showing index health BALANCED and risk HIGH — AI Tune v2 — Canonical Classification: Index Health BALANCED, Stability STABLE, Risk HIGH.

Recommendation

Apply the tool's P1 fix to stabilize the plan, then go further on sargability for the residual scan cost; treat index changes as advisory until a usage baseline exists, and refine the maintenance list by page count.

Apply the lowest-risk P1 unchanged: append OPTION (RECOMPILE) so each call compiles for its own parameters (workload reads −95%, but avg CPU/duration +41% — a stabilizer, not ideal to leave permanently on a hot path).
Prefer the sargability rewrite: parameterized dynamic SQL that adds @CustomerID only when supplied, skips the LineAmount filter when @MinLineAmount = 0, and drops the CAST wrapper — matched the read reduction, removed the implicit conversion, and reused one stable plan per shape (variance 126% → 0%).
Treat the remaining cost as a design problem: paginate (OFFSET/FETCH or keyset), pre-aggregate the LineAmount reporting, or add a persisted computed-column index once a usage gate clears.
Align Quantity’s data type to remove the implicit conversion in the LineAmount calculation.
Run index maintenance, but prioritize by page count — PK_Sales_Orders (92.8% / 13,734 pages) outranks the tiny 50%-fragmented Customers indexes the tool listed.
Capture a 14-day Query Store / usage baseline before creating or dropping any index (several indexes are write-only overhead and are drop candidates).

-- Tool's P1 (applied, unchanged): keep the existing SELECT / FROM / WHERE / ORDER BY exactly as-is.
ORDER BY o.OrderDate DESC, LineAmount DESC
OPTION (RECOMPILE);

Note

The application never applies changes automatically. Every script above is an example to review and run under your own change-control process.

Outcome

Two fixes were applied and measured. The quick OPTION (RECOMPILE) collapsed logical reads but added a per-call compile tax. The dynamic-SQL sargability rewrite (v2) kept the I/O win, removed the implicit conversion, and stabilized plans completely without recompile churn — and the tool re-rated index health to BALANCED. The verdict that did not move: across all fixes the AI held risk HIGH / POOR_QUERY_DESIGN, because the dominant cost is scanning and sorting all order lines for broad parameter shapes — a query-design problem, not anything a plan or index hint can resolve.

Workload avg logical reads / exec

8,392→420 (−95%)

Plan stability (duration variance)

126% / 3 plans→0% / 1 reused plan

Implicit-conversion signal

Present→Removed

Index health classification (AI)

OVER_INDEXED→BALANCED

NULL-path OrderLines reads (1 exec)

306,471→~455 (+~1,000 lob)

Overall risk (AI)

HIGH→HIGH (design cost remains)

Modules used in this case

Object Explorer Query Statistics Index Advisor

Back to Use Cases Download