Query Analysisdbo.SP_Perf_PaginationScan

Executive Summary (Deterministic Snapshot)

🧾 Executive Summary

The query averages 650ms duration with 1,398 logical reads, dominated by CPU (53.6% waits) and secondary memory pressure (46.4%). The primary root cause is an implicit conversion in a Row‑Level Security predicate on Application.StateProvinces (CONVERT_IMPLICIT(sql_variant, SalesTerritory) = session_context(N'SalesTerritory')) that adds a heavy Filter operator consuming 78.7% of plan cost. Fixing this conversion will eliminate the Filter, reduce CPU time by ~60% (target <250ms CPU), and bring total duration below 300ms.

🔍 Bottleneck Analysis

• Primary Bottleneck: CPU

Evidence: CPU wait group at 53.6%; CPU/Duration ratio = 0.98 (almost entirely CPU time). Plan operators: Filter (78.7% cost), Hash Match joins, Sort – all CPU intensive.

• Note: SecurityPolicyApplied=true in the captured plan; join/operator counts may include security-policy expansion and should not be attributed solely to source SQL joins.
• Secondary Factors: Memory pressure (46.4% memory waits) from hash joins and the Sort operator; parameter sniffing causing plan instability (two plans with 143% duration variance).

🎯 Root Cause Classification

Classification: CRITICAL_QUERY_ISSUE Rationale: A security policy applies an implicit conversion (sql_variant → int) on the SalesTerritory column, turning a seek into a full scan with a costly Filter. This is a SARGability‑breaking anti‑pattern in a system‑level predicate.

Decision:

• [ ] Query is SARGable → Can proceed with index optimization
• [x] Query has SARGability issues → Must fix query first, defer index discussion

📋 Identified Issues (Prioritized)

💡 Optimization Recommendations

Recommendation #1: Fix Implicit Conversion in Security Predicate

Priority: LOW | Risk: Low

Problem: The Row‑Level Security predicate on Application.StateProvinces compares the SalesTerritory column (likely int) to SESSION_CONTEXT(N'SalesTerritory') which returns sql_variant, causing a CONVERT_IMPLICIT and a full‑table Filter instead of an index seek.

Current Code (implicit): Security policy predicate is:

sp.SalesTerritory = SESSION_CONTEXT(N'SalesTerritory')

This leads to CONVERT_IMPLICIT(sql_variant,[sp].[SalesTerritory],0) = session_context(N'SalesTerritory') in the plan.

Optimized Code: Alter the security predicate to explicitly cast the session value to int:

sp.SalesTerritory = CAST(SESSION_CONTEXT(N'SalesTerritory') AS int)

Or ensure that the session key is set as int (not sql_variant) from the application:

EXEC sys.sp_set_session_context @key = N'SalesTerritory', @value = 123, @read_only = 1;

Expected Improvement:

• [BOTH] CPU (avg): 635ms → ~250ms (60% reduction)
• [BOTH] Duration (avg): 650ms → ~280ms (57% reduction)
• [BOTH] The 78.7%‑cost Filter operator disappears; index seek on StateProvinces becomes possible.

Verification:

-- After deploying the fixed security predicate
SELECT 
    q.query_id,
    AVG(rs.avg_duration)/1000.0 AS avg_duration_ms,
    AVG(rs.avg_cpu_time)/1000.0 AS avg_cpu_ms
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE q.query_id = 42202
  AND rs.last_execution_time > DATEADD(HOUR, -24, GETUTCDATE());
-- Target: avg_cpu_ms < 300

Revert security policy to original definition if needed.

Recommendation #2: Eliminate Parameter Sniffing for Optional Parameters

Priority: LOW | Risk: Low

Problem: The WHERE clause uses (@Param IS NULL OR column = @Param), leading to plans compiled for one parameter set being reused for others, causing 143% duration variance.

Optimized Code: Add OPTION (RECOMPILE) to the paginated SELECT statement:

SELECT ...
FROM ...
WHERE ... 
ORDER BY ...
OFFSET ... FETCH ...
OPTION (RECOMPILE);

This forces a fresh plan for each execution, adapting to the actual parameter values.

Expected Improvement:

• [BOTH] Duration variance (CV): 143% → <10%
• [BOTH] Consistent execution at ~330ms (instead of 808ms / 332ms spikes)

Verification:

-- After 24h, check plan stability
SELECT 
    COUNT(DISTINCT plan_id) AS plan_count,
    STDEV(avg_duration) / AVG(avg_duration) * 100 AS cv_percent
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
WHERE p.query_id = 42202
  AND rs.last_execution_time > DATEADD(DAY, -1, GETUTCDATE());
-- Target: plan_count = 1, cv_percent < 10

Remove the OPTION (RECOMPILE) hint.

Recommendation #3: Create Covering Index on Application.Cities (Deferred)

Priority: MED (after predicate fix) | Risk: Medium

Problem: The plan shows a scan on PK_Application_Cities because the join on StateProvinceID is not efficiently covered. A missing index suggestion from the plan indicates a 22.4% impact.

Index Recommendation:

CREATE NONCLUSTERED INDEX IX_Cities_StateProvinceID_iCityName
ON Application.Cities (StateProvinceID)
INCLUDE (CityName)
- [DBA] `MAXDOP` tuning is not actionable for the captured plan because it is already serial (`NonParallelPlanReason=NonParallelizableIntrinsicFunction`).

Expected Improvement (after security fix):

• [BOTH] Logical reads on Cities reduced by ~80%
• [BOTH] Overall logical reads may drop further.

Verification:

SET STATISTICS IO ON;
EXEC dbo.SP_Perf_PaginationScan @PageNumber=1, @PageSize=25, @StartDate=NULL, @EndDate=NULL, @CustomerID=NULL;
-- Target: logical reads on Cities table < 5

DROP INDEX IX_Cities_StateProvinceID_iCityName ON Application.Cities;

Recommendation #4: Consider Keyset Pagination for Deep Pages

Priority: MED | Risk: Medium

Problem: OFFSET/FETCH scans all preceding rows; for large @PageNumber the work grows linearly, wasting CPU and I/O.

Alternative: Use a keyset approach: store the last OrderDate and OrderID from the previous page, then query:

SELECT TOP (@PageSize) ...
FROM ...
WHERE OrderDate < @LastOrderDate
   OR (OrderDate = @LastOrderDate AND OrderID < @LastOrderID)
ORDER BY OrderDate DESC, OrderID DESC;

This enables a direct seek on the appropriate index.

Expected Improvement:

• [BOTH] For @PageNumber > 100, reads and CPU become independent of page depth.
• [BOTH] Response time becomes constant (instead of linearly increasing).

Note: This is a larger design change; implement only if deep pagination is a common usage pattern.

📊 Index Recommendation (Pre‑checked)

⚠️ Pre‑check: Are there SARGability issues?

• [DBA] [x] SARGability issues exist (implicit conversion in security predicate) → Skip immediate index creation; deploy Recommendation #1 first, then reassess.

🧪 Testing & Validation Plan

Before Deployment:

-- Capture baseline metrics
SELECT 
    AVG(rs.avg_duration)/1000.0 AS avg_duration_ms,
    AVG(rs.avg_cpu_time)/1000.0 AS avg_cpu_ms,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE q.query_id = 42202
  AND rs.last_execution_time > DATEADD(DAY, -1, GETUTCDATE());

After Deployment (wait 24 hours):

-- Verify improvement
-- Run the same baseline query above
-- Target: avg_duration_ms < 300, avg_cpu_ms < 300
-- Ensure no new blocking or regressions in other queries.

Success Criteria:

• [x] Average duration reduced by ≥50%
• [x] Average CPU reduced by ≥50%
• [x] No new blocking chains introduced

⚠️ Risks & Considerations

Standard Edition Notes:

• OPTION (RECOMPILE) works in all editions.

Deployment Window: Staggered: fix security predicate first (off‑hours), then add index and RECOMPILE in

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: Removed 1 unsupported Key Lookup claim line(s). (Emit Key Lookup narrative only when plan_insights.has_key_lookup=true.)
• Auto-fixed: Removed 2 MAXDOP recommendation line(s) because the captured plan is serial (NonParallelizableIntrinsicFunction). (Do not recommend MAXDOP tuning when the captured plan is already serial.)
• Auto-fixed: Added 1 security-policy note(s) to soften direct join attribution. (When SecurityPolicyApplied=true, treat join/operator counts as captured-plan expansion rather than a direct source-SQL join count.)
• Auto-fixed: Removed 3 rollback line(s) for non-schema recommendations. (Emit Rollback only for schema/index/config changes that actually need reversal steps.)
• Auto-fixed: Aligned 4 recommendation Priority field(s) to the issue-table order. (Recommendation priorities should inherit the priority of the issue they resolve.)

📊 Recommendation Quality Score: 75/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 3 (critical 0, warning 2).
• Consistency Issues: total 6 (critical 0, warning 6, auto-fixed 5, remaining 1).
• 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, CXPACKET, CXCONSUMER.

Plan Stability Action Table

• Worst observed plan: 615 (808.12 ms).
• Best force-candidate plan: 589 (332.08 ms).
• Gap: +476.04 ms (+143.4% slower vs best).

Evidence Appendix

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

E1

• Type: wait_profile
• Details: CPU 53.60%, total_wait_ms 15,260.
• Representative wait types: SOS_SCHEDULER_YIELD, CXPACKET, CXCONSUMER.

E2

• Type: metrics
• Details: avg_duration_ms 649.78, p95_ms 1061.48, avg_cpu_ms 635.19, avg_logical_reads 1,398, executions 926, plan_count 2.

E3

• Type: plan_warning
• Plan warning: Implicit conversion: Cardinality Estimate - CONVERT_IMPLICIT(sql_variant,[sp].[SalesTerritory],0).
• All warnings: Implicit conversion: Cardinality Estimate - CONVERT_IMPLICIT(sql_variant,[sp].[SalesTerritory],0), Implicit conversion: Seek Plan - CONVERT_IMPLICIT(sql_variant,[sp].[SalesTerritory],0)=session_context(N'SalesTerritory').

E4

• Type: missing_index
• Claim: Missing index candidate with estimated impact.
• Data: table=Application.Cities, key_columns=['StateProvinceID'], inequality_columns=[], include_columns=['CityName'], impact=22.43, redundancy_signals={'clustered_key_columns': ['CityID'], 'is_key_already_clustered': False, 'key_equals_clustered_key': False}.

E5

• Type: server_metrics
• Server metrics: sql_cpu_percent 40.00, ple_seconds 17,750, io_read_latency_ms 9.00, io_write_latency_ms 8.00, signal_wait_percent 60.00.