Row Goals: Why TOP and EXISTS Change the Plan

Asking for one row can make a query do more work than asking for several. Row goals tell the optimizer to favor an early answer. That bet works when a match arrives quickly and hurts when the search keeps going.

Understand How Row Goals Estimate an Early Exit

Row goals are optimization choices based on needing fewer rows than the full estimate. TOP, EXISTS, and related constructs can create that choice. The optimizer evaluates paths that deliver an early result.

It doesn't guarantee that the first qualifying row is physically close. The eventual amount of work depends on the data and the available access paths.

I look for this behavior when a TOP query reads far more rows than it returns. Nested loops and repeated probes are useful for quick matches. They become costly when most probes fail.

A hash-oriented plan can cost more to start but less to search broadly. The optimizer's early-exit assumption changes that trade, making a small result an unexpectedly expensive request.

Create a Selective Search Example

Use a disposable database for the following tables. The generated values are test input, not a measured distribution from a customer system. An indexed product identity supports the relationship.

The filtered attribute deliberately provides another selectivity question. Replace that input with a representative distribution when testing your actual request. A tiny fixture explains syntax but doesn't force a harmful row goal.

Capture an actual plan for the TOP query. Look at reads, rows reaching each operator, and whether filters happen early or late. The result count alone hides the search.

A query can return one row after checking a large input. Don't call it efficient because the output grid contains a single line. The engine pays for rejected candidates too.

CREATE TABLE dbo.RowGoalProducts(ProductId int NOT NULL PRIMARY KEY, IsEligible bit NOT NULL);
CREATE TABLE dbo.RowGoalSales(SaleId int NOT NULL PRIMARY KEY, ProductId int NOT NULL);
INSERT dbo.RowGoalProducts VALUES (1,0),(2,1);
INSERT dbo.RowGoalSales VALUES (1,1),(2,1),(3,2);
SELECT TOP (1) s.SaleId
FROM dbo.RowGoalSales AS s
JOIN dbo.RowGoalProducts AS p ON p.ProductId = s.ProductId
WHERE p.IsEligible = 1
ORDER BY s.SaleId;

Find Row Goals in the Plan's Separate Estimates

In supported showplan output, EstimateRowsWithoutRowGoal exposes the estimate before the row goal adjusted it. Compare that attribute with the ordinary estimate on the affected operator. A difference shows where the optimizer expected early termination to reduce work.

The actual row counters then show what the selected path processed during execution. Those are separate pieces of the explanation.

I inspect the XML when the graphical property view doesn't expose enough detail. Search for EstimateRowsWithoutRowGoal and then locate the matching RelOp. An absent attribute isn't proof that every TOP behaves identically.

Showplan details depend on the engine and operator. Use the surrounding plan and the query's semantics too. One property is evidence, not a complete diagnosis on its own.

Compare the Targeted Hint

DISABLE_OPTIMIZER_ROWGOAL removes row goal adjustments associated with these constructs for the statement. It doesn't remove TOP from the result contract. The query still returns the requested limit.

SQL Server instead costs the plan without that early-exit adjustment. Compare the hinted plan with the original before considering a permanent change. The best alternative depends on the workload's real input ranges.

Use the same data and parameter values for each test. Keep STATISTICS IO and the actual plan available. A different join type isn't automatically an improvement.

Check work done, CPU, memory grants, and spills. A plan that wins on a rare no-match input can lose on common quick matches. Keep the comparison broad enough to represent the requests your application actually sends.

SET STATISTICS IO ON;
SELECT TOP (1) s.SaleId
FROM dbo.RowGoalSales AS s
JOIN dbo.RowGoalProducts AS p ON p.ProductId = s.ProductId
WHERE p.IsEligible = 1
ORDER BY s.SaleId
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));
SET STATISTICS IO OFF;

Test Row Goals With EXISTS and the No-Match Case

EXISTS asks whether any qualifying row exists. It can benefit from stopping after a match, without returning all matching detail. That logical benefit remains valuable.

The problem appears when the chosen access path has to perform extensive work before establishing existence or absence. Test both cases deliberately. A no-match request needs to finish the search rather than celebrate an early success.

The query below asks which products have at least one sale in the small sample. Run it with and without the statement hint for a larger representative dataset. Inspect the semi-join behavior rather than expecting a particular graphical icon.

The optimizer can transform the expression while preserving its meaning. Your review should follow the rows and predicates through that transformed plan.

SELECT p.ProductId
FROM dbo.RowGoalProducts AS p
WHERE EXISTS
(
    SELECT 1 FROM dbo.RowGoalSales AS s WHERE s.ProductId = p.ProductId
);

Fix the Access Path Before Freezing a Hint

A useful index can make early matching reliable. Put the relevant equality and filtering columns where the workload can use them. Check statistics for skew and relationships between filters.

An estimate that assumes qualifying rows are spread evenly can be wrong when they cluster at one end. Understanding that distribution gives you a better fix than blindly disabling an optimizer feature.

Which request searches longest before finding a match? Include that parameter in the review. Also test a value with no qualifying row.

Keep ORDER BY deterministic when TOP selects a business record. Removing it to get a faster plan changes the question. A quick arbitrary row isn't a substitute for the earliest eligible sale or the required latest transaction.

Preserve the Result While Reducing Work

Save the original query, actual plan, and representative test inputs. Compare the alternative under the same result contract. Retain a hint only with a documented reason and review conditions.

Recheck after an index or statistics change. Row goals aren't defects by themselves. Each one is a cost assumption that succeeds or fails depending on where the matches live.

Avoid measuring only elapsed time from a single run. Blocking and caching complicate that number. Reads and per-operator rows explain the search more directly. The server supplies the actual measurements.

Your task is to connect those measurements to the optimizer's early-exit assumption. TOP asks for less output. It doesn't sign a promise for less internal work.

Keep a no-match case in the final validation set. It exposes the full cost of proving absence. Include a common quick-match case beside it.

Those endpoints expose the trade. A useful early-exit design needs to handle more than a convenient first candidate.

Related reading on this blog: Top 1 and Index Scan and TOP vs. TOP PERCENT: Hidden Costs: SQL in Sixty Seconds 206.

A row goal is not a limit on work, it is an estimate about finding an answer early.

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