Demo.usp_Test_01_ParameterSniffing — HIGH risk · 2 actions (1 critical)

1

What's Wrong

🔍

The actual execution plan contains Key Lookup operators on Sales.Orders and Sales.OrderLines. The nonclustered index IX_Demo_KeyLookup_Orders_CustomerID_OrderDate satisfies the predicate seek on CustomerID and OrderDate, but the SELECT list includes OrderID, ExpectedDeliveryDate, and SalespersonPersonID, which are not covered by that index. The optimizer performs a clustered index seek back to PK_Sales_Orders for every qualifying row. A second Key Lookup occurs on Sales.OrderLines for StockItemID, Description, Quantity, UnitPrice, and TaxRate after the IX_OrderLines_OrderID seek. These lookups add random I/O and CPU, especially as result row counts grow.

Main issue: The actual execution plan contains Key Lookup operators on Sales.Orders and Sales.OrderLines. The nonclustered index IX_Demo_KeyLookup_Orders_CustomerID_OrderDate satisfies the predicate seek on CustomerID and OrderDate, but the SELECT list includes OrderID, ExpectedDeliveryDate, and SalespersonPersonID, which are not covered by that index. The optimizer performs a clustered index seek back to PK_Sales_Orders for every qualifying row. A second Key Lookup occurs on Sales.OrderLines for StockItemID, Description, Quantity, UnitPrice, and TaxRate after the IX_OrderLines_OrderID seek. These lookups add random I/O and CPU, especially as result row counts grow.

Current status: Available evidence is sufficient to move forward with validation and controlled rollout planning.

Recommended next step: Test Eliminate key lookup with a covering index — measure duration/CPU/logical reads before and after, and apply additional changes only if the first step proves beneficial.

Confidence: Diagnosis confidence is 97%; action confidence should be treated separately.

Data Warning

Evidence is sufficient for the diagnosis, but no runtime baseline (DMV / Query Store) was captured; quantified impact is directional until one is.

AI Safety Review: NEEDS_REVIEW

Reason: Response recommends creating an online covering index and includes index maintenance/rebuild-reorg guidance. The DROP INDEX appears only as the rollback for the proposed covering index, so it is classified as a write action requiring review rather than destructive SQL. Overall write-capable schema/maintenance advice requires DBA review.

CREATE NONCLUSTERED INDEX IX_Orders_KeyLookup_Covering ON Sales.Orders (CustomerID, OrderDate) INCLUDE (OrderID, ExpectedDeliveryDate, SalespersonPersonID) WITH (ONLINE = ON);
DROP INDEX IX_Orders_KeyLookup_Covering ON Sales.Orders;
rebuild/reorg should apply only to indexes with page_count >=1000 and fragmentation >=30%

The analysis text above is shown unchanged; review the flagged items before acting.

2

2 Problem Details

1

The actual execution plan contains Key Lookup operators on Sales.Orders and Sales.OrderLines. The nonclustered index IX_Demo_KeyLookup_Orders_CustomerID_OrderDate satisfies the predicate seek on CustomerID and OrderDate, but the SELECT list includes OrderID, ExpectedDeliveryDate, and SalespersonPersonID, which are not covered by that index. The optimizer performs a clustered index seek back to PK_Sales_Orders for every qualifying row. A second Key Lookup occurs on Sales.OrderLines for StockItemID, Description, Quantity, UnitPrice, and TaxRate after the IX_OrderLines_OrderID seek. These lookups add random I/O and CPU, especially as result row counts grow.

P1High

Plan insights: has_key_lookup=true; operator_digest top_operators includes Key Lookup on Sales.Orders (node_id 12) and Sales.OrderLines (node_id 15).

2

Query Store shows 5 distinct plans with a 6.9x duration ratio between best (2.23 ms) and worst (15.39 ms) plans. Average logical reads range from 5 to 1103.6 across plans. Duration CV is 114% and CPU CV is 87%, indicating execution shape changes with parameter values even though parameter_sniffing currently labels risk as low due a single captured compiled-parameter snapshot.

P2Medium

parameter_sniffing: plan_variance_analysis (best/worst ratios), duration_cv_pct=114.1; query_store.summary plan_count=5.

3

Actions (P1)

1

Eliminate key lookup with a covering index

P1HighVerification SQL🧪 Test Required

▼

Root Cause

The existing index IX_Demo_KeyLookup_Orders_CustomerID_OrderDate lacks INCLUDE columns for the SELECT list; the optimizer must perform a Key Lookup to PK_Sales_Orders for each row.

Why This Matters

Key Lookups multiply I/O and CPU for each qualifying row; removing them is the primary structural fix for this procedure.

Change

Fix Steps

Add the key-lookup columns as INCLUDE columns to the existing seek index so the engine can satisfy the query without a bookmark lookup. Review the full column list from the execution plan before altering the index.

Expected Impact

Eliminates the Key Lookup operator entirely — the index satisfies both the seek predicate and the output projection in a single operator. Logical reads typically drop by a factor proportional to the qualifying row count (one lookup per row → zero lookups). Duration improvement is most pronounced when the query returns 100+ rows; for narrow result sets (< 50 rows) the win is modest.

Suggested Rewrite

```sql
-- Covering index eliminates the Key Lookup operator on Sales.Orders
CREATE NONCLUSTERED INDEX IX_Orders_KeyLookup_Covering
    ON Sales.Orders (CustomerID, OrderDate)
    INCLUDE (
        OrderID,
        ExpectedDeliveryDate,
        SalespersonPersonID
    ) WITH (ONLINE = ON);
```

Code Example (Before / After)

-- Covering index eliminates the Key Lookup operator
CREATE NONCLUSTERED INDEX IX_Orders_KeyLookup_Covering
    ON Sales.Orders (CustomerID, OrderDate)
    INCLUDE (
        -- SELECT * detected — list every non-key output column
    ) WITH (ONLINE = ON);

Verification SQL

SET STATISTICS IO, TIME ON;
EXEC Demo.usp_Test_01_ParameterSniffing;
SET STATISTICS IO, TIME OFF;

Rollback

DROP INDEX IX_Orders_KeyLookup_Covering ON Sales.Orders; -- Rollback is instant; no data loss possible. The dropped index returns the SP to Key Lookup behaviour.

2

Validate the recommended changes against a measured baseline

P3LowVerification SQL🧪 Test Required✓ Safe to Execute

▼

Why This Matters

Without a captured baseline nobody can tell whether the applied change helped, hurt, or did nothing.

Change

Capture baseline Query Store metrics for current workloads (average duration, logical reads, plan count). 2. Deploy covering index on Sales.Orders in a test environment with ONLINE=ON. 3. Run representative parameter sets including @CustomerID=1195, @StartDate='2015-08-30', @EndDate='2016-09-23' and broad ranges. 4. Compare actual plan: confirm Key Lookup operator removed and Sort/Spill risk reduced. 5. Test with OPTION (RECOMPILE) on high-variance executions and monitor CPU overhead vs read savings.

Fix Steps

1. Validate index usage after 14 days; if unused, consider drop.

Expected Impact

Produces before/after evidence for the corrective actions above; this step itself changes no code or database object.

Verification SQL

DECLARE @object_name sysname = 'Demo.usp_Test_01_ParameterSniffing';
DECLARE @window_start datetimeoffset = DATEADD(day, -14, SYSDATETIMEOFFSET());
DECLARE @window_end datetimeoffset = SYSDATETIMEOFFSET();
DECLARE @change_time datetimeoffset = NULL; -- Set this after applying the tested change.

SELECT
    CASE
        WHEN @change_time IS NULL THEN 'current_window'
        WHEN qsrsi.end_time < @change_time THEN 'before'
        ELSE 'after'
    END AS measurement_window,
    qsp.plan_id,
    qsp.query_plan_hash,
    SUM(qrs.count_executions) AS count_executions,
    CAST(
        SUM(qrs.avg_duration * qrs.count_executions)
        / NULLIF(SUM(qrs.count_executions), 0) / 1000.0
        AS decimal(18, 2)
    ) AS weighted_avg_duration_ms,
    CAST(MAX(qrs.max_duration) / 1000.0 AS decimal(18, 2)) AS max_duration_ms,
    CAST(
        SUM(qrs.avg_cpu_time * qrs.count_executions)
        / NULLIF(SUM(qrs.count_executions), 0) / 1000.0
        AS decimal(18, 2)
    ) AS weighted_avg_cpu_ms,
    CAST(
        SUM(qrs.avg_logical_io_reads * qrs.count_executions)
        / NULLIF(SUM(qrs.count_executions), 0)
        AS decimal(18, 2)
    ) AS weighted_avg_logical_reads,
    MAX(qrs.max_logical_io_reads) AS max_logical_reads,
    MAX(qrs.last_execution_time) AS last_execution_time
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qrs
    ON qrs.plan_id = qsp.plan_id
JOIN sys.query_store_runtime_stats_interval AS qsrsi
    ON qsrsi.runtime_stats_interval_id = qrs.runtime_stats_interval_id
WHERE qsq.object_id = OBJECT_ID(@object_name)
  AND qsrsi.end_time >= @window_start
  AND qsrsi.start_time <= @window_end
GROUP BY
    CASE
        WHEN @change_time IS NULL THEN 'current_window'
        WHEN qsrsi.end_time < @change_time THEN 'before'
        ELSE 'after'
    END,
    qsp.plan_id,
    qsp.query_plan_hash
ORDER BY MAX(qrs.last_execution_time) DESC;

Rollback

Not applicable — this action only collects evidence and makes no database change.

Not Shown

1 environment-scoped actions excluded (not attributable to this object's code). See the response contract for the complete set.

4

Test Plan

1
Capture baseline Query Store metrics for current workloads (average duration, logical reads, plan count).
2
Deploy covering index on Sales.Orders in a test environment with ONLINE=ON.
3
Run representative parameter sets including @CustomerID=1195, @StartDate='2015-08-30', @EndDate='2016-09-23' and broad ranges.
4
Compare actual plan: confirm Key Lookup operator removed and Sort/Spill risk reduced.
5
Test with OPTION (RECOMPILE) on high-variance executions and monitor CPU overhead vs read savings.
6
Validate index usage after 14 days; if unused, consider drop.
7
Action Confidence: 55%
8
Runtime Impact Confidence: 93%
9
SQL Validity: Passed
10
Evidence Sufficiency: Sufficient
11
Production Readiness: Test Required
12
Mentioned execution count (280) differs significantly from actual (1)
13
Action confidence is lower than diagnosis confidence; test proposed changes before production.

5

Confidence & Plan

Diagnosis

97%

Problem diagnosis confidence

Action

55%

Recommendation execution confidence

Runtime Impact

93%

Observed impact evidence confidence

Rollback

85%

Response includes an explicit backout path.

Signal	Value
Primary Pathology	KEY_LOOKUP
Runtime Bottleneck	CPU_BOUND
Query Design	POOR_QUERY_DESIGN
Index Health	OVER_INDEXED
Stability	STABLE
Risk	HIGH
Environment / Workload	None attributed
Contributing Factors	PARAMETER_SNIFFING

Related — Unverified

Key Lookup on Sales.Orders and Sales.OrderLines increases I/O per row

Environment / Workload

Rebuild 12 fragmented index(es) on 2 tables — worst: FK_Sales_Orders_SalespersonPersonID at 98%

Risk Level: Low
Evidence: duration CV 114%, best/worst duration ratio 6.9x, logical reads ratio 220.7x; Query Store shows 5 plans but parameter_sniffing.plan_count=1 and compiled-parameter snapshot present; no compiled-vs-runtime mismatch confirmed.
What Changes with Different Parameters: Plan shapes vary between index seek and clustered scan patterns based on date range selectivity and customer ID cardinality; worst plan uses 1103.6 reads vs 5 for best.
Safe Mitigations: OPTION (RECOMPILE) for high-variance executions, or split wide date ranges to avoid a single broad plan.
Verification Steps: Run with multiple known parameter sets; compare Query Store plan shapes and logical reads; confirm OPTION (RECOMPILE) reduces variance without excessive CPU overhead.

Signal	Value
Bottleneck	CPU BOUND
Priority	P1
Risk	HIGH
SQL Validity	Passed
Evidence	Sufficient
Production	Test Required
Query	POOR QUERY DESIGN
Index	OVER INDEXED
Stability	STABLE
Pathology	KEY LOOKUP