Lesson 45 of 54 17 minAdvanced

Reading execution plans

How SQL Server turns a query into a plan of operators, and how to read one: estimated and actual plans, the access operators, the three join operators, aggregates and sorts, costs, and what to look for first.

SQL is declarative: you say what you want, not how to get it. The query optimizer decides the how. It considers ways of running the query (which index, which join method, in which order), estimates the cost of each from statistics about the data, and picks a cheap one. The result is an execution plan: a tree of operators that pass rows to each other.

Start from a fresh Sales table with one nonclustered index, and a small table of returned sales:

Create dbo.Sales and dbo.SaleReturns

1DROP TABLE IF EXISTS dbo.SaleReturns, dbo.Sales;2CREATE TABLE dbo.Sales (3    SaleId     int           NOT NULL CONSTRAINT PK_Sales PRIMARY KEY,4    SaleDate   date          NOT NULL,5    CustomerId int           NOT NULL,6    ProductId  int           NOT NULL,7    Quantity   int           NOT NULL,8    Amount     decimal(10,2) NOT NULL,9    Channel    varchar(10)   NOT NULL,10    Notes      char(100)     NULL11);12INSERT INTO dbo.Sales (SaleId, SaleDate, CustomerId, ProductId, Quantity, Amount, Channel)13SELECT value,14       DATEADD(day, value * 37 % 1096, '2022-01-01'),15       value * 7919 % 5000 + 1,16       value % 12 + 1,17       value % 7 + 1,18       value * 31 % 9000 / 100.0 + 1,19       CASE value % 10 WHEN 0 THEN 'Phone' WHEN 1 THEN 'Shop' WHEN 2 THEN 'Shop' ELSE 'Web' END20FROM GENERATE_SERIES(1, 100000);21 22CREATE INDEX IX_Sales_CustomerId ON dbo.Sales (CustomerId);23 24CREATE TABLE dbo.SaleReturns (25    SaleId     int          NOT NULL CONSTRAINT PK_SaleReturns PRIMARY KEY,26    ReturnedOn date         NOT NULL,27    Reason     nvarchar(40) NOT NULL28);29INSERT INTO dbo.SaleReturns (SaleId, ReturnedOn, Reason)30SELECT SaleId, DATEADD(day, 14, SaleDate), N'Damaged'31FROM dbo.Sales32WHERE SaleId % 20 = 0;

Getting a plan

Estimated plan (SSMS: Ctrl+L, or Display Estimated Execution Plan): the plan the optimizer would use. The query does not run.
Actual plan (SSMS: Ctrl+M, Include Actual Execution Plan, then run): the same plan after the query has run, with the real number of rows each operator produced.
In a script: SET SHOWPLAN_TEXT ON returns the estimated plan as text instead of running the queries. It must be in a batch of its own, so it is followed by GO.

Query.sql

1SET SHOWPLAN_TEXT ON;2GO3SELECT c.FirstName, o.OrderDate4FROM dbo.Orders AS o5JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId6WHERE o.Status = 'Pending';7GO8SET SHOWPLAN_TEXT OFF;

Output

StmtText
---------------------------------------------------------------------------------------------------------------------------------------
SELECT c.FirstName, o.OrderDate
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Pending';

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([o].[CustomerId]))
       |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[Orders].[PK__Orders__C3905BCFAE1579C2] AS [o]), WHERE:([BuildMart].[dbo].[Orders].[Status] as [o].[Status]='Pending'))
       |--Clustered Index Seek(OBJECT:([BuildMart].[dbo].[Customers].[PK__Customer__A4AE64D8EB2B6A42] AS [c]), SEEK:([c].[CustomerId]=[BuildMart].[dbo].[Orders].[CustomerId] as [o].[CustomerId]) ORDERED FORWARD)

How to read the text form:

Each |-- line is one operator. The lines indented under an operator are its inputs.
Rows flow upwards: the most indented operators run first and pass their rows to the line above.
Here: scan Orders keeping the Pending rows, and for each one (Nested Loops) seek Customers by its key.

The graphical plan in SSMS shows the same tree turned on its side: the first operator to produce rows is on the right, and rows flow right to left to the SELECT on the left. Arrow thickness shows how many rows pass. Hover over an operator for its details.

Operators that read tables

Operator	What it does
Table Scan	Reads every page of a heap.
Clustered Index Scan	Reads every leaf page of the clustered index, which is the whole table.
Index Scan	Reads every leaf page of a nonclustered index (smaller than the table).
Clustered Index Seek / Index Seek	Goes down the tree to the rows for a key or range. A seek can still read many rows if the range is wide.
Key Lookup	Fetches a row from the clustered index for a row found in a nonclustered index. In text plans it is a Clustered Index Seek with LOOKUP.
RID Lookup	The same for a heap, using the row’s address.

The three join operators

Whatever join you write, SQL Server runs it with one of three physical operators:

Operator	How it works	Good when
Nested Loops	For each row of the outer (top) input, search the inner (bottom) input.	The outer input is small and the inner one has an index on the join column.
Merge Join	Reads both inputs sorted on the join column and zips them together.	Both inputs are already sorted on the join key, for example both clustered on it.
Hash Match	Builds a hash table from the smaller input, then probes it with each row of the larger.	Large inputs with no useful order or index. Needs memory.

Nested Loops. Only two customers live in York, and IX_Sales_CustomerId can find each one’s sales directly. So SQL Server reads the York customers and, for each, seeks the index:

Query.sql

1SET SHOWPLAN_TEXT ON;2GO3SELECT c.FirstName, COUNT(*) AS SalesRows4FROM dbo.Customers AS c5JOIN dbo.Sales AS s ON s.CustomerId = c.CustomerId6WHERE c.City = N'York'7GROUP BY c.FirstName;8GO9SET SHOWPLAN_TEXT OFF;

Output

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT c.FirstName, COUNT(*) AS SalesRows
FROM dbo.Customers AS c
JOIN dbo.Sales AS s ON s.CustomerId = c.CustomerId
WHERE c.City = N'York'
GROUP BY c.FirstName;

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
       |--Stream Aggregate(GROUP BY:([c].[FirstName]) DEFINE:([Expr1005]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[CustomerId]))
                 |--Sort(ORDER BY:([c].[FirstName] ASC))
                 |    |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[Customers].[PK__Customer__A4AE64D82EF48356] AS [c]), WHERE:([BuildMart].[dbo].[Customers].[City] as [c].[City]=N'York'))
                 |--Index Seek(OBJECT:([BuildMart].[dbo].[Sales].[IX_Sales_CustomerId] AS [s]), SEEK:([s].[CustomerId]=[BuildMart].[dbo].[Customers].[CustomerId] as [c].[CustomerId]) ORDERED FORWARD)

Merge Join. Sales and SaleReturns are both clustered on SaleId, so both can be read in SaleId order and matched in a single pass:

Query.sql

1SET SHOWPLAN_TEXT ON;2GO3SELECT COUNT(*) AS Returned, SUM(s.Amount) AS Refunded4FROM dbo.Sales AS s5JOIN dbo.SaleReturns AS r ON r.SaleId = s.SaleId;6GO7SET SHOWPLAN_TEXT OFF;

Output

StmtText
----------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS Returned, SUM(s.Amount) AS Refunded
FROM dbo.Sales AS s
JOIN dbo.SaleReturns AS r ON r.SaleId = s.SaleId;

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1010],0), [Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(DEFINE:([Expr1010]=Count(*), [Expr1011]=SUM([BuildMart].[dbo].[Sales].[Amount] as [s].[Amount])))
            |--Merge Join(Inner Join, MERGE:([r].[SaleId])=([s].[SaleId]), RESIDUAL:([BuildMart].[dbo].[Sales].[SaleId] as [s].[SaleId]=[BuildMart].[dbo].[SaleReturns].[SaleId] as [r].[SaleId]))
                 |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[SaleReturns].[PK_SaleReturns] AS [r]), ORDERED FORWARD)
                 |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[Sales].[PK_Sales] AS [s]), ORDERED FORWARD)

Hash Match. Which sales were above that customer’s own average? The derived table works out 5,000 averages, in no useful order, and they are joined to all 100,000 sales:

Query.sql

1SET SHOWPLAN_TEXT ON;2GO3SELECT COUNT(*) AS AboveAverage4FROM dbo.Sales AS s5JOIN (SELECT CustomerId, AVG(Amount) AS AvgAmount6      FROM dbo.Sales7      GROUP BY CustomerId) AS a ON a.CustomerId = s.CustomerId8WHERE s.Amount > a.AvgAmount;9GO10SET SHOWPLAN_TEXT OFF;

Output

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS AboveAverage
FROM dbo.Sales AS s
JOIN (SELECT CustomerId, AVG(Amount) AS AvgAmount
      FROM dbo.Sales
      GROUP BY CustomerId) AS a ON a.CustomerId = s.CustomerId
WHERE s.Amount > a.AvgAmount;

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1013],0)))
       |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))
            |--Hash Match(Inner Join, HASH:([BuildMart].[dbo].[Sales].[CustomerId])=([s].[CustomerId]), RESIDUAL:([BuildMart].[dbo].[Sales].[CustomerId] as [s].[CustomerId]=[BuildMart].[dbo].[Sales].[CustomerId] AND [BuildMart].[dbo].[Sales].[Amount] as [s].[Amount]>[Expr1003]))
                 |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012]/CONVERT_IMPLICIT(decimal(19,0),[Expr1011],0) END))
                 |    |--Hash Match(Aggregate, HASH:([BuildMart].[dbo].[Sales].[CustomerId]) DEFINE:([Expr1011]=COUNT(*), [Expr1012]=SUM([BuildMart].[dbo].[Sales].[Amount])))
                 |         |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[Sales].[PK_Sales]))
                 |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[Sales].[PK_Sales] AS [s]))

Note how the plans also aggregate: a Stream Aggregate works on rows that arrive sorted by the GROUP BY columns and needs almost no memory; a Hash Match (Aggregate) builds a hash table of groups, as the last plan does to work out the 5,000 averages before it joins them. The same operator name covers both jobs; the word in brackets (Inner Join or Aggregate) tells you which.

Sorts and their cost

A Sort operator must read all its input before it can return the first row, and it needs memory for it. ORDER BY, DISTINCT, GROUP BY and Merge Join can all add one when no index provides the order. If the memory granted is too small, the sort spills to tempdb and slows down sharply; the actual plan then shows a warning on the Sort.

Costs are estimates

The graphical plan shows a cost percentage on each operator. It is the optimizer’s estimate, based on the rows it expected, not a measurement. It is still a good place to start: look at the most expensive operators first.

The most useful thing in an actual plan is the comparison of estimated and actual rows. When they differ by a factor of ten or more, the optimizer chose the plan for a different query from the one that ran. The widget shows actual plans from SQL Server with both numbers:

Interactive

Reading an actual plan

SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Spent, MAX(SaleDate) AS LastSale
FROM dbo.Sales
WHERE CustomerId = 3000;

Operators (the most indented run first; rows flow up)

Compute ScalarWorks out an expression, such as converting COUNT’s result. Almost free.Estimated rows 1, actual rows not counted (it only works out expressions as rows pass through). Cost 0% of the plan.

Estimated and actual rows are close on every operator (within a factor of ten), so the optimizer chose the plan for about the right amount of data. Click an operator for details.

What to look for first

Scans of big tables when you expected a handful of rows: a missing index, or a WHERE the index cannot use (next lesson).
Key Lookups with many executions: make the index cover the query.
Estimated vs actual rows far apart: statistics are stale or the predicate cannot be estimated.
Warnings (a yellow triangle): implicit conversions, sorts or hashes that spilled, missing statistics.
Missing index suggestions (green text above the plan): useful hints, but check them before you create anything.

Quick check

In a graphical execution plan in SSMS, where does the data flow start?

Quick check

Which join operator suits a small outer input and an index on the inner input’s join column?

Practice

Get the estimated plan (SET SHOWPLAN_TEXT ON) for a query that returns each Channel with the total Amount of its 2024 sales, largest total first. Which operator reads the table, and how does SQL Server group the rows?

Show a hint

GROUP BY Channel with WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01' and ORDER BY the total DESC. Remember the GO lines around SHOWPLAN_TEXT.

Show the solution

Solution.sql

1SET SHOWPLAN_TEXT ON;2GO3SELECT Channel, SUM(Amount) AS Total4FROM dbo.Sales5WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01'6GROUP BY Channel7ORDER BY Total DESC;8GO9SET SHOWPLAN_TEXT OFF;

Output

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------
SELECT Channel, SUM(Amount) AS Total
FROM dbo.Sales
WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01'
GROUP BY Channel
ORDER BY Total DESC;

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(ORDER BY:([Expr1002] DESC))
       |--Hash Match(Aggregate, HASH:([BuildMart].[dbo].[Sales].[Channel]), RESIDUAL:([BuildMart].[dbo].[Sales].[Channel] = [BuildMart].[dbo].[Sales].[Channel]) DEFINE:([Expr1002]=SUM([BuildMart].[dbo].[Sales].[Amount])))
            |--Clustered Index Scan(OBJECT:([BuildMart].[dbo].[Sales].[PK_Sales]), WHERE:([BuildMart].[dbo].[Sales].[SaleDate]>='2024-01-01' AND [BuildMart].[dbo].[Sales].[SaleDate]<'2025-01-01'))

Remember

The optimizer turns a query into a plan of operators, choosing by estimated cost.
Estimated plans show the choice; actual plans add the real row counts. SHOWPLAN_TEXT gives the plan as text.
Read text plans from the innermost line up, and graphical plans from right to left.
Scans read everything; seeks navigate the tree; lookups fetch missing columns one row at a time.
Nested Loops suits small outer inputs with an index, Merge Join sorted inputs, Hash Match large unsorted ones.
Compare estimated and actual rows: a big gap means the plan was chosen for the wrong amount of data.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Reading execution plans

Getting a plan

Operators that read tables

The three join operators

Sorts and their cost

Costs are estimates

Reading an actual plan

What to look for first

Remember