Query Analysisdbo.fn_GetCustomerOrderCount

Executive Summary (Deterministic Snapshot)

🧾 Executive Summary

1. Performance Status: The query inside the UDF runs in 1.33 ms average (P95 1.87 ms) with 358 logical reads – extremely fast per execution, but with 355 k invocations over 7 days the total CPU wait is 100%, indicating almost all time is CPU-bound.
2. Root Cause: The scalar UDF dbo.fn_GetCustomerOrderCount is called row‑by‑row in a SELECT list (RBAR), forcing repeated execution of the same logic and preventing SQL Server from using set‑based processing or parallelism.
3. Expected Outcome: Replacing the scalar UDF with an INLINE table‑valued function or a direct JOIN will eliminate the scalar‑UDF overhead, reduce total CPU usage by approximately 60‑80 % for the calling queries, and allow the optimizer to choose better join strategies.

🔍 Bottleneck Analysis

• Primary Bottleneck: CPU
• Evidence: Wait profile shows 100 % CPU (E1). Average duration 1.33 ms, average CPU 1.30 ms (E2) – virtually all time is spent on CPU. Server signal wait is 60 %, indicating CPU pressure (E5).
• Secondary Factors:
• Very high execution count (355 k in 7 days) amplifies any per‑invocation overhead.
• Scalar‑UDF boundary prevents inlining, forcing a separate plan compilation and a function call per row.

🎯 Root Cause Classification

Classification: POOR_QUERY_DESIGN Rationale: Although the internal query is SARGable and well‑optimised, the UDF is a scalar function called for every row of the outer query – a classic RBAR anti‑pattern. This design multiplies CPU cost and blocks set‑based optimisation.

Decision:

• [x] Query is SARGable → Can proceed with optimisation (on the UDF itself)
• [ ] Query has SARGability issues → Must fix query first, defer index discussion

Note: The query inside the UDF is already SARGable; the issue is the scalar UDF pattern, not an index problem.

📋 Identified Issues (Prioritized)

💡 Optimization Recommendations

Recommendation #1: Convert Scalar UDF to Inline Table-Valued Function

Priority: LOW | Risk: Low Problem: The scalar UDF executes once per row, incurring context‑switch, plan‑cache bloat, and preventing parallel processing.

Current Code (UDF definition):

CREATE FUNCTION dbo.fn_GetCustomerOrderCount
(
    @CustomerID     INT,
    @StartDate      DATE,
    @EndDate        DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Count INT;
    SELECT @Count = COUNT(*)
    FROM Sales.Orders
    WHERE CustomerID    = @CustomerID
      AND OrderDate    >= @StartDate
      AND OrderDate    <= @EndDate;
    RETURN ISNULL(@Count, 0);
END;

Optimized Code (Inline TVF):

CREATE FUNCTION dbo.itvf_GetCustomerOrderCount
(
    @CustomerID     INT,
    @StartDate      DATE,
    @EndDate        DATE
)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderCount = ISNULL(COUNT(*), 0)
    FROM Sales.Orders
    WHERE CustomerID    = @CustomerID
      AND OrderDate    >= @StartDate
      AND OrderDate    <= @EndDate
);

Callers must change to CROSS APPLY or OUTER APPLY the TVF instead of scalar reference.

Expected Improvement:

• [BOTH] CPU per outer query: 1.30 ms → ~0.2 ms (70‑80 % reduction) for the same count logic when the TVF is inlined or joined.
• [BOTH] Overall CPU load: reduced proportionally with the number of rows processed by the outer query.

Verification:

-- Compare query plans for a typical caller, e.g.:
SELECT c.CustomerID,
       oc.OrderCount
FROM Sales.Customers c
CROSS APPLY dbo.itvf_GetCustomerOrderCount(c.CustomerID, '2023-01-01', '2023-12-31') oc;
-- Target: plan shows the TVF logic inlined; no 'Compute Scalar' from scalar UDF.
-- If the new TVF causes issues (unlikely), revert callers to the scalar UDF:
SELECT c.CustomerID,
       dbo.fn_GetCustomerOrderCount(c.CustomerID, '2023-01-01', '2023-12-31') AS OrderCount
FROM Sales.Customers c;

Recommendation #2: Remove Unnecessary ISNULL Variable

Priority: LOW | Risk: Low Problem: The UDF declares a local variable, assigns it, then applies ISNULL – while COUNT(*) never returns NULL, so the extra step is waste.

Current Code:

DECLARE @Count INT;
SELECT @Count = COUNT(*)
...
RETURN ISNULL(@Count, 0);

Optimized Code (if you must keep scalar UDF):

RETURN ( SELECT COUNT(*) FROM Sales.Orders WHERE ... );

Expected Improvement: Minimal (<1 ms) per call. Over 355 k calls, saves a few seconds of CPU over 7 days.

Verification:

-- Check CPU time for a sample execution with and without variable.

📊 Index Recommendation

⚠ Pre‑check: Are there SARGability issues?

• [DBA] [x] No SARGability issues → Proceed with index recommendation? No index needed. The current execution plan uses an index seek on FK_Sales_Orders_CustomerID and a clustered index seek – both optimal. No missing index suggestions from DMV or plan. The compute operator stems from the scalar UDF itself, not from data access. Decision: NOT_NEEDED.

🧪 Testing & Validation Plan

Before Deployment:

-- Capture baseline CPU for a representative caller, e.g.:
SET STATISTICS TIME, IO ON;
SELECT c.CustomerID, dbo.fn_GetCustomerOrderCount(c.CustomerID, '2023-01-01', '2023-12-31')
FROM Sales.Customers c;
-- Record CPU time and total duration.

After Deployment (wait 24 hours):

-- Use the TVF version and verify improvement:
SET STATISTICS TIME, IO ON;
SELECT c.CustomerID, oc.OrderCount
FROM Sales.Customers c
CROSS APPLY dbo.itvf_GetCustomerOrderCount(c.CustomerID, '2023-01-01', '2023-12-31') oc;
-- Target: CPU reduced by ≥60 %, no new blocking.

Success Criteria:

• [ ] CPU time for the calling query reduced by ≥60 %.
• [ ] Plan no longer shows User Defined Function or scalar UDF operator.
• [ ] No increase in logical reads; total reads may drop slightly.
• [ ] No regression in other queries (check sys.dm_exec_query_stats for new plans).

⚠️ Risks & Considerations

Deployment Window: Can be deployed during normal maintenance; no blocking or protracted rewrite required.

Evidence used: E1 (CPU wait dominance), E2 (metrics), E5 (server CPU pressure).

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 rollback line(s) for non-schema recommendations. (Emit Rollback only for schema/index/config changes that actually need reversal steps.)
• Auto-fixed: Aligned 2 recommendation Priority field(s) to the issue-table order. (Recommendation priorities should inherit the priority of the issue they resolve.)

📊 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 2 (critical 0, warning 2, auto-fixed 2, 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, CXPACKET, CXCONSUMER.

Evidence Appendix

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

E1

• Type: wait_profile
• Details: CPU 100.00%, total_wait_ms 10,725.
• Representative wait types: SOS_SCHEDULER_YIELD, CXPACKET, CXCONSUMER.

E2

• Type: metrics
• Details: avg_duration_ms 1.33, p95_ms 1.87, avg_cpu_ms 1.30, avg_logical_reads 358, executions 355,293, plan_count 1.

E5

• Type: server_metrics
• Server metrics: sql_cpu_percent 26.00, ple_seconds 19,145, io_read_latency_ms 9.00, io_write_latency_ms 7.00, signal_wait_percent 60.00.