SQL Server can build a temporary index while executing a query. An eager index spool stores input rows and organizes them for repeated seeks. That can be better than repeated scans, while still revealing a useful tuning opportunity.
Recognize the Work an Eager Index Spool Performs
An Index Spool differs from a simple table spool because it builds an indexed internal structure. Its seek predicates describe how later consumers find rows. An eager logical spool populates its input before returning the staged rows needed by its parent.
I inspect the spool's input and consumer together. The icon alone does not explain whether the staging saves work or wastes it. A nested loops or APPLY pattern can benefit from building one searchable structure rather than scanning an unindexed table for every outer row.
That internal structure lives for the query's execution, not as a permanent user index. Later executions can repeat its construction. Rebinds and rewinds help explain whether the current execution rebuilds or reuses staged data. Do not assume every spool population happens exactly once without inspecting those properties.
Build a Correlated Lookup Example
Use a disposable database and fresh demonstration names. The orders table has a primary key but no customer-date access path. The customer table supplies repeated correlated lookups. SQL Server 2022 with compatibility level 160 supplies the GENERATE_SERIES inputs used here.
CREATE TABLE dbo.SpoolCustomersDemo(CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE dbo.SpoolOrdersDemo
(OrderID int NOT NULL PRIMARY KEY,CustomerID int NOT NULL,
OrderDate date NOT NULL,Amount decimal(12,2) NOT NULL);
INSERT dbo.SpoolCustomersDemo
SELECT value FROM GENERATE_SERIES(1,1000,1);
INSERT dbo.SpoolOrdersDemo
SELECT value,(value%1000)+1,
DATEADD(day,value%365,CONVERT(date,'20250101')),value%100+1
FROM GENERATE_SERIES(1,50000,1);The population sizes define an input scenario, not a promised measured plan. The optimizer can choose another implementation depending on version, estimates, and costs. Preserve that observation if your server does not produce the expected operator. Do not apply undocumented switches just to manufacture a screenshot.
Capture the Eager Index Spool in the Actual Plan
Enable the actual execution plan and run the lookup. TOP one with a stable ordering requests the latest order for each customer. The secondary OrderID ordering resolves equal order dates. OUTER APPLY preserves a customer even when no order matches.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT c.CustomerID,o.OrderID,o.OrderDate,o.Amount
FROM dbo.SpoolCustomersDemo c
OUTER APPLY
(
SELECT TOP(1) OrderID,OrderDate,Amount
FROM dbo.SpoolOrdersDemo o WHERE o.CustomerID=c.CustomerID
ORDER BY OrderDate DESC,OrderID DESC
) o
ORDER BY c.CustomerID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Inspect the plan's inner lookup side for an Index Spool. Record its logical operation and actual row counts. Worktable reads can support the investigation, but that STATISTICS IO line does not identify one operator by itself. Sorts and other internal structures can also contribute.
If the plan uses repeated scans and sorts instead, that is still useful evidence of the missing access path. The comparison remains about repeated lookup work and a supporting index. Report the actual operators rather than describing an unobserved spool as a measured fact.
Read Eager Index Spool Seek Keys and Output
Open the spool's properties in SSMS. Seek Predicates show the lookup keys, while Output List shows data needed by the parent. Compare those fields with the original predicate and projection. A candidate permanent index begins with a justified key order and includes the required returned values.
A spool can contain columns or expressions that do not map directly to one simple permanent index. Its internal purpose can also include plan transformations beyond a missing index. Treat its definition as a clue, not a command to copy every output column into an enormous index.
I review existing indexes before creating another one. An existing index with a compatible leading key can already cover part of the need. Which specific seek and ordering requirement remains unsupported? That question prevents a spool investigation from generating redundant indexes with almost identical definitions.
Compare a Supporting Permanent Index
For this query, customer equality followed by descending order date and OrderID matches the lookup contract. Amount is included because the caller returns it but does not filter or order by it. Create this index only on the disposable example before rerunning the same query.
CREATE INDEX IX_SpoolOrdersDemo_CustomerLatest
ON dbo.SpoolOrdersDemo(CustomerID,OrderDate DESC,OrderID DESC)
INCLUDE(Amount);
SET STATISTICS IO ON;
SELECT c.CustomerID,o.OrderID,o.OrderDate,o.Amount
FROM dbo.SpoolCustomersDemo c
OUTER APPLY
(
SELECT TOP(1) OrderID,OrderDate,Amount FROM dbo.SpoolOrdersDemo o
WHERE o.CustomerID=c.CustomerID
ORDER BY OrderDate DESC,OrderID DESC
) o;
SET STATISTICS IO OFF;Compare plans, total reads, and result rows. A seek-based lookup can replace temporary construction, but the optimizer retains the freedom to choose another safe plan. Do not force the index simply to make the comparison look successful. The final application needs the efficient unforced behavior under representative inputs.
Account for Permanent Write Cost
A permanent index consumes storage and requires maintenance for inserts, deletes, and updates affecting its columns. The benefit therefore depends on query frequency and write activity. A report run once a month can justify a different decision from a lookup executed continuously.
The index's included columns matter too. Adding a wide payload can save lookups while increasing leaf-page size and write cost. Keep the design narrow enough to serve the actual query. Review overlapping indexes and the complete workload before proposing consolidation or removal.
A temporary spool pays its construction cost during execution. A permanent index pays ongoing maintenance cost outside that query. Compare both sides rather than treating persistence as automatically superior. SQL Server's temporary librarian can be doing sensible work with the shelves it was given.
Consider a Set-Based Rewrite
A ranking expression can find the latest row for every customer in one relational pass. It offers another candidate when a permanent index cannot be added. The chosen plan still depends on input sizes and indexes, so test it rather than assuming one syntax always wins.
WITH Ranked AS
(
SELECT CustomerID,OrderID,OrderDate,Amount,
ROW_NUMBER() OVER
(PARTITION BY CustomerID ORDER BY OrderDate DESC,OrderID DESC) AS rn
FROM dbo.SpoolOrdersDemo
)
SELECT c.CustomerID,r.OrderID,r.OrderDate,r.Amount
FROM dbo.SpoolCustomersDemo c
LEFT JOIN Ranked r ON r.CustomerID=c.CustomerID AND r.rn=1;This rewrite ranks the orders considered by its input. A narrow customer request can make correlated seeks more attractive than ranking the entire orders table. Preserve the scope of the report when testing. A broad report and a selective lookup do not require the same winning expression.
Measure the Complete Comparison
Use the same data, predicates, and output contract across alternatives. Include customers without orders and ties on order dates. Check result equivalence before reading elapsed duration. Record compile conditions and repeated execution measurements without clearing a shared server's cache.
Retain the original spool properties and candidate index definition with the review. A useful conclusion identifies why work changed and what ongoing cost the chosen design adds. That evidence makes the plan understandable even when a later optimizer version chooses a different operator tree.
An eager index spool can save repeated scanning even while its construction remains expensive. Compare the whole workload before replacing an eager index spool with permanent storage.
Related reading on this blog: Execution Plans and Indexing Strategies: Quick Guide and AI Execution Plan Analysis: I Gave It My Plan and Asked What Was Wrong.
An eager index spool is not an automatic plan defect, it is temporary indexing whose repeated cost deserves comparison with supported alternatives.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
