Sql Server Performance

SQL Server Query Performance

You are a SQL Server performance specialist. Your job is to write the fastest possible queries, stored procedures, table-valued functions, and views. Every pattern in this skill is derived from expert practitioners: Brent Ozar, Erik Darling, Paul White, Kendra Little, Itzik Ben-Gan, Aaron Bertrand, and Microsoft documentation.

This skill covers performance. For schema design, see sql-server. For safety patterns, see sql-server-safety. For multi-tenant isolation, see multi-tenant-safety.

---

Quick Wins: The 5 Highest-Leverage Patterns

1. Make predicates SARGable -- rewrite WHERE YEAR(col) = 2024 to range

predicates. Eliminates full scans instantly. (Section 1)

1. Add INCLUDE columns to eliminate Key Lookups -- the single most common

plan fix. Turns random I/O into a pure index seek. (Section 2)

1. Use NOT EXISTS, never NOT IN -- NOT IN silently returns zero rows when

NULLs exist. NOT EXISTS is safe and usually faster. (Section 3)

1. Inline TVFs, never multi-statement TVFs -- MSTVFs get a fixed 100-row

estimate regardless of actual data. Inline TVFs are fully optimized. (Section 5)

1. Enable RCSI -- eliminates 60-90% of deadlocks and all reader-writer

blocking with a single ALTER DATABASE. (Section 10)

Validate every optimization. Use the benchmarking protocol in Section 0 to prove your change is faster with logical reads, not assumptions.

---

0. How to Benchmark: Prove It's Faster

Every optimization in this skill should be validated, not assumed. When comparing two query approaches, follow this protocol.

Step 1: Capture baseline (Approach A)

-- Clear buffer pool so both approaches start cold (dev/test ONLY, never prod)
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

-- Clear the plan cache for a SPECIFIC query (safe, targeted)
-- Find the plan handle first:
SELECT plan_handle FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%YourQuerySignature%';
-- Then evict just that plan:
DBCC FREEPROCCACHE(0x06000700...);  -- specific plan handle

-- Turn on diagnostics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run Approach A
-- Copy the Messages tab output: logical reads, CPU time, elapsed time

Step 2: Capture alternative (Approach B)

-- Clear again for a fair cold-cache comparison
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

-- Run Approach B
-- Copy the Messages tab output

Step 3: Compare the right metric

| Metric | What it tells you | Stability | |---|---|---| | Logical reads | Pages read from buffer pool. THE primary metric. | Highly stable -- same value every run regardless of server load | | CPU time | Processor time consumed. Good secondary metric. | Stable -- varies , >=, , NOT IN, NOT LIKE, LIKE '%suffix', LIKE '%middle%'`

Anti-patterns and rewrites

Functions on columns:

-- NON-SARGABLE
WHERE YEAR(OrderDate) = 2024
WHERE CONVERT(DATE, CreatedAt) = '2024-01-15'
WHERE ISNULL(MiddleName, '') = ''

-- SARGABLE
WHERE OrderDate >= '2024-01-01' AND OrderDate = '2024-01-15' AND CreatedAt  100

-- SARGABLE: move math to the other side
WHERE Price > 100 / 1.1

Implicit type conversions (the #1 hidden performance killer):

When comparing a VARCHAR column to an NVARCHAR parameter, SQL Server converts EVERY row's column value to NVARCHAR, killing index seeks. .NET, Entity Framework, and Prisma send string params as NVARCHAR by default.

-- KILLS INDEX SEEK: column-side conversion on every row
WHERE VarcharColumn = N'SomeValue'

-- PRESERVES SEEK: match the column type
WHERE VarcharColumn = 'SomeValue'

Data type precedence rule: The lower-precedence type is converted to the higher. NVARCHAR > VARCHAR, so the VARCHAR column gets converted (millions of conversions), not the parameter.

LIKE with leading wildcard — workaround:

-- NON-SARGABLE
WHERE Email LIKE '%@gmail.com'

-- Workaround: reversed computed column + index
ALTER TABLE Users ADD EmailReversed AS REVERSE(Email);
CREATE INDEX IX_Users_EmailReversed ON Users(EmailReversed);
-- Then:
WHERE EmailReversed LIKE REVERSE('@gmail.com') + '%'

ISNULL vs COALESCE SARGability:

ISNULL can preserve SARGability in some cases because the optimizer knows it returns the column's data type. COALESCE wraps in CASE, which may prevent seeks. However, neither should be in a WHERE clause if avoidable -- rewrite to explicit IS NULL OR col = ... for guaranteed SARGability.

Computed column escape hatch (for ORM-generated queries you cannot rewrite):

ALTER TABLE Orders ADD OrderYear AS YEAR(OrderDate) PERSISTED;
CREATE INDEX IX_Orders_OrderYear ON Orders(OrderYear);
-- Now WHERE OrderYear = 2024 is SARGable

---

2. Index Strategy

Clustered index: the NUSE rule

The clustered key should be Narrow, Unique, Static, Ever-increasing:

• Narrow: The key is stored in every nonclustered index. INT (4 bytes) or

BIGINT (8 bytes) is ideal. NVARCHAR(256) bloats everything.

• Unique: Without explicit uniqueness, SQL Server adds a hidden 4-byte

uniquifier silently.

• Static: Changing a clustered key value forces delete + re-insert across

every nonclustered index.

• Ever-increasing: IDENTITY, SEQUENCE, or NEWSEQUENTIALID(). Random GUIDs

(NEWID()) cause massive page splits and fragmentation.

The canonical choice: INT/BIGINT IDENTITY.

Composite index column ordering: the ESR rule

Order columns as Equality, Sort, Range (Erik Darling):

1. Equality columns first (WHERE col = @value) -- enable index seek
2. Sort columns second (ORDER BY col) -- eliminate Sort operator
3. Range columns third (WHERE col > @value, BETWEEN, LIKE) -- once

a range is hit, the index cannot seek further right

1. Cover columns in INCLUDE -- only in leaf pages, not in B-tree

-- Query:
SELECT OrderId, Amount FROM Orders
WHERE CustomerId = @CustId AND OrderDate >= @StartDate
ORDER BY OrderDate;

-- Optimal index (ESR):
CREATE NONCLUSTERED INDEX IX_Orders_Cust_Date
ON Orders (CustomerId, OrderDate)  -- equality, then sort+range
INCLUDE (Amount);                  -- cover

Covering indexes eliminate Key Lookups

A Key Lookup means the nonclustered index found rows but must fetch additional columns from the clustered index. Each lookup is a random I/O. Fix: add missing columns to INCLUDE.

-- Before: seek + Key Lookup per row
CREATE INDEX IX_Posts_UserId ON Posts(UserId);

-- After: seek only, no lookup
CREATE INDEX IX_Posts_UserId ON Posts(UserId) INCLUDE (Title, Status, CreatedAt);

Filtered indexes

Dramatically smaller and faster for common subsets:

CREATE INDEX IX_Orders_Pending ON Orders(CreatedAt) WHERE Status = 'Pending';
CREATE INDEX IX_Users_Active ON Users(Email) WHERE IsActive = 1;

Index anti-patterns

| Anti-pattern | Problem | |---|---| | Over-indexing (15+ NCIs) | Every INSERT/UPDATE/DELETE maintains all indexes | | Duplicate/redundant indexes | Index on (A) is redundant when (A, B) exists | | Unused indexes (0 seeks, 0 scans) | Pure write overhead, wastes buffer pool | | NEWID() as clustered key | Random page splits, massive fragmentation | | Blindly creating missing index suggestions | Per-query, ignores write cost and overlap |

Never blindly create missing index suggestions

The DMV suggestions are per-query, capped at 600 entries, don't consider existing indexes, write overhead, or overlap. Always consolidate and apply the ESR rule yourself.

---

3. Query Patterns: Fastest Implementations

Semi-joins: EXISTS vs IN vs JOIN

All three typically produce the same plan in modern SQL Server. Prefer EXISTS -- clearest intent, handles NULLs safely, short-circuits.

Anti-joins: NOT EXISTS is safest and fastest

-- BEST: safe with NULLs, clear intent
SELECT o.* FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Returns r WHERE r.OrderId = o.OrderId);

-- DANGEROUS: if Returns.OrderId contains ANY NULL, returns ZERO rows
SELECT o.* FROM Orders o
WHERE o.OrderId NOT IN (SELECT OrderId FROM Returns);

The NOT IN NULL trap: value NOT IN (1, 2, NULL) evaluates to UNKNOWN for every value. UNKNOWN in WHERE filters out the row. Result: zero rows.

Pagination: keyset beats OFFSET/FETCH

-- OFFSET/FETCH: O(n) -- scans and discards skipped rows. Gets SLOWER per page.
SELECT Id, Title, CreatedAt FROM Posts
ORDER BY CreatedAt DESC
OFFSET 10000 ROWS FETCH NEXT 25 ROWS ONLY;

-- KEYSET/SEEK: O(1) -- constant time regardless of page depth
SELECT TOP 25 Id, Title, CreatedAt FROM Posts
WHERE (CreatedAt = @StartDate;

UNION ALL over UNION

UNION performs a DISTINCT sort; UNION ALL does not. Always use UNION ALL when duplicates are acceptable or impossible.

CROSS APPLY for "top N per group"

SELECT c.CustomerId, c.Name, o.OrderDate, o.Amount
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderDate, Amount FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) o;

CTEs are not materialized -- materialize shared CTEs yourself

SQL Server expands CTEs inline at every reference point. A CTE is syntactic sugar, not a plan boundary. When a CTE feeds into a UNION ALL with N branches, the optimizer expands the entire CTE chain N times independently.

-- BAD: EmployeeSummary CTE reads vwTimeEntries.
-- 6 UNION ALL branches each re-expand it = 6 independent copies of a
-- 7-table join tree. Optimizer hits TimeOut, picks a naive plan.
;WITH EmployeeSummary AS (SELECT ... FROM dbo.vwTimeEntries ...)
SELECT ... FROM EmployeeSummary WHERE EarningType = 'REG'
UNION ALL
SELECT ... FROM EmployeeSummary WHERE EarningType = 'OT'
UNION ALL
SELECT ... FROM EmployeeSummary WHERE EarningType = 'PTO'
-- ...3 more branches, each re-reading the entire view

-- GOOD: Materialize the expensive computation ONCE into a temp table.
-- CTEs over the temp table are cheap -- each branch reads a small
-- physical table with statistics, not a re-expanded join tree.
SELECT e.EmployeeId, te.HoursWorked, ph.PhaseName
INTO #TE
FROM @Emps ee
JOIN dbo.TimeEntryWeeks tew ON tew.EmployeeId = ee.EmployeeId
JOIN dbo.TimeEntries te ON te.TimeEntryWeekId = tew.TimeEntryWeekId
JOIN dbo.Phases ph ON tew.PhaseId = ph.PhaseId
WHERE te.DateWorked BETWEEN @startDate AND @endDate;

;WITH EmployeeSummary AS (SELECT ... FROM #TE ...)  -- reads ~200 rows
SELECT ... FROM EmployeeSummary ...
UNION ALL ...  -- each branch reads the small temp table, not 7-table views

Rule: If a CTE will be referenced more than once (multiple UNION ALL branches, multiple downstream CTEs, or a self-join), materialize it into a temp table first. The temp table's value is as a plan boundary -- it gives the optimizer statistics and prevents CTE expansion. The cost of writing to TempDB is negligible compared to re-executing the source N times.

CROSS APPLY VALUES to avoid UNION ALL branches

When generating multiple derived rows per source row (e.g., unpivoting fixed earning types), use CROSS APPLY VALUES instead of UNION ALL. Each UNION ALL branch is an independent query that expands its own CTE references. VALUES generates multiple rows in a single pass.

-- BAD: 3 UNION ALL branches, each re-expands the CTE
SELECT EmployeeId, 'REG' AS Type, RegularHours AS Hours FROM Summary
UNION ALL
SELECT EmployeeId, 'OT',  OvertimeHours FROM Summary
UNION ALL
SELECT EmployeeId, 'PTO', PTOHours FROM Summary

-- GOOD: single pass, no CTE re-expansion
SELECT s.EmployeeId, v.EarningType, v.Hours
FROM Summary s
CROSS APPLY (VALUES
    ('REG', s.RegularHours),
    ('OT',  s.OvertimeHours),
    ('PTO', s.PTOHours)
) v(EarningType, Hours)
WHERE v.Hours > 0;  -- bonus: can filter inline

The correct upsert pattern (avoid MERGE)

MERGE has documented bugs with indexed views, filtered indexes, temporal tables, and OUTPUT clause. Race conditions without SERIALIZABLE/HOLDLOCK.

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.Settings WITH (UPDLOCK, SERIALIZABLE)
SET Value = @Value
WHERE TenantId = @TenantId AND [Key] = @Key;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.Settings (TenantId, [Key], Value)
    VALUES (@TenantId, @Key, @Value);
END

COMMIT TRANSACTION;

UPDLOCK prevents conversion deadlocks. SERIALIZABLE prevents phantom inserts.

---

4. Stored Procedures

Temp tables vs table variables

| Characteristic | #Temp Tables | @Table Variables | |---|---|---| | Statistics | Yes (auto-created) | No (pre-2019) | | Cardinality estimate | Based on statistics | Fixed 1 row (pre-2019) | | Parallel plans | Yes | No (modifications block parallelism) | | Indexes | Any type | Primary key / unique only |

Rule: Use #temp tables when >~100 rows or complex joins. Use table variables for small, fixed-size sets or when rows must survive ROLLBACK.

SQL Server 2019+ (compat 150): Table Variable Deferred Compilation uses actual row counts instead of the fixed 1-row estimate. No code change needed.

Small driving set pattern (table variable with PRIMARY KEY)

When a proc identifies a small set of entities (employees, orders, tenants) and then joins them to large tables, use a table variable with a PRIMARY KEY as the driving table. The PK gives the optimizer cardinality information and a unique clustered index, which consistently produces nested loop seeks instead of hash joins on large scans.

-- Step 1: Small driving set with PRIMARY KEY
DECLARE @Emps TABLE (EmployeeId INT PRIMARY KEY);
INSERT INTO @Emps
SELECT et.EmployeeId FROM dbo.EmployeeTenure et
WHERE et.TenantId = @tenantId AND et.EmploymentType = 'Exempt';
-- ~62 rows

-- Step 2: Drive joins FROM the small set
SELECT tew.EmployeeId, te.HoursWorked, ph.PhaseName
INTO #TE
FROM @Emps ee                              -- 62 rows, driving table
JOIN dbo.TimeEntryWeeks tew
  ON tew.EmployeeId = ee.EmployeeId        -- index seek per employee
JOIN dbo.TimeEntries te
  ON te.TimeEntryWeekId = tew.TimeEntryWeekId  -- index seek per week
JOIN dbo.Phases ph
  ON tew.PhaseId = ph.PhaseId;             -- index seek per phase

Without the driving set, the optimizer may choose a date-driven scan (4,500 rows across all employees) and hash join to filter down. With the driving set, it does 62 index seeks -- dramatically fewer reads.

Why table variable, not temp table, for the driving set? For small sets ( 0 ORDER BY sp.modification_counter DESC;

### UPDATE STATISTICS: the real reason rebuilds "fix" things

`ALTER INDEX REBUILD` updates statistics with FULLSCAN. Often the statistics
refresh -- not the defrag -- is what fixes performance. You can get the same
benefit from `UPDATE STATISTICS ... WITH FULLSCAN` without the rebuild cost.

---

## 8. Plan Cache

### sp_executesql vs EXEC(@sql)

| Feature | sp_executesql | EXEC(@sql) |
|---|---|---|
| Plan reuse | High (parameterized) | Low (per unique string) |
| SQL injection | Protected (with params) | Vulnerable |
| Plan cache bloat | Minimal | Severe |

**Always** use sp_executesql with parameters for dynamic SQL.

### Plan cache bloat detection

```sql
SELECT COUNT(*) AS single_use_plans,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS wasted_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'Adhoc';

OPTIMIZE FOR AD HOC WORKLOADS

Stores only a plan stub on first execution, full plan on second. Reduces memory from single-use plans but masks the real problem (non-parameterized queries). Not a blanket best practice -- fix parameterization instead.

---

9. Execution Plans: What to Look For

Read plans right-to-left, top-to-bottom. Always use ACTUAL plans.

Red flags

| Signal | Meaning | Fix | |---|---|---| | Key Lookup / RID Lookup | NCI found rows but needs columns from CI | Add INCLUDE columns to make covering index | | Yellow triangle (!) | Spill, implicit conversion, missing stats | Investigate the specific warni

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: stonegiantstudio
• Source: stonegiantstudio/skills
• License: Apache-2.0

Install and usage instructions live in the source repository linked above.