Sql Server Performance

SQL Server Performance

When to Use

• A query that used to be fast is now slow
• Reading execution plans to find bottlenecks
• Choosing between index strategies (clustered, covering, filtered)
• Diagnosing locking, blocking, or deadlocks
• Writing bulk INSERT/UPDATE/DELETE that won't bloat the transaction log
• Fixing parameter sniffing in stored procedures
• Deciding between temp tables and table variables
• Analyzing wait statistics to identify resource bottlenecks

When NOT to use: application schema design (table design, naming conventions, access control), backup/restore strategies, high availability configuration, or replication setup.

Diagnostic Flowchart

When a query is slow, follow this sequence — each step narrows the cause before moving to the next.

Step 1 — Get the actual execution plan

Copy code
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Then run the query with Ctrl+M (Include Actual Plan) in SSMS

Look for:

• Key Lookup — nonclustered index seek followed by a clustered lookup per row. At 1,000+ rows this dominates. Fix: add missing SELECT columns to the NCI INCLUDE list.
• Fat arrows (thick connectors) between operators — estimated rows ≪ actual rows. This is a cardinality estimation failure. Fix: update statistics, check for parameter sniffing.
• Yellow warning triangle on Hash Match — the memory grant was too small; the hash spilled to tempdb. Fix: update statistics so the grant is sized correctly.
• Table Scan on a large table — no clustered index or a non-SARGable predicate forced it. Fix: add a clustered index or rewrite the predicate.
• Index Spool — SQL Server built a temporary index on the fly. Fix: create the permanent index.

Step 2 — Check wait statistics

Copy code
-- Step 1: snapshot before the problem window
SELECT wait_type, wait_time_ms INTO #w FROM sys.dm_os_wait_stats;
-- ...wait 60 seconds or through the slow period...
-- Step 2: delta shows what consumed wait time during the window
SELECT c.wait_type, c.wait_time_ms - b.wait_time_ms AS delta_ms
FROM sys.dm_os_wait_stats c JOIN #w b ON b.wait_type = c.wait_type
WHERE c.wait_time_ms > b.wait_time_ms
  AND c.wait_type NOT IN ('SLEEP_TASK','LAZYWRITER_SLEEP','WAITFOR','CXCONSUMER')
ORDER BY delta_ms DESC;
DROP TABLE #w;

See wait-stats.md for the full methodology and DMV queries.

Step 3 — Evaluate indexes

Copy code
-- Top missing index recommendations (resets on restart — act promptly)
SELECT TOP 10
    d.statement                 AS [Table],
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    ROUND(s.avg_total_user_cost * s.avg_user_impact
          * (s.user_seeks + s.user_scans), 0) AS estimated_improvement
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s
    ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details d
    ON g.index_handle = d.index_handle
WHERE d.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

See index-strategy.md for clustered key selection, covering indexes, and over-indexing tradeoffs.

Step 4 — Check statistics freshness

Copy code
-- Compare histogram max to actual max (ascending key problem)
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate') WITH HISTOGRAM;
SELECT MAX(OrderDate) AS actual_max FROM dbo.Orders;
-- If actual_max > last RANGE_HI_KEY, statistics are stale for recent rows

-- Check modification counter
SELECT s.name, sp.last_updated, sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(5,2))
           AS pct_modified
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

See statistics-tuning.md for histogram interpretation and update strategies.

Step 5 — Diagnose parameter sniffing

Copy code
-- Multiple plans for the same proc = sniffing instability
SELECT qsq.query_id, COUNT(DISTINCT qsp.plan_id) AS plan_count,
       qsqt.query_sql_text
FROM sys.query_store_query       qsq
JOIN sys.query_store_plan        qsp  ON qsp.query_id      = qsq.query_id
JOIN sys.query_store_query_text  qsqt ON qsqt.query_text_id = qsq.query_text_id
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 3
ORDER BY plan_count DESC;

See the Parameter Sniffing section below and execution-plans.md.

Index Strategy

A table should have one well-chosen clustered index and targeted covering nonclustered indexes for secondary access patterns. Every index has a write penalty — keep OLTP tables at 3–5 nonclustered indexes total.

Clustered index selection — choose a key that is:

• Narrow — the clustered key is copied into every nonclustered index leaf row. An INT (4 bytes) vs UNIQUEIDENTIFIER (16 bytes) multiplies across all indexes and millions of rows.
• Ever-increasing — random inserts cause 50/50 page splits and severe fragmentation. Use INT IDENTITY, BIGINT IDENTITY, or NEWSEQUENTIALID().
• Unique — SQL Server silently appends a 4-byte uniquifier to duplicate values, bloating the index.
• Static — updating the clustered key physically moves the row and cascades updates to every nonclustered index.

Nonclustered index column ordering:

1. Equality predicates first (col = @val)
2. Range predicates next (col BETWEEN, col > @val)
3. ORDER BY columns last (sort is free if they follow equality columns in the index)

Covering indexes eliminate Key Lookups — add SELECT-only columns to INCLUDE:

Copy code
-- Before: every row causes a Key Lookup back to the clustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
    ON dbo.Orders (CustomerID);

-- After: covering index — no Key Lookup
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Covering
    ON dbo.Orders (CustomerID)
    INCLUDE (OrderDate, Status, TotalAmt);

Filtered indexes cover a subset of rows — lower maintenance cost, higher selectivity:

Copy code
-- Index only active orders — much smaller than a full-table NCI
CREATE NONCLUSTERED INDEX IX_Orders_Active
    ON dbo.Orders (CustomerID, OrderDate)
    INCLUDE (Status)
    WHERE Status = 1;

-- Unique constraint on nullable external key (NULLs excluded)
CREATE UNIQUE NONCLUSTERED INDEX UX_Orders_ExternalRef
    ON dbo.Orders (ExternalRef)
    WHERE ExternalRef IS NOT NULL;

For fragmentation thresholds, fill factor guidance, and missing index DMV usage, see index-strategy.md.

SARGability

A predicate is SARGable when the optimizer can use an index seek instead of scanning every row. The rule: never wrap the filtered column in a function or expression.

An implicit type conversion has the same effect as a function — it forces the optimizer to convert every row. Match parameter types exactly to column types. Look for PlanAffectingConvert warnings in execution plan XML.

Parameter Sniffing

SQL Server compiles a stored procedure plan on first execution using the actual parameter values passed at that moment. That plan is cached and reused — even for future calls with different parameters. This is beneficial 90% of the time; it hurts when the data distribution is skewed and the sniffed values are atypical.

Ranked strategies (use in order):

1. OPTIMIZE FOR UNKNOWN — stable plan using average distribution statistics. Preferred when many different parameter values are possible and no single value dominates:

Copy code
CREATE PROCEDURE dbo.GetOrders_ut
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, TotalAmt
    FROM   dbo.Orders
    WHERE  CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
END;

2. OPTION (RECOMPILE) — forces a fresh plan per execution, embedding the actual parameter value. The optimizer finds the optimal plan every time but pays CPU compilation cost on every call. Use for infrequent but highly variable procedures (nightly reports, admin tools):

Copy code
SELECT OrderID, OrderDate, TotalAmt
FROM   dbo.Orders
WHERE  CustomerID = @CustomerID
OPTION (RECOMPILE);   -- statement-level, not WITH RECOMPILE on the proc

3. Local variable copy — avoid. Assigning @CustomerID to a local variable defeats sniffing, but also defeats the benefit of sniffing on the common case. OPTIMIZE FOR UNKNOWN achieves the same statistical behavior with explicit, documented intent.

Copy code
-- BAD: defeats sniffing entirely, even when sniffing would help
DECLARE @CID INT = @CustomerID;
SELECT ... WHERE CustomerID = @CID;

On SQL Server 2022 (compatibility level 160) with Query Store enabled, Parameter-Sensitive Plan Optimization (PSPO) can automatically maintain multiple plan variants for skewed distributions. It is not a substitute for the above strategies on 2019 and earlier.

Temp Tables vs Table Variables

Decision rules:

• Use a temp table when rows exceed a few hundred, you need an index on the intermediate result, or you need accurate cardinality estimates for downstream joins.
• Use a table variable when the set is small and well-bounded (1–100 rows), you want to avoid recompile triggers, or you need the data to survive a ROLLBACK (e.g., audit log inserted before work begins).
• Use a CTE when the result is referenced exactly once — CTEs are not materialized; each reference re-executes the subquery. A CTE referenced twice scans the source twice.

SQL Server 2019 table variable deferred compilation (compatibility level 150+): the optimizer defers compilation of statements using table variables until after the table variable is populated, using the actual row count. This largely eliminates the "1 row" estimate problem on 2019+ environments. Temp tables still have the edge for column-level statistics and indexes on non-key columns.

Copy code
-- Verify deferred compilation is active (compat 150+)
SELECT name, value FROM sys.database_scoped_configurations
WHERE name = 'DEFERRED_COMPILATION_TV';

Common Mistakes

Reference Files

• execution-plans.md — reading plans, key operators, cardinality estimation, CE version map, Intelligent Query Processing (IQP), Query Store configuration and regression detection
• index-strategy.md — clustered key selection, covering indexes, filtered indexes, fragmentation, missing index DMVs
• wait-stats.md — top wait types, DMV queries, baseline methodology, resource correlation, TempDB configuration, max server memory
• locking-blocking.md — isolation levels, RCSI, lock escalation, deadlocks, NOLOCK dangers
• batch-operations.md — chunked DML, minimal logging, partition switching, log management
• statistics-tuning.md — histogram interpretation, auto-update thresholds, ascending key problem, incremental statistics, filtered statistics