Estimated Plan With a Temp Table: Why It Guesses One Row

An estimated plan is a forecast, and for a temp table it forecasts one row. The real run can read forty thousand. If you tune from the forecast, you tune the wrong query. Let’s put the two plans side by side and see exactly where they split.

Two Kinds of Plan

SQL Server Management Studio gives you two buttons. Display Estimated Execution Plan (Ctrl+L) compiles your code and shows the plan without running anything. Include Actual Execution Plan (Ctrl+M) runs the code and returns the plan with real numbers attached.

The estimated plan is fast and safe. It never changes data and never waits for a long query. The actual plan costs a full run. In return, it tells you what happened: actual rows, executions, time and memory for each operator. If you want a refresher on reading the two numbers, my post on estimated vs actual rows covers it.

Build the Test

I ran everything here on SQL Server 2025. The script creates a database with 200,000 orders.

CREATE DATABASE SqlEstimatedPlanDemo;
GO
USE SqlEstimatedPlanDemo;
GO
CREATE TABLE dbo.Orders
(
    OrderID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT s.value % 2000 + 1, s.value % 500 + 10.50
FROM GENERATE_SERIES(1, 200000) AS s;
GO

Now the code we want to study. It copies the big orders into a temp table and counts them. This pattern is everywhere: reports, cleanup jobs and stored procedures that stage data before the real work.

CREATE TABLE #BigOrders (OrderID int PRIMARY KEY, Amount decimal(10,2) NOT NULL);
INSERT INTO #BigOrders (OrderID, Amount)
SELECT OrderID, Amount FROM dbo.Orders WHERE Amount > 400;
SELECT COUNT(*) AS BigOrders FROM #BigOrders;

Select these four lines and press Ctrl+L for the estimated plan. Then press Ctrl+M and run them for the actual plan. Drop the temp table between runs, or open a new query window.

Two Plans, Two Numbers

You can find old advice that says this request fails with Msg 208, invalid object name. The reason given is that the temp table doesn’t exist yet. On SQL Server 2025 it didn’t fail. The estimated plan came back with a plan for the INSERT and a plan for the SELECT. The trouble is in the numbers.

The first row is fine. dbo.Orders is a real table with statistics, so both plans make the same good guess. The second row is the problem. The estimated plan thinks the temp table holds one row. The real run read 44,000.

Why the Forecast Says One

An estimated plan compiles every statement in one pass, before anything runs. At that moment the temp table is empty, or doesn’t exist at all. The optimizer has no statistics to read, so it uses its minimum guess of one row.

The real run works differently. SQL Server runs the INSERT first. When the SELECT comes up, the temp table has changed by far more than its recompile threshold. So SQL Server compiles the SELECT again, this time with 44,000 rows in the table. That second compile is the one in the actual plan.

I tried three more versions to be sure. Creating the temp table first, in its own batch, gave the same one-row guess, because the table was still empty. A table variable gave one row too. So did the same code inside a stored procedure. Since SQL Server 2019, table variable deferred compilation fixes the count at the first real compile. An estimated plan never gets that far.

Why It Matters

With one row, everything after the temp table looks cheap. In a bigger procedure, a join to #BigOrders would show a nested loop with one tiny lookup. A sort would show almost no memory. The real run brings 44,000 rows, and the plan you studied isn’t the plan that ran.

This is how a tuning session goes wrong. Someone reads the estimated plan, sees nothing expensive and moves on. The slow part was hiding behind a guess.

What the Actual Plan Adds

Comparing the two plans is a comparison of metadata as much as shape. The actual plan adds what the estimated plan can’t know:

• Actual rows and actual executions for each operator.
• Elapsed time and CPU time for each operator.
• The memory grant the query used, not only the one it asked for.
• Runtime warnings, such as a sort or hash that spilled to tempdb.

You could argue that the actual plan should always win. Fair point, but it has a price. It runs the code. An actual plan of a DELETE deletes rows, and an actual plan of a two-hour report takes two hours. The estimated plan stays useful. Use it to check the shape and the index choices on permanent tables. Don’t trust its row counts after a temp table or table variable is filled.

The Middle Ground: Live Query Statistics

There is a third option in SSMS. Include Live Query Statistics shows the plan while the query runs. Row counts move along the arrows as data flows, and each operator shows how far it has gotten. It still runs the code, so the same warning applies to deletes and long reports.

Its strength is the slow query you can’t wait for. You can watch where the rows pile up after the first few minutes, then cancel. You don’t need the full run to see which operator is the problem. For our temp table, it would show the real 44,000 rows reaching the scan, long before the query ends.

A Simple Rule

When the code fills a temp table, get the actual plan. If the query is too long or too risky to run, look for the plan of a real run instead. My post on getting the last known actual execution plan shows how to read one from the plan cache. In my performance work, a one-row estimate after a temp table is the first number I distrust.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlEstimatedPlanDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlEstimatedPlanDemo;

An estimated plan is not wrong about a temp table, it is early.

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