Estimated vs Actual, and Why Actual Matters

The optimizer does not know your data. It knows statistics about your data: histograms summarizing the distribution of values in indexed and sampled columns. From those it estimates how many rows each step will produce, and from those estimates it picks join types, memory grants, and index strategies.

Every choice in a plan follows from a row estimate. If the estimate is wrong, the plan is wrong — not slightly suboptimal, but built for a different problem.

The single most useful signal in a plan

On an actual execution plan, every operator shows two numbers:

Estimated Number of Rows — what the optimizer predicted.
Actual Number of Rows — what really came out.

Compare them. That comparison is the fastest diagnosis in query tuning.

Estimated 1, actual 1 — fine.
Estimated 1,200, actual 1,050 — fine. Estimates are never exact.
Estimated 1, actual 940,000 — this is the problem. Everything downstream was planned for one row.

An estimate of 1 row leading to nested loops leading to 940,000 executions of an inner seek is the classic slow-report shape. The plan looks cheap. It runs for a minute.

The visual name for this is the "fat arrow" pattern: a thin arrow the optimizer expected, drawn thin, while the tooltip reveals a huge actual count. Newer SSMS versions draw arrow width from actual rows on an actual plan, so a plan full of thick arrows out of a step the optimizer thought was tiny is visible at a glance.

Reading it in the XML

For a large plan, hunting through the graphic is tedious. The XML holds EstimateRows on each RelOp and ActualRows inside RunTimeInformation. In the plan XML, look for operators where the ratio is large in either direction.

To reproduce the estimate/actual comparison without opening the plan at all, this works on any statement:

SET STATISTICS PROFILE ON;

SELECT o.OrderId, o.OrderDate, c.CustomerName
FROM   dbo.Orders   AS o          -- placeholder tables
JOIN   dbo.Customer AS c ON c.CustomerId = o.CustomerId
WHERE  o.OrderDate >= '2026-01-01';

SET STATISTICS PROFILE OFF;

The result grid gives you Rows (actual) and EstimateRows side by side, one line per operator, which is easy to scan and easy to paste into a ticket.

For a query that is currently running and that you cannot wait for, SQL Server 2016 and later expose live per-operator counts:

SELECT  qp.node_id,
        qp.physical_operator_name,
        qp.row_count,               -- actual rows so far
        qp.estimate_row_count,
        qp.estimated_read_row_count
FROM sys.dm_exec_query_profiles AS qp
WHERE qp.session_id = 57            -- placeholder: the slow session's spid
ORDER BY qp.node_id;

Watching row_count climb past estimate_row_count by three orders of magnitude, live, is a very direct answer to "why is this taking so long."

Why estimates go wrong

The common causes, roughly in order of how often they explain a slow report:

Stale statistics. The table grew; the histogram still describes last quarter. Module 5.
A function wrapping a column in the WHERE clause. The optimizer cannot use the histogram on YEAR(OrderDate) because it has no statistics on that expression, so it guesses. Module 4.
Implicit conversion for the same reason — the comparison is no longer against the column as stored. Module 4.
Table variables. Historically estimated at 1 row regardless of contents. SQL Server 2019+ has deferred compilation for table variables, which fixes many cases, but only under a recent compatibility level.
Multi-statement table-valued functions. Fixed guess (100 rows in 2014+, 1 before). SQL Server 2017+ interleaved execution improves this for some shapes.
Local variables in a WHERE clause. The optimizer does not know the value at compile time and falls back to a density guess. Module 5 uses this deliberately as a tool.
Correlated predicates. WHERE City = 'Sacramento' AND State = 'CA' — the optimizer largely treats these as independent, so it multiplies selectivities and underestimates badly.
Parameters far outside the histogram. A date range beyond the last statistics update.

What to do about a bad estimate

Fixing the estimate is almost always better than fixing the plan. In order of preference:

Update statistics and see if the estimate corrects itself. Cheapest possible test.
Remove whatever blinds the optimizer — unwrap the function, fix the parameter type, replace the table variable with a temp table (temp tables get real statistics; table variables largely do not).
Give it better information — a filtered statistic, or an index whose leading column matches the predicate, produces a finer histogram exactly where you need it.
Only then consider hints (OPTION (RECOMPILE) so the actual parameter values are known at compile time is usually the mildest and most effective).

The reason for that order: a hint locks in today's answer. A corrected estimate keeps being right as the data changes.

The short version

On an actual plan, compare estimated rows to actual rows at every operator. A big divergence means the optimizer built the plan for a different amount of data than it got, and that explains almost every bad join choice and undersized memory grant you will meet. Fix the estimate — stale stats, a function on a column, a parameter type mismatch — before reaching for a hint, because a corrected estimate stays correct as the data grows.