How to Read a SQL Server Execution Plan (Beginner's Guide)

When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them.

Estimated vs actual plans

SQL Server Management Studio (SSMS) can show two kinds of plan:

• Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run.
• Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can.

Turn on IO and time statistics at the same time so you have numbers to compare after each change:

SET STATISTICS IO, TIME ON;
-- run the query with "Include Actual Execution Plan" enabled
SET STATISTICS IO, TIME OFF;

Read from right to left

Data flows from right to left. The operators on the far right read data from tables and indexes; each operator passes rows to the one on its left, and the final SELECT (or INSERT, UPDATE) operator is on the far left. Where there are several branches, read them from top to bottom.

Follow the thick arrows

The thickness of each arrow shows how many rows pass between operators. A very thick arrow that leads into an operator which then outputs only a few rows means a lot of work was done to throw most of it away. That is usually where a better index or a better filter helps.

Each operator also shows a cost percentage. It is a useful pointer, but remember that it is based on SQL Server's estimates, even in an actual plan, so it can be wrong when estimates are wrong.

Compare estimated and actual rows

Hover over an operator, or open its properties (F4), and compare Estimated Number of Rows with Actual Number of Rows. A big gap, such as an estimate of 10 rows when 500,000 came back, is one of the most important clues in any plan. It usually means:

• Out-of-date statistics on the table or index.
• Parameter sniffing: the plan was compiled for a different parameter value.
• Filters the optimizer cannot estimate well, such as functions on columns or complex expressions.

Bad estimates lead to bad choices downstream: the wrong join type, too little memory, or a scan where a seek would be better.

Spot scans and key lookups

• Index Seek: SQL Server jumped straight to the rows it needed. Usually good.
• Index Scan / Clustered Index Scan: it read the whole index or table. Fine for small tables or when most rows are needed; a red flag on large tables when only a few rows come back.
• Key Lookup: for each row found in a nonclustered index, SQL Server went back to the clustered index for more columns. Cheap for a handful of rows, expensive for thousands. Adding the missing columns to the index with INCLUDE usually removes it.

Check the join types

Join	Best for	Watch out for
Nested Loops	A small outer input with an index on the inner side	A large outer input, often caused by a bad row estimate
Hash Match	Large, unsorted inputs	Memory grants and spills to tempdb
Merge Join	Two large inputs already sorted on the join key	Extra Sort operators added just to enable it

None of these is bad by itself. A join type becomes a problem when it was chosen because of a wrong estimate.

Look for warning icons

A small yellow triangle on an operator means SQL Server is warning you about something. Common ones:

• Implicit conversion: a column is compared with a value of a different data type, for example an NVARCHAR parameter against a VARCHAR column. This can prevent index seeks. Match parameter types to column types.
• Spill to tempdb on a Sort or Hash operator: the memory grant was too small, usually because the row estimate was too low.
• No statistics / missing join predicate: check the query and the statistics on the columns involved.

Also look at the green missing index hint at the top of the plan, but treat it as a suggestion to evaluate, not an instruction.

Confirm with STATISTICS IO

The Messages tab shows logical reads per table. After each change, run the query again and compare. Fewer logical reads on the biggest table is the clearest sign your fix worked.

Where to get plans from production

You do not always have to reproduce a problem. Query Store keeps the plans each query used over time and lets you compare them, which is ideal for "it was fast yesterday" problems. For queries currently in the plan cache, you can retrieve the cached plan:

SELECT TOP (10)
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    qs.execution_count,
    st.text       AS query_text,
    qp.query_plan AS plan_xml
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Click the XML in SSMS to open it as a graphical plan. Cached plans are estimated plans, so they do not include actual row counts.

A five-minute routine

1. Get the actual plan and turn on STATISTICS IO.
2. Find the thickest arrows and the operators with the highest cost.
3. Compare estimated and actual rows on those operators.
4. Check for scans on large tables, expensive key lookups and warnings.
5. Make one change, rerun, and compare logical reads.

Related reading:

• SQL Server Parameter Sniffing: What It Is and How to Fix It
• SQL Server Statistics Explained
• SQL Server Index Tuning for Beginners