The most useful clue in a slow plan can be a row estimate that missed the workload. Compare estimated vs actual rows before blaming the final operator, because the mistake can begin much farther upstream.
Get a Plan That Includes Execution Evidence
An estimated execution plan shows what SQL Server expects before running the statement. An actual execution plan includes the executed plan and runtime information. You need the latter to compare predictions with the rows the operators actually processed.
In SSMS, enable Include Actual Execution Plan with Ctrl+M, then execute a safe representative statement. That action runs the query. An actual plan for an UPDATE also runs the UPDATE, so use a controlled test copy for write operations. Obtaining an estimated plan does not execute the statement.
I start with the actual plan and the relevant parameters together. A plan captured for a different customer or date range can explain the wrong workload. Keep the query text, parameter values, time window, and business symptom beside the captured evidence.
Create a Skewed Teaching Input
The scratch example creates a table where one category receives most generated identifiers. The distribution is deliberately uneven, so a typical-category estimate does not necessarily describe every category. The GENERATE_SERIES input count is part of the teaching setup, not a measured production result.
SQL Server 2022 introduced GENERATE_SERIES and requires compatibility level 160 or higher. Run this once in a scratch database with that support. The nonclustered index covers the category filter and amount expression. UPDATE STATISTICS supplies a controlled starting point for the example.
Use your own output to inspect the distribution. Do not assume that every demonstration will produce a large estimation gap on every current engine. Recent optimizer features and compilation choices affect the plan. The example provides a place to practice reading the evidence, not a guaranteed failure benchmark.
CREATE TABLE dbo.CategorySales
(
RowID int NOT NULL PRIMARY KEY,
CategoryID int NOT NULL,
Amount decimal(12,2) NOT NULL
);
INSERT dbo.CategorySales
SELECT value, CASE WHEN value <= 9950 THEN 1 ELSE 2 END, CONVERT(decimal(12,2), 10.00)
FROM GENERATE_SERIES(1, 10000, 1);
CREATE INDEX IX_CategorySales_Category
ON dbo.CategorySales (CategoryID) INCLUDE (Amount);
UPDATE STATISTICS dbo.CategorySales IX_CategorySales_Category WITH FULLSCAN;
SELECT CategoryID, COUNT_BIG(*) AS CategoryRows
FROM dbo.CategorySales
GROUP BY CategoryID
ORDER BY CategoryID;Read Estimated vs Actual Rows on the Data Access Operator
Run the next SELECT with the actual plan enabled. Open the index operator's properties. Compare Estimated Number of Rows with the actual row information. Also inspect rows read when available, since reading many rows and returning few reveals a different kind of work.
A predicate can be an access predicate or a residual filter. An index seek can still read more rows than its final output suggests. Do not stop at the operator's icon and declare success. Look at how much input the operator examined to produce the requested result.
Which operator first returns a population different from its prediction? Begin near the table access, then follow the row flow toward joins and aggregates. The final SELECT's total is a symptom. The first divergent input is usually a more useful investigation point.
SELECT SUM(Amount) AS CategoryAmount
FROM dbo.CategorySales
WHERE CategoryID = 2;Compare Estimated vs Actual Rows in the Same Scope
Nested loops and other operators can execute an inner input repeatedly. A total actual row count across many executions is not directly comparable to an estimate for one execution. Inspect execution counts and the properties' units before calculating a dramatic mismatch ratio.
Parallel plans also distribute runtime observations across threads. Use the plan's aggregate properties deliberately and inspect thread details when necessary. A zero estimate or zero actual output needs explanation rather than a divide-by-zero ratio dressed up as analysis.
I check those units before calling an estimate wildly wrong. The plan can be confusing without being incorrect. Compare rows per execution with rows per execution, and totals with totals. Then the remaining gap has an interpretable meaning.
Follow the Estimated vs Actual Rows Gap Into Joins
Underestimating an input can favor an access or join strategy poorly matched to the real volume. A repeated lookup becomes expensive when the expected small set becomes large. A sort or hash operation can also receive a memory grant too small for its actual input.
Overestimation can reserve more memory than useful and reduce concurrency. Join choice, grant size, row width, and available resources all matter. A row mismatch is a strong clue, but not every mismatch causes a slow plan. Check its downstream consequences before choosing a fix.
Inspect spill warnings and granted-versus-used memory in the actual plan where available. A spill also has causes beyond row count, including row-size errors and memory constraints. Follow the evidence rather than turning every warning into the same statistics update script.
Inspect Statistics for the Relevant Predicate
Statistics describe distributions the optimizer uses during estimation. Check whether the relevant statistics exist, when they were updated, how they were sampled, and how much data changed afterward. A recent timestamp does not guarantee the histogram represents every important skew or column relationship.
The next query uses documented columns from sys.dm_db_stats_properties. It shows the statistics associated with the teaching table. The histogram command then exposes the category index's distribution. Review the predicate's actual range against that information instead of refreshing every statistic without a reason.
SELECT s.name AS StatisticsName, s.auto_created, s.user_created,
p.last_updated, p.[rows] AS StatisticsRows,
p.rows_sampled, p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.CategorySales')
ORDER BY s.stats_id;
DBCC SHOW_STATISTICS (N'dbo.CategorySales', N'IX_CategorySales_Category')
WITH HISTOGRAM;Test Parameters and Expressions Separately
A local variable can hide the specific filter value from ordinary compilation. A cached parameterized plan can also reflect a previously compiled parameter case. Those situations need different explanations from missing or stale statistics. Recent versions provide additional parameter-sensitive behavior, so inspect the actual chosen plan.
The next pair contrasts a local-variable query with an OPTION (RECOMPILE) version. Recompilation can expose the current value to optimization, but adds compilation work. It is a diagnostic comparison here, not a universal recommendation for every production statement.
DECLARE @CategoryID int = 2;
SELECT SUM(Amount) AS CategoryAmount
FROM dbo.CategorySales
WHERE CategoryID = @CategoryID;
SELECT SUM(Amount) AS CategoryAmount
FROM dbo.CategorySales
WHERE CategoryID = @CategoryID
OPTION (RECOMPILE);Functions and implicit conversions on filtered columns can also complicate estimation and access. Compare the next expression with the original typed equality. Check the actual properties, not an assumed scan result. Matching parameter types to column types removes an avoidable source of confusion.
SELECT SUM(Amount) AS CategoryAmount
FROM dbo.CategorySales
WHERE CONVERT(varchar(11), CategoryID) = '2';Older table-variable behavior is another known estimation trap. Recent deferred compilation improves some situations, but it does not give every table variable the same distribution statistics as a temporary table. Inspect the version, compatibility level, compilation timing, and workload before repeating an old fixed-row rule.
Fix the Earliest Relevant Cause
Choose a fix from the identified cause: a justified statistics update, a clearer predicate, appropriate parameter handling, or a better temporary data structure. Re-run representative cases, including both common and uncommon values. One improved parameter case does not certify the whole workload.
When reviewing estimated vs actual rows, keep the operator where divergence begins and the downstream cost together. Save the before and after plans and actual measurements. That keeps the investigation focused on useful improvements instead of making every estimate numerically perfect.
Estimated vs actual rows gives the first useful reading path through a plan. Follow the wrong prediction to its cause, then check whether correcting it improves the business operation. The goal is a better execution, not a prettier tooltip.
Related reading on this blog: Why Query Cost Percentages in a Plan Mislead You and SQL Server 2022: Cardinality Estimation (CE) Feedback for Performance.
A row-estimate gap is not the final diagnosis, it is the place to start tracing the plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
