Query Analysisdbo.fn_GetStockItemRevenue

### 🧾 Executive Summary
The function experiences moderate CPU-bound execution (avg 56.6 ms, 867 logical reads) with 245 K weekly calls, where 88% of wait time is CPU; the immediate problem is a full scan on `Sales.Orders` caused by the non‑SARGable `YEAR(o.OrderDate) = @Year` predicate.  
Root cause is a **CRITICAL QUERY ISSUE** – the function’s filter prevents an index seek, forcing a Clustered Index Scan of 73 K rows every invocation.  
After rewriting the predicate to a date range, logical reads will drop by **>99 %** (target < 10 reads) and CPU time will decrease proportionally, eliminating the scan bottleneck.

### 🔍 Bottleneck Analysis
- **Primary Bottleneck:** CPU (dominant wait category CPU at 87.83 %; evidence `E1`)
- **Evidence:** `avg_cpu_ms` (55 ms) almost equals `avg_duration_ms` (57 ms); server‑side SQL CPU at 36 % and signal wait at 60 % indicate CPU pressure (`E5`)
- **Secondary Factors:** Minor memory grant pressure (12.16 %) from the Hash Match building a hash table; the scan also generates modest I/O (0.01 % Buffer IO) but is not the primary limiter

### 🎯 Root Cause Classification

**Classification:** CRITICAL_QUERY_ISSUE

**Rationale:** The `YEAR(o.OrderDate)` function on the left side of the filter violates SARGability, requiring a Clustered Index Scan of the entire `Sales.Orders` table followed by a post‑scan `Filter` operator. This dominates the plan cost (90 %) and causes every execution to read 74 K rows.

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

### 📋 Identified Issues (Prioritized)
| # | Issue | Priority | Risk | Impact |
|---|-------|----------|------|--------|
| 1 | Non‑SARGable `YEAR(o.OrderDate)` forces scan (867 reads/exec) | P1 | LOW | 99 % of subtree cost |
| 2 | Scalar function inhibits statement‑level optimisation and adds overhead | P2 | LOW | Minor plan‑quality penalty |
| 3 | Missing index on `Sales.OrderLines.StockItemID` (present but not optimal for this pattern) | P3 | LOW | Small residual scan after predicate fix |

### 💡 Optimization Recommendations

#### Recommendation #1: Rewrite the Date Predicate to a SARGable Range
**Priority:** LOW | **Risk:** Low

**Problem:**
`YEAR(o.OrderDate) = @Year` cannot use any index on `OrderDate`, so SQL Server performs a Clustered Index Scan of `Sales.Orders` (73 K rows) and then filters.

**Current Code:**

**Optimized Code:**

**Expected Improvement:**
- [BOTH] Logical Reads: 867 → < 10 (99 % reduction)
- [BOTH] Duration: 56 ms → ~5 ms (91 % reduction)

**Verification:**

**Rollback:**

---

#### Recommendation #2: Convert Scalar Function to Inline Table-Valued Function (or inline expression)
**Priority:** LOW | **Risk:** Low

**Problem:**
User‑defined scalar functions prevent the optimizer from cost‑based decisions (they appear as a black box). After fixing SARGability, residual overhead from the scalar wrapper may still limit plan quality.

**Current Code:** (scalar, as above)

**Optimized Code (inline TVF example):**

**Expected Improvement:**
- [BOTH] Enables more robust optimisation (possible parallel plan, better estimate)
- [BOTH] Eliminates scalar‑return call‑site overhead

**Verification:**

**Rollback:**

### 📊 Index Recommendation

⚠️ **Pre-check:** Are there SARGability issues?
- [DBA] [x] SARGability issues exist → Skip index recommendation until query is fixed

**Decision:** **NOT_NEEDED** until the predicate is rewritten.  
After the rewrite, the existing `PK_Sales_Orders` (clustered on `OrderID`) will be used for an index seek if `OrderDate` is included in a supporting index. Since the query currently filters on `StockItemID` on `OrderLines` and joins on `OrderID`, the main bottleneck will be resolved by the SARGable predicate, eliminating the scan. If performance is still suboptimal, consider an index on `Sales.Orders(OrderDate, OrderID)`.

### 🧪 Testing & Validation Plan

**Before Deployment:**

**After Deployment (wait 24 hours):**

**Success Criteria:**
- [ ] Duration reduced by > 80 % (to < 10 ms)
- [ ] Logical reads reduced by > 99 % (to < 10 reads)
- [ ] No new blocking introduced

### ⚠️ Risks & Considerations

| Risk | Likelihood | Impact | Mitigation |
|------|------------|--------|------------|
| Parameter sniffing on @StockItemID after rewrite (if very skewed) | Low | Minor plan shift | Add `OPTION (OPTIMIZE FOR UNKNOWN)` if needed |
| Year‑boundary leap year misinterpretation | None | Incorrect results | Test with leap years (2000, 2024) to verify `DATEFROMPARTS` boundary logic |
| Callers using `SELECT @var = dbo.fn_GetStockItemRevenue(...)`; scalar overhead remains | Medium | 5 ms per call | Replace with CROSS APPLY inline TVF for bulk use |

**Standard Edition Notes:** Not applicable (Developer Edition, same engine as Enterprise but with full feature set). All recommendations work on all editions.

**Deployment Window:** During low activity, after validating boundary‑date logic in pre‑production. No data changes required.

---

---
## 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.

| Dimension | Value |
| --- | --- |
| Primary Pathology | NONE |
| Query Design Class | QUERY_HEALTHY |
| Index Health Class | BALANCED |
| Stability Class | STABLE |
| Confidence Class | LOW |
| Overall Risk | LOW |

---
## Consistency Warnings (Auto-Check)
- 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 `1` (critical `0`, warning `1`, auto-fixed `1`, 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._