NO_PERFORMANCE_SPOOL: Compare the Work Before Keeping the Hint

NO_PERFORMANCE_SPOOL is a controlled comparison, rather than proof that a visible spool should disappear. Reusable work can save repeated effort. Measure the work below the operator before deciding whether its removal helps.

Understand what NO_PERFORMANCE_SPOOL changes

The hint is available from SQL Server 2016. It does not remove spools that are required to preserve valid update semantics. Therefore, the hint is not a promise that every spool disappears. Apply it to a specific query after understanding the plan.

A performance spool can retain intermediate work for later requests. Removing it may expose repeated scans, sorts or other child operations. Fewer worktable reads do not automatically mean less total work. Compare the full execution, rather than one operator’s presence.

Use repeated keys and preserve unmatched rows

The setup below creates 1,000 outer rows across 50 repeated keys. One extra outer row has no matching inner key. The inner table contains 5,000 rows. Each match asks for the highest V within that key.

DROP TABLE IF EXISTS #Outer, #Inner, #BaseResult, #HintResult, #IndexResult;
CREATE TABLE #Outer (OuterId int NOT NULL PRIMARY KEY, K int NOT NULL);
CREATE TABLE #Inner (K int NOT NULL, V int NOT NULL);
CREATE TABLE #BaseResult (OuterId int NOT NULL PRIMARY KEY, K int NOT NULL, HighestV int NULL);
CREATE TABLE #HintResult (OuterId int NOT NULL PRIMARY KEY, K int NOT NULL, HighestV int NULL);
CREATE TABLE #IndexResult (OuterId int NOT NULL PRIMARY KEY, K int NOT NULL, HighestV int NULL);

WITH D(n) AS (SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS X(n)),
N(n) AS (SELECT 1+a.n+10*b.n+100*c.n FROM D a CROSS JOIN D b CROSS JOIN D c)
INSERT #Outer SELECT n, (n-1)%50 FROM N;
INSERT #Outer VALUES(1001,999);

WITH D(n) AS (SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS X(n)),
N(n) AS (SELECT 1+a.n+10*b.n+100*c.n+1000*d.n FROM D a CROSS JOIN D b CROSS JOIN D c CROSS JOIN D d)
INSERT #Inner SELECT (n-1)%50, n FROM N WHERE n<=5000;

Every outer row has a unique OuterId. That identifier preserves repeated-key multiplicity during correctness checks. OUTER APPLY preserves the unmatched outer row with a NULL value. Replacing it with CROSS APPLY would change the contract.

INSERT #BaseResult (OuterId,K,HighestV)
SELECT o.OuterId,o.K,a.V
FROM #Outer AS o
OUTER APPLY
(
    SELECT TOP(1) i.V FROM #Inner AS i
    WHERE i.K=o.K ORDER BY i.V DESC
) AS a;

The highest value is unique within every matching key in this example. Real data may contain ties or require additional returned columns. Include a deterministic tie-breaker when the business rule demands one. Keep that rule identical across every comparison.

The tested baseline contains both a lazy index spool and an eager index spool. The inner table scan executes once and reads 5,000 rows. The Top N Sort executes 51 times. These observations describe this exact example, rather than every similar query.

Change only the optional spool decision first

The second statement keeps the input, correlation, ordering and returned columns unchanged. It adds the query hint to the comparison. The destination table differs only because the example keeps both results for verification.

INSERT #HintResult (OuterId,K,HighestV)
SELECT o.OuterId,o.K,a.V
FROM #Outer AS o
OUTER APPLY
(
    SELECT TOP(1) i.V FROM #Inner AS i
    WHERE i.K=o.K ORDER BY i.V DESC
) AS a
OPTION(NO_PERFORMANCE_SPOOL);

Enable Include Actual Execution Plan before running these statements. Read the operators beneath any spool, their execution counts and rows processed. If no optional spool appears initially, record that observation. Do not invent a removal that the plan never shows.

In the tested hinted plan, the lazy spool disappears but the eager spool remains. The inner table scan still executes once. However, the Top N Sort executes 1,001 times. The remaining eager spool records 100,000 rows read across its executions.

Both variants record 11 logical reads against the inner table. That shared scan count does not explain all their downstream work. Inspect the sort and remaining spool before drawing a conclusion. These counters do not establish a universal runtime ranking.

The hinted plan also reports an excessive memory grant warning. Its recorded request and grant are 1,024 KB, while maximum use is 16 KB. The plan reports no spill. That warning describes this execution, rather than every hinted query.

Compare an access path that supports the request

The third comparison adds an index only to the inner table. Its leading key supports the equality condition. Descending V aligns with the requested highest value. That comparison runs without the spool hint.

CREATE INDEX IX_Inner_K_V ON #Inner(K,V DESC);

INSERT #IndexResult (OuterId,K,HighestV)
SELECT o.OuterId,o.K,a.V
FROM #Outer AS o
OUTER APPLY
(
    SELECT TOP(1) i.V FROM #Inner AS i
    WHERE i.K=o.K ORDER BY i.V DESC
) AS a;

This index can change the useful work beneath TOP. Whether the optimizer chooses a particular seek or scan remains an observed plan choice. Additional returned columns can introduce lookup requirements. Wider indexes also add storage and maintenance costs.

The tested indexed plan contains neither spool nor sort. Its inner index seek executes 1,001 times and reads 1,000 rows. The unmatched outer row contributes no inner row. The recorded inner logical reads are 2,002, despite the simpler access path.

Verify the returned rows before comparing work

The last block below compares baseline and hinted results in both directions. It repeats that comparison against the indexed version. It also counts the returned rows and the unmatched row. A faster query that changes those results is not the same experiment.

The intended result is 1,001 rows, including one unmatched row, in every variant. Performance observations depend on the tested server and actual plan. This demonstration provides no universal runtime ranking. Keep measured findings tied to the exact data and executed statements.

SELECT 'Baseline' AS variant, COUNT(*) AS returned_rows,
       SUM(CASE WHEN HighestV IS NULL THEN 1 ELSE 0 END) AS unmatched_rows
FROM #BaseResult
UNION ALL
SELECT 'Spool hint', COUNT(*), SUM(CASE WHEN HighestV IS NULL THEN 1 ELSE 0 END) FROM #HintResult
UNION ALL
SELECT 'Supporting index', COUNT(*), SUM(CASE WHEN HighestV IS NULL THEN 1 ELSE 0 END) FROM #IndexResult;

SELECT
 (SELECT COUNT(*) FROM (SELECT * FROM #BaseResult EXCEPT SELECT * FROM #HintResult) AS x) AS BaseNotInHint,
 (SELECT COUNT(*) FROM (SELECT * FROM #HintResult EXCEPT SELECT * FROM #BaseResult) AS x) AS HintNotInBase,
 (SELECT COUNT(*) FROM (SELECT * FROM #BaseResult EXCEPT SELECT * FROM #IndexResult) AS x) AS BaseNotInIndex,
 (SELECT COUNT(*) FROM (SELECT * FROM #IndexResult EXCEPT SELECT * FROM #BaseResult) AS x) AS IndexNotInBase;

DROP TABLE #IndexResult, #HintResult, #BaseResult, #Inner, #Outer;

Measure the entire experiment

If a spool exists, inspect its child work as well as its own counters. Capture CPU, elapsed time and logical reads through an approved measurement method. Separate compilation effects from repeated execution observations. Keep the same result contract and representative data.

None of this clears caches, resets counters or changes server configuration. It creates and removes only temporary tables. Repeating the experiment with distinct keys tests a different reuse pattern. Document changed inputs before comparing those results.

Keep every finding tied to the exact data and plans you tested.

A hint is not a fix, it is an experiment you judge by the total work.

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