SQL Server indexes and execution plans, explained

Most slow SQL Server queries are slow for one of three reasons: there is no index the query can seek on, there is an index but the query is written so it cannot be used, or the index finds the rows quickly and then fetches every column from the table one row at a time. The execution plan tells you which, in a few seconds, if you know where to look. This guide covers what SQL Server indexes actually are, how clustered and nonclustered indexes differ, what included columns are for, and how to read a plan from right to left until the expensive operator stops hiding.

The examples use SQL Server 2022, and everything here applies to the Express edition - every index type, including filtered and columnstore indexes, has been available in all editions since SQL Server 2016 Service Pack 1. What Express lacks are the online rebuild options, which matter less than people think on a database capped at 10 GB.

How SQL Server stores a table#

A SQL Server index is a B-tree: a root page, some intermediate levels, and leaf pages at the bottom, each page 8 KB. Searching for a key walks from the root down, touching one page per level. A table of ten million rows usually has an index three or four levels deep, so finding one row costs three or four page reads instead of reading the whole table.

There are two kinds of table:

• A clustered table has a clustered index, and its leaf level is the table. The rows are stored in clustered-key order, inside the index. There can be only one clustered index per table, because the rows can only be physically ordered one way.
• A heap has no clustered index. Rows sit wherever there was space, and each one is addressed by a physical row identifier (file, page, slot).

Declaring a PRIMARY KEY creates a clustered index on it by default, unless the table already has a clustered index or you write PRIMARY KEY NONCLUSTERED. That default is why almost every table in an application database is clustered on an int or bigint IDENTITY column, and for most tables it is a good choice: the key is narrow, unique, never changes and always increases, so new rows go on the last page instead of splitting pages in the middle.

Heaps are a reasonable choice for staging tables you load and then read once. For anything that is updated, they have a long-standing problem: when an updated row no longer fits on its page, SQL Server moves it and leaves a forwarding pointer behind, and reads then follow those pointers. Give application tables a clustered index.

Nonclustered indexes and included columns#

A nonclustered index is a separate B-tree holding its key columns plus a pointer back to the row. On a clustered table, that pointer is the clustered key; on a heap, it is the row identifier. A table can have up to 999 nonclustered indexes, which is roughly 990 more than any table should have.

CREATE TABLE dbo.Orders (    OrderId     int IDENTITY(1,1) NOT NULL PRIMARY KEY,   -- clustered by default    CustomerId  int           NOT NULL,    Status      tinyint       NOT NULL,    CreatedAt   datetime2(0)  NOT NULL,    Total       decimal(12,2) NOT NULL,    Notes       nvarchar(max) NULL);CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_CreatedAt    ON dbo.Orders (CustomerId, CreatedAt)    INCLUDE (Status, Total);

The key columns, CustomerId and CreatedAt, are sorted and searchable. The INCLUDE columns are stored only at the leaf level: they cannot be searched or sorted on, but a query that needs them can read them straight from the index instead of going back to the table. An index that holds every column a query touches is called covering, and it is the single most useful shape in SQL Server tuning.

Column order in the key matters, and the rule is simple: equality first, then range or sort. This index serves all of these:

-- Seek on CustomerId, rows already in CreatedAt order: no sort neededSELECT OrderId, CreatedAt, TotalFROM dbo.OrdersWHERE CustomerId = 42ORDER BY CreatedAt DESC;-- Seek on CustomerId, then a range on CreatedAtSELECT OrderId, Status, TotalFROM dbo.OrdersWHERE CustomerId = 42  AND CreatedAt >= '2026-09-01';

It does not help WHERE CreatedAt >= '2026-09-01' on its own, because the index is sorted by customer first, and every customer has rows in September. That query needs an index that leads with CreatedAt.

Some limits worth knowing: an index key can be at most 900 bytes for a clustered index and 1,700 bytes for a nonclustered one, and at most 32 key columns. nvarchar(max) and varbinary(max) columns cannot be key columns at all, although they can be included - which is usually a bad idea, since it copies large values into the index.

Filtered and columnstore indexes#

A filtered index has a WHERE clause and indexes only the rows that match. It is the right tool when queries keep asking for a small, well-defined slice of a big table:

CREATE NONCLUSTERED INDEX IX_Orders_Pending    ON dbo.Orders (CreatedAt)    INCLUDE (CustomerId, Total)    WHERE Status = 0;

If 2 percent of orders are pending, this index is 2 percent of the size of a full one and much cheaper to maintain. The catch: the optimiser only uses it when it can prove the query's predicate falls inside the filter. A query with WHERE Status = @status and a parameter will usually not match, because the plan has to work for any value. Filtered indexes suit queries with literal values or OPTION (RECOMPILE). They are also the standard way to enforce uniqueness on a nullable column while allowing several NULLs: a unique index WHERE Email IS NOT NULL.

A columnstore index stores data by column rather than by row, compressed, and processes it in batches. It is excellent for aggregating millions of rows - reporting, dashboards, analytics over event tables - and poor at fetching single rows. On Express it works, but columnstore memory is capped at 352 MB per instance, so it suits modest analytical tables rather than turning Express into a data warehouse.

Getting an execution plan#

An execution plan is the tree of operators SQL Server chose to run a query. There are two you can look at:

• The estimated plan - what the optimiser intends to do, without running the query. In SSMS, Ctrl+L.
• The actual plan - the same plan, plus what really happened: actual row counts, executions per operator, memory grants, warnings. In SSMS, toggle "Include Actual Execution Plan" with Ctrl+M, then run the query.

Always prefer the actual plan when you can afford to run the query. The most important number in any plan is the gap between estimated and actual rows, and the estimated plan does not have it.

Pair the plan with I/O statistics, which tell you how much work was done in a unit you can compare between versions of a query:

SET STATISTICS IO, TIME ON;SELECT OrderId, CreatedAt, TotalFROM dbo.OrdersWHERE CustomerId = 42ORDER BY CreatedAt DESC;

Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, ... SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 1 ms.

Logical reads are 8 KB pages read from memory. They are the number to drive down: a fix that takes a query from 48,000 logical reads to 12 has worked, whatever the elapsed time says on a warm cache. Outside SSMS, SET STATISTICS XML ON returns the actual plan as XML, which any tool can save and open later.

Reading a plan: the operators that matter#

A graphical plan reads right to left and top to bottom: data starts at the operators on the right, flows left along arrows whose thickness reflects row counts, and ends at the SELECT on the left. Each operator shows a cost percentage, which is an estimate even in an actual plan - useful for finding where to look, not proof.

The pattern you will see most often is an Index Seek feeding a Nested Loops join with a Key Lookup underneath. The seek found the rows; the lookup went back to the clustered index for each one to fetch a column the index did not have. Hover over the Key Lookup and the tooltip lists that column under "Output List". Add it to the index's INCLUDE list and the lookup disappears.

Then check the estimates. Hover over the operator on the far right of the expensive branch and compare "Estimated Number of Rows" with "Actual Number of Rows". An estimate of 1 against an actual of 200,000 means the optimiser chose a plan for the wrong problem - a nested loop for what should have been a hash join, a memory grant too small so the sort spills. The cause is almost always stale statistics, a predicate the optimiser cannot estimate, or a parameter value very different from the one the plan was compiled for.

Finally, read the yellow warning triangles. The common ones are "Type conversion in expression may affect cardinality estimate", a spill to tempdb on a Sort or Hash Match, and a missing index suggestion in green above the plan.

Why a query ignores your index#

An index is only useful if the predicate is sargable - written so the engine can turn it into a seek range. These are not:

-- A function on the column: every row must be computed firstWHERE YEAR(CreatedAt) = 2026-- Rewrite as a rangeWHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01'-- A leading wildcard: no starting point in the sorted indexWHERE Email LIKE '%@example.com'-- Arithmetic on the columnWHERE Total * 1.2 > 100-- Move it to the other sideWHERE Total > 100 / 1.2

The subtle one is implicit conversion. If Email is varchar and the application sends the parameter as nvarchar - which is what most drivers do for strings by default - SQL Server must convert one side. nvarchar has the higher precedence, so the column is converted, and the plan shows CONVERT_IMPLICIT on the column with a scan where you expected a seek. With a Windows collation the optimiser can sometimes still seek on a computed range, but with the old SQL_ collations it scans. Match the parameter type to the column type in the driver; SQL Server collations and Unicode explains why the collation changes the outcome.

The other common reason is that the index would need too many key lookups. If a query returns a large share of the table, seeking and then looking up each row is slower than scanning, and the optimiser is right to scan. The fix is a covering index, not an index hint.

Missing index hints and index maintenance#

SQL Server records indexes it wished it had while optimising queries. The suggestions are a starting point, not a to-do list - they ignore column order subtleties, suggest near-duplicates of each other, and never consider the cost of maintaining the index on writes:

SELECT TOP (20)    d.statement AS table_name,    d.equality_columns, d.inequality_columns, d.included_columns,    s.user_seeks, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dJOIN sys.dm_db_missing_index_groups AS g ON g.index_handle = d.index_handleJOIN sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handleORDER BY s.user_seeks * s.avg_user_impact DESC;

The opposite question - which indexes are never used - comes from sys.dm_db_index_usage_stats. An index with zero seeks, scans and lookups but thousands of updates since the last restart is costing you writes and space for nothing. Both views reset when the server restarts, so judge them after a representative period of traffic.

On fragmentation, the honest position for a small database on NVMe is that it matters much less than it used to. Fragmentation hurt when spinning disks paid for every out-of-order read. What still matters is page density - half-empty pages after heavy deletes mean more pages to read and more memory used. Check it with sys.dm_db_index_physical_stats and rebuild only what is badly affected:

ALTER INDEX IX_Orders_CustomerId_CreatedAt ON dbo.Orders REBUILD;-- or the lighter, always-online optionALTER INDEX IX_Orders_CustomerId_CreatedAt ON dbo.Orders REORGANIZE;

REBUILD WITH (ONLINE = ON) is an Enterprise feature, so on Express a rebuild locks the table for its duration; on a table of a few million rows that is seconds, but schedule it for a quiet hour. Statistics matter more than fragmentation: UPDATE STATISTICS dbo.Orders WITH FULLSCAN; after a large data load fixes more bad plans than any rebuild. A rebuild also updates the statistics on that index as a side effect; a reorganise does not.

Every index has a cost on every insert, update and delete, and it counts towards the 10 GB data limit on Express. Five well-chosen covering indexes on a busy table beat fifteen single-column ones. SQL Server Query Store and slow queries shows how to find which queries deserve an index in the first place, and the general method in EXPLAIN ANALYZE and slow queries carries over from PostgreSQL almost unchanged.

FAQ#

Should every foreign key column have an index?

Usually, yes. SQL Server does not create one automatically for a foreign key, and without it, joins on that column scan, and deleting a parent row scans the child table to check for references. The exceptions are tiny lookup tables and columns nobody ever joins or filters on.

What is the difference between an index seek and an index scan?

A seek walks the B-tree straight to the rows that match a key or range. A scan reads every page of the index or table from one end. A scan of a 50-row table is fine; a scan of a 5-million-row table to return ten rows is the thing to fix.

Does SQL Server use more than one index per table in a query?

Sometimes. It can intersect two nonclustered indexes, but it rarely chooses to, and the result is almost always slower than one composite index covering the predicate. Design composite indexes for your real queries rather than relying on intersection.

Why is my query fast in SSMS and slow from the application?

Usually a different cached plan. SSMS and most drivers use different SET options, especially ARITHABORT, so they get separate plan cache entries, each compiled for different parameter values. Compare the plans for both, and check parameter types for implicit conversion.

How many indexes is too many?

There is no number, but the symptom is clear: inserts and updates get slower, and usage statistics show indexes that are written constantly and read never. On a write-heavy table, five or six well-designed indexes is a lot. On a read-mostly reporting table, more is fine.