How to Read a SQL Server Execution Plan

An execution plan is SQL Server showing you its working. You wrote what you wanted, the optimizer decided how to get it, and the plan is that decision written down. Learning to read one is the biggest single step from writing SQL to tuning it.

Estimated and Actual

There are two plans and the difference matters.

The estimated plan is what the optimizer intends to do. You get it without running the query, which is handy when the query takes ten minutes.

The actual plan is the same thing plus what really happened, including how many rows each step produced. That extra column is where nearly every answer lives.

In SQL Server Management Studio the estimated plan is Ctrl+L. The actual plan is Ctrl+M, then run the query.

A Plan in Text

The graphical plan is easier on the eye and the text version is easier to talk about. This ran on SQL Server 2025 against a table of 200,000 orders.

SET SHOWPLAN_TEXT ON;
GO
SELECT city, COUNT(*) AS orders, AVG(amount) AS avg_amount
FROM dbo.Orders WHERE amount > 100 GROUP BY city;

|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1010],0), ...))
     |--Hash Match(Aggregate, HASH:([dbo].[Orders].[city]),
          DEFINE:([Expr1010]=COUNT(*), [Expr1011]=SUM([dbo].[Orders].[amount])))
          |--Clustered Index Scan(OBJECT:([dbo].[Orders].[PK_Orders]),
               WHERE:([dbo].[Orders].[amount]>($100.0000)))

Read It Inside Out

This is the rule people miss, and everything else follows from it. Execution starts at the most indented line and works outward.

So the order here is: scan the table and keep rows over 100, group those by city and count them, then work out the average. The top line is the last thing that happens, not the first.

The graphical plan has the same shape turned sideways. Read right to left and follow the arrows.

The Operators You Will Actually Meet

Clustered Index Scan reads the whole table. Fine on a small table. A warning sign on a large one when you only wanted a few rows.

Index Seek jumps straight to the rows you asked for. This is what you want.

Key Lookup means an index found the row but not every column, so SQL Server goes back to the table for the rest. One is cheap. Ten thousand is a problem, and the fix is usually an INCLUDE on the index.

Hash Match builds a hash table in memory. Normal for grouping, and for joining large unsorted sets.

Nested Loops takes each row from one side and looks it up on the other. Excellent when the first side is small, terrible when it is not.

Sort is honest work when you asked for ORDER BY. When you did not, something upstream needed sorted input, and an index might have supplied it free.

Ignore the Percentages

Every graphical plan puts a cost percentage under each operator, and people chase the biggest one. Those numbers are estimates from the optimizer, not measurements.

If the estimate was wrong, the percentages are wrong with it. The operator showing 2 percent can be the one taking all the time. Treat them as a hint and never as evidence.

What to Look at Instead

Hover any operator in an actual plan and compare Estimated Number of Rows with Actual Number of Rows. That one comparison finds most bad plans.

The optimizer chose Nested Loops because it expected 5 rows. It got 500,000. It would have chosen differently had it known. Now you know what to fix, and it is usually stale statistics, a variable the optimizer cannot see into, or a function wrapped around a column.

Two more things deserve a glance. A warning triangle on an operator is the plan telling you something plainly, often an implicit conversion or a spill to tempdb. And a thick arrow between operators means a lot of rows moved, which is easier to spot than any number.

Back It Up With Reads

SET STATISTICS IO ON;
SELECT COUNT(*) FROM dbo.Orders WHERE city = 'Perth';

16440
Table 'Orders'. Scan count 1, logical reads 42, physical reads 1

Forty two logical reads to find 16,440 rows, because a nonclustered index on city exists. Without it the same query reads the whole table. The plan tells you what SQL Server did and the read count tells you what it cost, so use both.

Where to Find Plans You Missed

Query Store keeps plans after the fact, including the one from the slow run at three in the morning. It is on by default for new databases in current versions. It is the first place to look when the complaint is that something was slow yesterday.

Start with one query you already know is slow. Turn on the actual plan, run it, and find the operator where estimated and actual rows disagree the most. That habit alone will carry you a long way.

An execution plan is not a score card, it is the database explaining what it assumed about your data.

This post was rewritten from scratch in September 2026. The original, published on 2011-10-03, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.