# Sql Server Performance

> >

- **Type:** Skill
- **Install:** `agentstack add skill-stonegiantstudio-skills-sql-server-performance`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [stonegiantstudio](https://agentstack.voostack.com/s/stonegiantstudio)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** Apache-2.0
- **Upstream author:** [stonegiantstudio](https://github.com/stonegiantstudio)
- **Source:** https://github.com/stonegiantstudio/skills/tree/main/plugins/stone-giant/skills/sql-server-performance

## Install

```sh
agentstack add skill-stonegiantstudio-skills-sql-server-performance
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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)
2. **Add INCLUDE columns to eliminate Key Lookups** -- the single most common
   plan fix. Turns random I/O into a pure index seek. (Section 2)
3. **Use NOT EXISTS, never NOT IN** -- NOT IN silently returns zero rows when
   NULLs exist. NOT EXISTS is safe and usually faster. (Section 3)
4. **Inline TVFs, never multi-statement TVFs** -- MSTVFs get a fixed 100-row
   estimate regardless of actual data. Inline TVFs are fully optimized. (Section 5)
5. **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)

```sql
-- 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)

```sql
-- 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:**

```sql
-- 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.

```sql
-- 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:**

```sql
-- 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):

```sql
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
4. **Cover** columns in INCLUDE -- only in leaf pages, not in B-tree

```sql
-- 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.

```sql
-- 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:

```sql
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

```sql
-- 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

```sql
-- 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"

```sql
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.

```sql
-- 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.

```sql
-- 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.

```sql
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.

```sql
-- 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](https://github.com/stonegiantstudio)
- **Source:** [stonegiantstudio/skills](https://github.com/stonegiantstudio/skills)
- **License:** Apache-2.0

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-stonegiantstudio-skills-sql-server-performance
- Seller: https://agentstack.voostack.com/s/stonegiantstudio
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.