Estimated vs Actual Execution Plans, Operators, Properties, Warnings, and Runtime Metrics

Learning outcomes

A graphical plan is not a verdict. It is evidence about a compiled strategy, and an actual plan adds runtime counters from one execution. ServiceHub operators need to distinguish logical operators from physical implementations, estimates from actual rows, plan cost from elapsed time, and compile-time warnings from runtime symptoms. They also need to remember a safety rule that is easy to forget: requesting an actual plan executes the statement.

Lab bootstrap for an independently runnable lesson

If you arrived directly at Lesson 2, run this idempotent setup. It creates the same deterministic skewed lab10.WorkOrderFact used throughout the chapter only when it is absent.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab10') IS NULL EXEC(N'CREATE SCHEMA lab10 AUTHORIZATION dbo;');IF OBJECT_ID(N'lab10.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab10.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab10_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (30000)           ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab10.WorkOrderFact(customer_id,region_code,status,priority,opened_at,closed_at,amount,notes)  SELECT CASE WHEN n <= 18000 THEN 1 ELSE 2 + n % 1999 END,         CASE WHEN n <= 21000 THEN 'N01' WHEN n <= 27000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 20 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 21000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n % 5=0 THEN DATEADD(minute,n+90,'2026-01-01T00:00:00') END,         CAST(20 + (n % 5000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 37=0 THEN REPLICATE('x',160) END  FROM n;  CREATE INDEX IX_lab10_RegionStatusOpened    ON lab10.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);  CREATE INDEX IX_lab10_Customer    ON lab10.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);END;GO

1. Estimated and actual plans answer different questions

An estimated plan is the optimizer's compiled strategy and does not contain runtime row counts or runtime warnings. An actual plan is the compiled strategy plus execution context and runtime information collected after execution. That means an actual plan for DELETE really deletes unless you deliberately protect the lab. The same is true when SET STATISTICS XML ON is used.

USE ServiceHubLab;GO-- Estimated: statement is compiled but not executed.SET SHOWPLAN_XML ON;GOSELECT customer_id,SUM(amount) AS total_amountFROM lab10.WorkOrderFactWHERE region_code='N01'GROUP BY customer_id;GOSET SHOWPLAN_XML OFF;GO-- Actual: statement EXECUTES and returns runtime Showplan XML.SET STATISTICS XML ON;GOSELECT customer_id,SUM(amount) AS total_amountFROM lab10.WorkOrderFactWHERE region_code='N01'GROUP BY customer_id;GOSET STATISTICS XML OFF;GO

In SSMS, the corresponding commands are “Display Estimated Execution Plan” and “Include Actual Execution Plan.” Both require the ability to compile/execute the statement and SHOWPLAN permission for referenced databases.

2. Read from the root question outward

Plan reading starts with the statement and its properties: estimated versus actual rows, memory grant, degree of parallelism, compile-time parameter values, warnings, and cardinality-estimation model version. Then trace the row flow through physical operators. A logical operation describes relational intent; a physical operation describes the implementation chosen, such as Nested Loops, Hash Match, Merge Join, Sort, Index Seek, Index Scan, Key Lookup, or Parallelism exchange.

SET STATISTICS IO ON;SET STATISTICS TIME ON;SELECT f.customer_id,COUNT_BIG(*) AS work_count,SUM(f.amount) AS total_amountFROM lab10.WorkOrderFact AS fWHERE f.region_code='N01'  AND f.status IN ('OPEN','ESCALATED')GROUP BY f.customer_idORDER BY total_amount DESC;SET STATISTICS TIME OFF;SET STATISTICS IO OFF;GO

Logical reads and CPU/elapsed output are local observations from this execution. They can change with cache warmth, concurrent activity, MAXDOP, host resources, and data distribution. Do not copy the numbers into a production SLO. Compare alternatives under controlled conditions.

3. Estimated-vs-actual rows are usually more useful than icon folklore

If an operator expects 10 rows but receives 100,000, downstream join choice, memory grant, parallelism, and lookup strategy may be inappropriate. The estimate mismatch does not automatically prove “bad statistics”; it can also arise from correlation, parameters, local variables, expressions, constraints, or model assumptions. Conversely, a plan can contain an Index Scan and still be the correct plan when much of the table is required.

DECLARE @region char(3)='E03';SELECT work_order_id,customer_id,status,opened_at,amountFROM lab10.WorkOrderFactWHERE region_code=@region  AND status='ESCALATED';GODECLARE @region char(3)='N01';SELECT work_order_id,customer_id,status,opened_at,amountFROM lab10.WorkOrderFactWHERE region_code=@region  AND status='OPEN';GO

Capture actual plans for both. Record Estimated Number of Rows and Actual Number of Rows at key operators. The data was deliberately skewed, but the exact plan shape remains an optimizer decision; the lesson never promises a seek, scan, hash, or loops join.

4. Memory grants, spills and parallelism require runtime context

Sorts and hash operations can request workspace memory. If the grant is too small, work can spill to tempdb; if too large, concurrency can suffer. Actual plans can expose grant properties and spill warnings. Parallel plans add exchange operators and per-thread runtime counters. Chapter 12 will go deeper into grants and tempdb; here the rule is to diagnose them from actual evidence rather than from a yellow warning triangle alone.

SET STATISTICS XML ON;GOSELECT region_code,status,customer_id,       SUM(amount) AS total_amount,       COUNT_BIG(*) AS row_countFROM lab10.WorkOrderFactGROUP BY region_code,status,customer_idORDER BY total_amount DESC;GOSET STATISTICS XML OFF;GO

On a 30,000-row local table this might remain entirely in memory. That is valid evidence. Do not artificially claim a spill occurred. If you later reproduce a spill with representative data, record the memory grant, requested/used memory, warning details, row width, data volume, concurrency, compatibility level, and host limits.

5. Wrong approach: get an actual plan for a destructive production statement “just to look”

Actual plan capture executes DML. In a disposable lab you can protect a demonstration with an explicit transaction and rollback; in production you should prefer existing Query Store/plan cache evidence, an estimated plan where useful, or a controlled reproduction.

BEGIN TRANSACTION;SET STATISTICS XML ON;UPDATE lab10.WorkOrderFactSET notes='chapter10-plan-probe'WHERE work_order_id BETWEEN 1 AND 3;SET STATISTICS XML OFF;SELECT work_order_id,notesFROM lab10.WorkOrderFactWHERE work_order_id BETWEEN 1 AND 3;ROLLBACK TRANSACTION;GOSELECT work_order_id,notesFROM lab10.WorkOrderFactWHERE work_order_id BETWEEN 1 AND 3;GO

The verification after rollback should show the original values. This proves the rollback boundary, not that every production DML can safely be profiled this way.

6. Production judgment

When diagnosing a regression, preserve the plan XML, query text, parameter values, compatibility level, Query Store state, statistics timestamps, and relevant runtime metrics. “Operator X is slow” is not a diagnosis. The operator may be processing unexpectedly many rows because the problem began earlier in the plan. The baseline remains SQL Server 2025 CU7 / compatibility 170 / Developer or Express; no paid feature is required.

Authoritative references

• Display and save execution plans — estimated, actual and live plan distinctions
• Display an actual execution plan — runtime plan semantics and permissions
• SET SHOWPLAN_XML — nonexecuting compile-time plan
• SET STATISTICS XML — runtime Showplan capture
• SQL Server 2025 build versions — CU/build servicing baseline
• SSMS 22 release notes — current client-tool baseline