Day 22: Query execution plans

A. Lesson overview

Activity breakdown

Business scenario. The retailer’s history has grown to a million orders. Some reports and screens have become slow. Before anyone adds indexes or rewrites queries, the team needs to see what SQL Server is doing and measure it — guessing is how performance work goes wrong.

About numbers in Week 4. Row counts and query results in these lessons are exact: the data is deterministic and the values were computed independently from the generation formulas. Timings, logical reads and some plan choices are not: they depend on your hardware, memory, number of CPUs and SQL Server’s decisions. Wherever a number depends on your machine, the lesson asks you to measure and record it rather than quoting one.

B. Theory explained clearly

Part 1 — How SQL Server runs a query

1. Parse — check the syntax.
2. Bind — resolve names (tables, columns), check permissions and types.
3. Optimise — the query optimiser considers many ways to get the answer (which index, which join order, which join algorithm) and estimates the cost of each, mainly from statistics about the data. It picks a plan that looks cheap — not necessarily the best possible, but good enough, found quickly.
4. Cache — the plan is stored in the plan cache and reused for later executions of the same query text.
5. Execute — the plan’s operators read and process the rows.

The execution plan is the optimiser’s chosen recipe. Reading it tells you what SQL Server actually does with your query.

Part 2 — Getting and reading a plan

Reading order. Data flows from right to left; the leftmost operator (SELECT) returns results to you. Start at the top right, follow the arrows left. Arrow thickness shows the number of rows.

Properties. Hover over an operator for a tooltip, or select it and press F4 for the full Properties window. The most useful properties:

A green Missing Index hint may appear above the plan. It is a suggestion from the optimiser, for this one query; Day 23 explains why to treat it with care.

Part 3 — The operators you will meet most

Part 4 — Measuring: STATISTICS IO and STATISTICS TIME

SET STATISTICS IO, TIME ON;
-- run the query, then look at the Messages tab
SET STATISTICS IO, TIME OFF;

Messages look like this (numbers vary):

Table 'SalesOrder'. Scan count 1, logical reads 6123, physical reads 0, read-ahead reads 0, …
 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 98 ms.

Rule for this week: run each query twice and record the second run (warm cache), focusing on logical reads.

Part 5 — Statistics and estimates

The optimiser estimates how many rows each step will produce using statistics objects. Each statistics object describes one or more columns:

• a histogram of up to 200 steps for the first column — for each step, the highest value (RANGE_HI_KEY), the number of rows equal to it (EQ_ROWS), and the rows and distinct values between steps;
• density — 1 ÷ number of distinct values, used when the value isn’t known at compile time.

SQL Server creates statistics automatically for every index and for columns you filter on (AUTO_CREATE_STATISTICS, on by default), and updates them after enough changes (AUTO_UPDATE_STATISTICS). The script ran UPDATE STATISTICS … WITH FULLSCAN so everyone starts with accurate statistics.

When estimates are badly wrong — because statistics are out of date, the value is unknown (a local variable), or the data is skewed — the optimiser can choose a poor plan: a nested loop that runs a million times, a memory grant that’s too small and spills to tempdb. Comparing estimated and actual rows is the first diagnostic step for a slow query.

Query Store (enabled by default for new databases on SQL Server 2022 and later) records plans and runtime statistics over time; the stretch uses it.

C. Dataset and starting state

Generate the data. Open scripts/setup/05_generate_performance_data.sql (download it — it is also in the setup zip) and run it (F5). It is destructive for the Perf schema only: it drops and recreates every Perf table. It prints a line per 200,000 orders and finishes with a validation table.

Customer 1 has 50,000 orders; every other customer has 9 or 10. Only the clustered primary keys exist — no other indexes.

Validation. All 17 rows in the script’s final result should show PASS. To re-check later:

SELECT  (SELECT COUNT(*) FROM Perf.Customer)       AS Customers,       -- 100000
        (SELECT COUNT(*) FROM Perf.SalesOrder)     AS Orders,          -- 1000000
        (SELECT COUNT(*) FROM Perf.SalesOrderLine) AS OrderLines,      -- 3000000
        (SELECT COUNT(*) FROM sys.indexes
         WHERE object_id IN (OBJECT_ID('Perf.SalesOrder'), OBJECT_ID('Perf.SalesOrderLine'), OBJECT_ID('Perf.Customer'))
           AND index_id > 1)                       AS NonclusteredIndexes;   -- 0

Baseline table. You will record measurements today and compare them on Days 23 and 24:

CREATE TABLE Lab.PerfBaseline
(
    RunID        int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    QueryName    varchar(40)    NOT NULL,
    LessonDay    tinyint        NOT NULL,
    LogicalReads bigint         NULL,     -- total across tables, from STATISTICS IO
    CpuMs        int            NULL,
    ElapsedMs    int            NULL,
    PlanShape    nvarchar(200)  NULL,     -- e.g. 'Clustered Index Scan + Stream Aggregate'
    RecordedAt   datetime2(0)   NOT NULL DEFAULT (SYSDATETIME())
);

(If it already exists from a previous attempt, keep it.)

D. Worked examples

Turn on Include Actual Execution Plan (Ctrl + M) for all of today’s examples.

Worked example 1 — A seek on the clustered key

SET STATISTICS IO ON;
SELECT OrderID, CustomerID, OrderDate, OrderStatus
FROM   Perf.SalesOrder
WHERE  OrderID = 123456;
SET STATISTICS IO OFF;

Expected result: one row: 123456, customer 57842, 2024-02-26, Delivered. Plan: a single Clustered Index Seek with a Seek Predicate on OrderID. Logical reads: a handful (one per level of the B-tree — typically 3 for a table this size). This is the best case: SQL Server walks from the root of the index to one leaf page.

Worked example 2 — The same table, filtered on a column with no index

SET STATISTICS IO ON;
SELECT OrderID, OrderDate, OrderStatus
FROM   Perf.SalesOrder
WHERE  CustomerID = 2
ORDER BY OrderID;
SET STATISTICS IO OFF;

Expected result: 10 rows — order IDs 99999, 199998, 299997, … 999990. Plan: Clustered Index Scan with a Predicate (not a seek predicate) on CustomerID, probably with a green Missing Index suggestion; possibly parallel. Logical reads: thousands — every page of the table — to return 10 rows. Note the number; it is today’s most important baseline.

Worked example 3 — Joins and aggregation

SET STATISTICS IO, TIME ON;

SELECT  c.Region,
        COUNT(DISTINCT o.OrderID)                                      AS Orders,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(14,2)) AS NetSales
FROM    Perf.SalesOrder     AS o
JOIN    Perf.Customer       AS c ON c.CustomerID = o.CustomerID
JOIN    Perf.SalesOrderLine AS l ON l.OrderID    = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled'
  AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
GROUP BY c.Region
ORDER BY NetSales DESC, c.Region;

SET STATISTICS IO, TIME OFF;

Expected result:

South is larger because customer 1 is in the South region. Plan: typically Hash Match joins and aggregates over scans of all three tables, often with Parallelism operators — large, unsorted inputs are exactly what hash joins are for. The STATISTICS IO output lists each table with its own logical reads (and possibly Worktable/Workfile lines for hash operations). CPU time may be greater than elapsed time if the plan is parallel.

Worked example 4 — Estimates come from statistics

CREATE STATISTICS st_PerfSalesOrder_CustomerID ON Perf.SalesOrder (CustomerID) WITH FULLSCAN;

DBCC SHOW_STATISTICS ('Perf.SalesOrder', 'st_PerfSalesOrder_CustomerID') WITH STAT_HEADER;
DBCC SHOW_STATISTICS ('Perf.SalesOrder', 'st_PerfSalesOrder_CustomerID') WITH DENSITY_VECTOR;
DBCC SHOW_STATISTICS ('Perf.SalesOrder', 'st_PerfSalesOrder_CustomerID') WITH HISTOGRAM;

Expected result: header: Rows 1000000, Rows Sampled 1000000; density vector: All density 1E-05 (1 ÷ 100,000 distinct customers); histogram: up to 200 steps, the first with RANGE_HI_KEY 1 and EQ_ROWS 50000 — the statistics know customer 1 is special. Now compare estimates:

SELECT COUNT(*) FROM Perf.SalesOrder WHERE CustomerID = 1;   -- estimate 50000, actual 50000
SELECT COUNT(*) FROM Perf.SalesOrder WHERE CustomerID = 2;   -- estimate small (around 10), actual 10

In the actual plan, select the scan operator and compare Estimated Number of Rows with Actual Number of Rows. With a literal value the optimiser looks it up in the histogram. (If AUTO_CREATE_STATISTICS already made a _WA_Sys_… statistic on CustomerID during worked example 2, both exist; that’s fine.)

E. Guided practical lab — Record a performance baseline

You’ll measure five queries that Days 23 and 24 will improve. For each: run once to warm the cache, run again, then record the second run’s numbers.

Step 1 — Prepare

1. Do: Turn on plans and measurements.
2. Run:

   USE RetailLab;
   GO
   SET STATISTICS IO, TIME ON;

3. Observe: Nothing yet; the next queries will print to Messages.
4. Why it matters: Measurements are only comparable if collected the same way each time.
5. Confirm: Ctrl + M is on (the toolbar button is highlighted).

Step 2 — Run the five baseline queries

1. Do: Run each query twice; on the second run, note total logical reads (add up the tables), CPU and elapsed time, and the main operators.
2. Run:

   -- Q1: one customer's orders
   SELECT OrderID, OrderDate, OrderStatus FROM Perf.SalesOrder WHERE CustomerID = 12345 ORDER BY OrderID;
   
   -- Q2: one month of orders
   SELECT COUNT(*) AS Orders, SUM(ShippingCost) AS Shipping
   FROM   Perf.SalesOrder WHERE OrderDate >= '2025-12-01' AND OrderDate < '2026-01-01';
   
   -- Q3: find a customer by email
   SELECT CustomerID, CustomerName, Region FROM Perf.Customer WHERE Email = 'customer12345@example.com';
   
   -- Q4: the pending-orders screen
   SELECT OrderID, CustomerID, OrderDate FROM Perf.SalesOrder WHERE OrderStatus = 'Pending' ORDER BY OrderDate, OrderID;
   
   -- Q5: top five products by 2025 net sales
   SELECT TOP (5) p.ProductCode,
          CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(14,2)) AS NetSales
   FROM   Perf.SalesOrder AS o
   JOIN   Perf.SalesOrderLine AS l ON l.OrderID = o.OrderID
   JOIN   Perf.Product        AS p ON p.ProductID = l.ProductID
   WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
   GROUP BY p.ProductCode
   ORDER BY NetSales DESC, p.ProductCode;

3. Observe: Results — Q1: 10 rows; Q2: 21216, 71716.28; Q3: customer 12345, Customer 12345, North; Q4: 11402 rows; Q5: NAV-004 22195143.60, TNT-002 22192770.40, SLP-002 19010719.00, NAV-002 19007579.50, TNT-003 15826763.70. Plans: Q1–Q4 scan the whole table (clustered index scan); Q5 scans orders and lines with hash joins.
4. Why it matters: Q1–Q4 return tiny fractions of their tables but read all of them. That gap is the opportunity for Day 23.
5. Confirm: You have five sets of numbers.

Step 3 — Save the numbers

1. Do: Insert your measurements (replace the zeros with your values).
2. Run:

   INSERT INTO Lab.PerfBaseline (QueryName, LessonDay, LogicalReads, CpuMs, ElapsedMs, PlanShape)
   VALUES ('Q1 customer orders', 22, 0, 0, 0, N'Clustered Index Scan'),
          ('Q2 month of orders',  22, 0, 0, 0, N'Clustered Index Scan'),
          ('Q3 customer by email',22, 0, 0, 0, N'Clustered Index Scan'),
          ('Q4 pending orders',   22, 0, 0, 0, N'Clustered Index Scan + Sort'),
          ('Q5 top products 2025',22, 0, 0, 0, N'Scans + Hash Match joins');
   SELECT * FROM Lab.PerfBaseline WHERE LessonDay = 22 ORDER BY RunID;

3. Observe: 5 rows with your own numbers.
4. Why it matters: Without a recorded “before”, you can’t prove an improvement “after”.
5. Confirm: PlanShape describes what your plans showed — edit the text if your plan differs.

Step 4 — Estimated vs actual on Q4

1. Do: In Q4’s actual plan, open the properties (F4) of the scan.
2. Run: (no new code)
3. Observe: Estimated Number of Rows close to 11402 — SQL Server auto-created statistics on OrderStatus the first time you filtered on it; Number of Rows Read 1000000; Actual Number of Rows 11402.
4. Why it matters: “Rows read” vs “rows returned” is a direct measure of wasted work: a million rows read for eleven thousand returned.
5. Confirm: You can find all three numbers.

Step 5 — Switch off

1. Do: SET STATISTICS IO, TIME OFF;
2. Run: as shown.
3. Observe: Messages no longer show reads and times.
4. Why it matters: Leave sessions as you found them.
5. Confirm: Run Q3 again; Messages shows only (1 row affected).

F. Independent practice

Task 1 — Order without a sort

Compare the plans for the five newest orders sorted two ways: ORDER BY OrderID DESC and ORDER BY OrderDate DESC, OrderID DESC (both TOP (5), columns OrderID, CustomerID, OrderDate). Which needs a sort and why?

Success criteria: By date: 999245 (customer 288), 997784 (30513), 996323 (60738), 994862 (90963), 993401 (21189), all 2025-12-31. By ID: 1000000 down to 999996. The OrderID version reads the clustered index backwards with no Sort and very few reads; the OrderDate version scans everything and uses a Top N Sort.

Task 2 — A local variable changes the estimate

Run SELECT COUNT(*) FROM Perf.SalesOrder WHERE CustomerID = @c; with DECLARE @c int = 1;. Compare the estimated rows with the literal version from worked example 4. Then add OPTION (RECOMPILE) and compare again.

Success criteria: With the variable: estimate 10 (1,000,000 × density 0.00001), actual 50000. With OPTION (RECOMPILE): estimate 50000. Explain in one sentence.

Task 3 — Read a join plan

Run: customer 12345’s orders with customer name — Perf.Customer joined to Perf.SalesOrder on CustomerID, filtered on c.CustomerID = 12345. Name the join operator, which table is accessed by seek and which by scan, and how many times each operator executed.

Success criteria: 10 rows. Typically: a Clustered Index Seek on Perf.Customer (1 row) and a Clustered Index Scan of Perf.SalesOrder (no index on CustomerID), joined by Nested Loops; each operator has Number of Executions 1 (or one per thread in a parallel plan). The filter c.CustomerID = 12345 was applied to o.CustomerID as well — check the scan’s predicate.

Task 4 — Parallel or not (challenging)

Run Q5 with OPTION (MAXDOP 1) and without. Record CPU and elapsed time for both. Explain the difference.

Success criteria: Same five rows. Typically the default plan shows Parallelism operators, with CPU time higher than elapsed time; MAXDOP 1 has no Parallelism operators and CPU ≈ elapsed. Your numbers are your own — the pattern is what matters.

Stretch challenge — Query Store

Check that Query Store is on, then find the query with the most total logical reads recorded today, with its execution count and average duration.

Success criteria: actual_state_desc is READ_WRITE; the top query by logical reads is one of today’s large scans or joins (most likely Q5 or worked example 3).

G. Solutions and explanations

SELECT TOP (5) OrderID, CustomerID, OrderDate FROM Perf.SalesOrder ORDER BY OrderID DESC;
SELECT TOP (5) OrderID, CustomerID, OrderDate FROM Perf.SalesOrder ORDER BY OrderDate DESC, OrderID DESC;

DECLARE @c int = 1;
SELECT COUNT(*) FROM Perf.SalesOrder WHERE CustomerID = @c;
SELECT COUNT(*) FROM Perf.SalesOrder WHERE CustomerID = @c OPTION (RECOMPILE);

SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM   Perf.Customer   AS c
JOIN   Perf.SalesOrder AS o ON o.CustomerID = c.CustomerID
WHERE  c.CustomerID = 12345
ORDER BY o.OrderID;

SET STATISTICS TIME ON;
-- Q5 as in the lab (default)
-- Q5 again, adding OPTION (MAXDOP 1) after the ORDER BY
SET STATISTICS TIME OFF;

SELECT actual_state_desc, desired_state_desc, query_capture_mode_desc
FROM   sys.database_query_store_options;

SELECT TOP (5)
        q.query_id,
        LEFT(qt.query_sql_text, 80)                        AS QueryStart,
        SUM(rs.count_executions)                           AS Executions,
        SUM(rs.avg_logical_io_reads * rs.count_executions) AS TotalLogicalReads,
        CAST(AVG(rs.avg_duration) / 1000.0 AS decimal(12,1)) AS AvgDurationMs
FROM    sys.query_store_query         AS q
JOIN    sys.query_store_query_text    AS qt ON qt.query_text_id = q.query_text_id
JOIN    sys.query_store_plan          AS p  ON p.query_id       = q.query_id
JOIN    sys.query_store_runtime_stats AS rs ON rs.plan_id       = p.plan_id
GROUP BY q.query_id, LEFT(qt.query_sql_text, 80)
ORDER BY TotalLogicalReads DESC;

H. Knowledge check

1. Understanding. In which direction does data flow in a graphical plan, and where do you start reading?

2. Query interpretation. A plan shows an Index Seek feeding a Key Lookup through Nested Loops. The Key Lookup’s Number of Executions is 40,000. What does that mean, and why is it a concern?

3. Understanding. Why are logical reads a better comparison measure than elapsed time when tuning a query?

4. Debugging. A query’s plan shows Estimated Number of Rows 1 and Actual Number of Rows 250,000 for a scan feeding a nested loop. Give two possible causes.

5. Understanding. What is the difference between a Seek Predicate and a Predicate on a scan?

I. Troubleshooting and cleanup

Cleanup. Keep the Perf tables, the statistics object and Lab.PerfBaseline: Days 23–25 use them. To free space at the end of Week 4, see Day 25’s cleanup.

J. Completion checklist, recap and references

Completion checklist

• Script 05 finished with 17 PASS rows.
• You saw a seek (worked example 1) and a scan returning 10 rows from a million (worked example 2).
• The region totals matched; you found the hash joins in the plan.
• You read the histogram’s first step: RANGE_HI_KEY 1, EQ_ROWS 50000.
• Lab.PerfBaseline holds your five measurements.

Recap

• The optimiser picks a plan using cost estimates based on statistics; read plans right to left and check operator properties.
• Scans read everything; seeks navigate; key lookups add a seek per row; nested loops, hash and merge joins suit different input sizes and orders.
• Compare logical reads first; record warm-cache measurements.
• Large gaps between estimated and actual rows point to statistics or unknown values.
• Record a baseline before changing anything.

Next: Day 23 — Indexing Strategies: clustered and nonclustered indexes, covering and included columns, filtered and columnstore indexes, and maintenance — applied to today’s five queries.

References (Microsoft Learn)

• Execution plan overview
• Display an actual execution plan
• Showplan logical and physical operators reference
• SET STATISTICS IO (Transact-SQL)
• Statistics
• DBCC SHOW_STATISTICS (Transact-SQL)
• Monitor performance by using the Query Store
• GENERATE_SERIES (Transact-SQL)