SpDev Install free

How to read a SQL Server execution plan

Estimated vs actual plans, how to get them in SSMS, VS Code and T-SQL, how to read the operators, and the eight things to look for when a query is slow.

By Areeb Ahmer, . 5 minute read.

An execution plan is SQL Server’s answer to “how will you run this query?”. It lists the steps, called operators, that read the tables, join them, sort and aggregate them: which index each read uses, how the joins are done, how many rows SQL Server expected at each step and, in an actual plan, how many it got.

Every serious performance investigation ends up in a plan. This article shows how to get one and how to read it, using the SpDevDemo database.

Estimated or actual?

  • An estimated plan is what the optimizer intends to do. Getting one doesn’t run the query. It has estimated row counts only.
  • An actual plan is the same plan after running the query, with what really happened added: actual rows and executions per operator, memory used, waits, warnings found at run time.

The plan shape is the same: SQL Server doesn’t change its mind halfway through. What the actual plan adds is the comparison between estimate and reality, and that’s where most problems show. When you can afford to run the query, use the actual plan.

How to get one

SSMS: Include Actual Execution Plan (Ctrl+M) before running, or Display Estimated Execution Plan (Ctrl+L) without running.

VS Code: the MSSQL extension shows estimated and actual plans for a query. SpDev Pro captures the actual plan of every statement in a stored procedure while it profiles it, and opens the plan of a line from its report.

T-SQL, from any client:

SET SHOWPLAN_XML ON;     -- estimated: returns the plan instead of running the statement
SET STATISTICS XML ON;   -- actual: runs the statement, then returns its plan

From the plan cache, for a query that already ran (estimated plan, without actuals):

SELECT TOP (10) qs.execution_count, qs.total_logical_reads, t.text, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
ORDER BY qs.total_logical_reads DESC;

From Query Store, which keeps plan history across restarts and is on by default for new databases on SQL Server 2022: the Top Resource Consuming Queries report in SSMS, or sys.query_store_plan.

The XML plan can be saved as a .sqlplan file and opened in SSMS or another plan viewer.

Reading the plan

Take a simple query, with its plan as text:

SET STATISTICS PROFILE ON;

SELECT o.OrderNumber, o.Total, c.Name
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.CustomerId = 42;

SET STATISTICS PROFILE OFF;
Rows  Executes  Operator
----  --------  -------------------------------------------------------------------------
9     1         Nested Loops (Inner Join)
1     1           |--Clustered Index Seek (Customers.PK_Customers, CustomerId = 42)
9     1           |--Nested Loops (Inner Join, outer references: OrderId)
9     1                |--Index Seek (Orders.IX_Orders_CustomerId, CustomerId = 42)
9     9                |--Clustered Index Seek (Orders.PK_Orders, lookup of OrderNumber, Total)

In the graphical plan the same operators are drawn as a tree, with the root (the SELECT) at the top left.

Read it from the right, and from the top. Data flows from the operators at the leaves, which read tables, toward the root, which returns rows. Here:

  1. An Index Seek on IX_Orders_CustomerId finds the 9 orders of customer 42.
  2. For each of them, a Clustered Index Seek fetches OrderNumber and Total, which the index doesn’t hold. This is a key lookup: it executed 9 times, once per row. The graphical plan labels it Key Lookup.
  3. A Nested Loops join combines the two.
  4. A Clustered Index Seek on Customers finds customer 42, once.
  5. The outer Nested Loops join pairs the customer with the 9 orders.

The arrows between operators get thicker with the number of rows flowing through them. A thick arrow into an operator that outputs a thin one is a filter doing work an index could have done.

The cost percentages on each operator are estimates, even in an actual plan. They tell you where the optimizer thought the cost would be. When estimates are wrong, the percentages are wrong too. Trust actual rows, executions and the runtime statistics more than percentages.

The operators you’ll meet most

Operator What it does Worth a look when
Index Seek Navigates an index to the rows it needs Rarely a problem
Index Scan / Clustered Index Scan / Table Scan Reads a whole index or table It reads many rows to return few
Key Lookup / RID Lookup Fetches columns missing from a nonclustered index, one row at a time It executes thousands of times
Nested Loops For each outer row, searches the inner input The outer input is large
Hash Match Builds a hash table from one input, probes it with the other It spills to tempdb
Merge Join Joins two inputs already sorted on the join key It needed a Sort to get there
Sort Sorts rows It’s large, or spills
Spool Stores rows in tempdb to reuse them It appears unexpectedly in a big plan
Compute Scalar Calculates an expression Almost never

What to look for

When a query is slow, go through the actual plan in this order.

  1. Estimated vs actual rows. Find the first operator, reading from the right, where they differ by a factor of 10 or more. Everything to its left was planned for the wrong number of rows. Causes: stale statistics, parameter sniffing, table variables, functions on columns, correlated predicates.
  2. Scans that keep few rows. In the operator’s properties, compare Number of Rows Read with Actual Number of Rows. 200,000 read for 39 returned means a missing or unusable index.
  3. Key lookups with many executions. Add the columns to the index’s INCLUDE list (see covering indexes).
  4. Warnings (the yellow triangle): implicit conversions, missing join predicates, missing statistics, spills to tempdb, excessive memory grants. Each is worth reading.
  5. Implicit conversions of a column in a seek or scan predicate: CONVERT_IMPLICIT(nvarchar(20), [OrderNumber]). See implicit conversions.
  6. Missing index suggestions, in green at the top of the graphical plan. A hint, not an order.
  7. Parallelism and memory. On the root operator: Degree of Parallelism, Memory Grant against Used Memory. A grant far larger than its use starves other queries.
  8. Waits. Actual plans record what the query waited on (WaitStats on the root). A plan that looks fine with a long duration may simply have been blocked.

Plans of stored procedures

A procedure has one plan per statement. In SSMS, executing a procedure with Include Actual Execution Plan on shows them all, one after another, which for a 300-line procedure is a long scroll. Find the statement that matters first: sys.dm_exec_procedure_stats and sys.dm_exec_query_stats tell you which statements of a procedure use the most time and reads.

That is the part SpDev Pro automates. It measures every statement of the procedure as it runs, so you see which lines matter, captures the actual plan of each expensive statement, and checks it for the problems above. The findings appear on the lines of your .sql file, each pointing at the operator and the numbers behind it.