SQL Server Performance Tuning & Indexing

Reading execution plans: estimated vs actual

SQL Server gives you two flavors of execution plan, and conflating them is the most common mistake in query tuning. The estimated plan (SET SHOWPLAN_XML ON, or Ctrl+L in SSMS) is produced by the optimizer without running the query. It relies entirely on statistics — histograms and density information on the columns involved — to guess how many rows will flow through each operator. The actual plan (Ctrl+M, then execute) runs the query for real and annotates every operator with the actual row count alongside the estimate.

The single most useful habit in plan analysis is comparing estimated rows to actual rows at each operator. When they're close, the optimizer had good information and chose a reasonable plan. When they diverge by an order of magnitude or more, the optimizer built a plan around a wrong assumption — usually stale statistics, an out-of-date histogram after a large data load, or parameter sniffing where a cached plan compiled for one parameter value is being reused for a very differently-shaped parameter. A nested loops join that made sense for an estimated 12 rows becomes catastrophic when the actual row count is 1.2 million, because nested loops pays its cost once per outer row.

Pair the plan with SET STATISTICS IO, TIME ON. IO gives you logical reads per table, which is often a better tuning signal than the plan's cost percentages — those percentages are relative estimates within a single plan, not absolute measures of work, and they can be misleading when comparing two different plans for the same query. Read plans right-to-left and top-to-bottom: data flows from the rightmost leaf operators (scans and seeks) up through joins and aggregates toward the SELECT at the top left. Arrow thickness is proportional to row count, so a fat arrow feeding a thin one downstream is a visual cue that a lot of data is being filtered late — often a sign that a predicate could be pushed down with a better index.

Spotting expensive operators

A handful of operators account for most real-world performance problems:

Table Scan / Clustered Index Scan. The engine reads every row (or every row of the clustered index) because no usable index exists for the predicate, or the optimizer decided a scan was cheaper than seeking a large fraction of the table. A scan on a 500-row lookup table is irrelevant; a scan on a 200-million-row fact table driven by a WHERE clause on an unindexed column is usually the root cause of a slow report.

Key Lookup (or RID Lookup on a heap). This appears when a nonclustered index seek finds the right rows but the index doesn't contain every column the query needs, forcing a second read into the clustered index (or heap) per matching row to fetch the rest. A Key Lookup next to a seek that matches thousands of rows is a strong signal you need a covering index — the lookup cost scales linearly with rows returned, and at high row counts it can dominate the entire plan's cost even though the seek itself looks cheap.

Sort. Expensive when the input is large and there's no index to deliver rows in the required order already. A Sort feeding a Top or an ORDER BY is often eliminable by building an index whose key order matches the ORDER BY, letting the engine read rows already sorted instead of buffering and sorting them in memory (or spilling to tempdb if memory grants are insufficient — check for a Sort or Hash warning icon in the plan, which indicates exactly that spill).

Hash Match vs Nested Loops. Hash Match is the optimizer's choice for joining two large, unsorted inputs — it builds an in-memory hash table from the smaller input and probes it with the larger one. Nested Loops is efficient when the outer input is small and the inner side can be seeked cheaply per outer row, but it degrades badly if the outer row estimate is wrong and the actual outer set is large, since every outer row triggers a separate seek (or scan) on the inner side.

Clustered vs nonclustered indexes: the mechanics

A clustered index determines the physical storage order of the table's data rows. The leaf level of a clustered index *is* the table — there's no separate structure holding the row data elsewhere. Because of this, a table can have at most one clustered index; if you never create one, the table is a heap, and rows are located by a physical Row ID (RID) instead of a key. Clustering key choice matters enormously: a narrow, unique, ever-increasing key (a surrogate IDENTITY or sequential bigint) keeps new rows appended at the end of the physical order, avoiding page splits. A wide or non-sequential clustering key (a GUID generated with NEWID(), for instance) causes random insert positions, fragmenting the table and bloating every nonclustered index, since every nonclustered index stores that clustering key as its row locator.

A nonclustered index is a separate B-tree with its own leaf level, holding a copy of the indexed column(s) plus a pointer back to the actual data row — the clustering key if the table has a clustered index, or a physical RID if it's a heap. A table can have many nonclustered indexes (up to 999), each providing a different sort order and seek path, but each is a fully separate structure that must be maintained on every INSERT, UPDATE, and DELETE that touches its key columns. This is the crux of the write-overhead tradeoff: every additional nonclustered index speeds up some read pattern but slows down every write that modifies its columns, because the engine has to update that index's B-tree in addition to the clustered index.

When a nonclustered index's key columns satisfy the WHERE/JOIN predicate but the SELECT list needs other columns, the engine performs a Key Lookup back into the clustered index per row — which is exactly the expensive operator described above. This is the problem covering indexes solve.

Covering indexes with INCLUDE columns

A covering index contains every column the query needs — in the key, in an INCLUDE list, or both — so the engine can satisfy the entire query from the nonclustered index's leaf level without ever touching the clustered index. INCLUDE columns are stored only at the leaf level of the index, not in the upper B-tree levels used for seeking, which keeps the index narrower and faster to traverse than making those columns part of the key. INCLUDE columns also aren't subject to the same restrictions as key columns — they can be large types that couldn't participate in a key at all, and they don't count against the 900-byte key size limit.

-- Query being tuned:
-- SELECT OrderId, OrderDate, TotalDue
-- FROM Sales.SalesOrderHeader
-- WHERE CustomerId = @CustomerId AND Status = 'Shipped';

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerId_Status
ON Sales.SalesOrderHeader (CustomerId, Status)
INCLUDE (OrderDate, TotalDue);

Here CustomerId and Status are key columns because they're used for seeking and equality filtering; OrderDate and TotalDue are INCLUDE columns because they're only ever selected, never filtered or sorted on. After creating this index, the plan for the query above should show an Index Seek with no accompanying Key Lookup. Column order in the key matters too: put the equality predicate columns first (in whatever order matches how the query filters), then range predicate or sort columns last, since the index is only seekable on a left-to-right prefix of its key columns.

Missing-index DMVs and their limits

SQL Server tracks, per database, index shapes the optimizer wished it had while compiling plans, exposed through sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats. A standard query joins all three, ranked by an estimated improvement measure:

SELECT
    d.statement AS table_name,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    gs.user_seeks,
    gs.avg_total_user_cost,
    gs.avg_user_impact,
    gs.user_seeks * gs.avg_total_user_cost * (gs.avg_user_impact / 100.0)
        AS estimated_improvement
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g
    ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats gs
    ON g.index_group_handle = gs.group_handle
ORDER BY estimated_improvement DESC;

This is a genuinely useful starting point, but the output needs human judgment before anything gets deployed. First, each row describes a single missing index in isolation — the DMV has no awareness of the other indexes already on that table, so it will happily suggest an index that's a near-duplicate of one that exists, or several missing-index rows from different queries that could be merged into one broader index instead of created separately. Second, it only ever suggests equality_columns, then inequality_columns, then included_columns in that fixed order — it doesn't experiment with column ordering, so its suggested key order isn't necessarily optimal. Third, and most important, it has no concept of write cost: it's purely a read-side heuristic based on plans the optimizer compiled, and it will suggest an index on a heavily-written OLTP table just as readily as on a read-mostly reporting table, with no discount for the INSERT/UPDATE/DELETE overhead that index would add. Fourth, this data is entirely in-memory and resets on every service restart, failover, or database detach, so user_seeks and avg_user_impact only reflect activity since the last restart — treat it as a hint to investigate, never as a list to apply verbatim with a script.

Using Query Store to find regressions

Query Store, enabled per-database with ALTER DATABASE ... SET QUERY_STORE = ON, persists query text, the distinct execution plans compiled for each query over time, and runtime statistics (duration, CPU, logical reads, memory grant) bucketed into configurable time intervals — all written to disk, so it survives restarts, unlike the plan cache which is wiped on every restart or memory pressure eviction. This makes it the right tool for answering "did this deployment or statistics update make things worse?" because you can directly compare a query's runtime stats before and after a known point in time.

The built-in SSMS reports under Query Store — Top Resource Consuming Queries, and specifically Regressed Queries — let you pick a baseline interval and a recent interval and see which queries got slower between them, along with every plan SQL Server has compiled for that query in the window. It's common to find a query with two or three distinct plan_id values, where the regression lines up exactly with a plan change — often triggered by an automatic statistics update after enough rows changed, or by a new plan compiled after an index was dropped or altered during deployment. You can query this directly:

SELECT
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.avg_duration,
    rs.avg_logical_io_reads,
    rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE q.query_id = @QueryId
ORDER BY rs.last_execution_time DESC;

Seeing multiple plan_id rows for one query_id, with runtime jumping between them, is the clearest possible evidence that the regression is a plan choice problem rather than a data volume problem.

Forcing a plan as a stopgap

Once you've identified that an earlier plan performed better than the one currently being chosen, sys.sp_query_store_force_plan lets you pin the query to that known-good plan_id immediately, buying time to investigate the real cause without leaving production degraded:

EXEC sys.sp_query_store_force_plan
    @query_id = 4821,
    @plan_id  = 6103;

This is deliberately framed as a stopgap, not a fix. Forcing a plan tells the optimizer to skip its normal compilation and reuse that specific plan shape for future executions of that query, which sidesteps whatever made it choose the worse plan — but it doesn't address the underlying cause, whether that's stale statistics, a parameter-sniffing-sensitive predicate, or a missing index that would make the whole question moot. A forced plan can also become invalid over time as data distribution shifts, or fail to force at all if the referenced objects change (an index used by that plan gets dropped, for instance), so it needs to be revisited, not left in place indefinitely. Remove it with sys.sp_query_store_unforce_plan once the root cause is fixed — typically by updating statistics, rewriting the query to avoid the sniffing-sensitive pattern, or adding the covering index that made the better plan possible in the first place.