Finding Eager Index Spools in the Plan Cache

2026-07-23 · by Hannah Vernon · in performance

Somewhere in your plan cache right now, there is probably a query quietly building an index every single time it runs. Not once. Every execution. It builds the index, uses it, and throws it away, like a carpenter who constructs a new ladder each morning and burns it every night.

Illustration of a woman carpenter building a wooden ladder at dawn while the charred remains of yesterday's identical ladder smolder in a fire pit behind her

That is an eager index spool, and the frustrating part is that SQL Server never files a missing index request for it. The optimizer already solved the problem, after all. It just solved it in the most expensive way possible.

What an Eager Index Spool Actually Is

When the optimizer needs to look up rows on the inner side of a nested loops join and no suitable index exists, it has a few options. It can scan the table on every iteration of the loop, which gets ugly fast. Or it can build itself a temporary index in tempdb, seek against that for the rest of the query, and discard it when the query finishes.

That second option shows up in the execution plan as an Index Spool with the logical operation Eager Spool. “Eager” means the spool is fully populated before the first row flows out of it: SQL Server reads every qualifying row from the source table, builds the index, and only then starts the join.

The optimizer only chooses this when it estimates the spool is cheaper than the alternatives. And to be fair to the optimizer, it usually is cheaper than scanning the inner table ten thousand times. But “cheaper than the worst option” is not the same as “cheap.” You are paying for an index build, in tempdb, on every execution, usually single-threaded, and the resulting index benefits exactly one query execution before it evaporates.

Here is the kicker: because the optimizer considers the problem solved, eager index spools do not generate missing index suggestions. The missing index DMVs stay silent. The green hint in the graphical plan never appears. If you rely on sys.dm_db_missing_index_details to tell you what to index, this entire class of problem is invisible to you.

Building One on Purpose

Rather than ask you to take my word for it, let’s build a spool in captivity. Everything below was run against a SQL Server 2019 (CU32) Developer Edition instance, and I re-ran the whole demo against SQL Server 2025 (RTM) with identical results: same spool, same plan shape, same fix. The scripts are self-contained, so you can follow along.

First, a demo database with 20,000 customers and 2,000,000 orders. Note what is missing: the only index on orders is the clustered primary key on order_id. The customer_id column is deliberately unindexed – that is the hole the optimizer will fill with a spool.

USE [master];

IF DB_ID(N'spool_demo') IS NULL
BEGIN
    CREATE DATABASE [spool_demo];
END;
GO

USE [spool_demo];
GO

CREATE TABLE [dbo].[customers]
(
      [customer_id]   int           NOT NULL IDENTITY(1, 1)
    , [customer_name] varchar(50)   NOT NULL
    , CONSTRAINT [pk_customers] PRIMARY KEY CLUSTERED ([customer_id])
);

CREATE TABLE [dbo].[orders]
(
      [order_id]     int      NOT NULL IDENTITY(1, 1)
    , [customer_id]  int      NOT NULL
    , [order_date]   date     NOT NULL
    , [amount]       money    NOT NULL
    , [filler]       char(80) NOT NULL DEFAULT ('x')
    , CONSTRAINT [pk_orders] PRIMARY KEY CLUSTERED ([order_id])
);
GO

/* 20,000 customers */
INSERT INTO [dbo].[customers]
(
    [customer_name]
)
SELECT TOP (20000)
    CONCAT(N'customer ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
FROM
    [sys].[all_columns] AS [ac1]
CROSS JOIN
    [sys].[all_columns] AS [ac2];
GO

/* 2,000,000 orders spread across the customers */
INSERT INTO [dbo].[orders]
(
      [customer_id]
    , [order_date]
    , [amount]
)
SELECT TOP (2000000)
      (ABS(CHECKSUM(NEWID())) % 20000) + 1
    , DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 1461, CONVERT(date, '2022-01-01', 120))
    , CONVERT(money, (ABS(CHECKSUM(NEWID())) % 100000) / 100.0)
FROM
    [sys].[all_columns] AS [ac1]
CROSS JOIN
    [sys].[all_columns] AS [ac2];
GO

USE [master];

IF DB_ID(N'spool_demo') IS NULL

BEGIN

CREATE DATABASE [spool_demo];

END;

USE [spool_demo];

CREATE TABLE [dbo].[customers]

(

[customer_id] int NOT NULL IDENTITY(1, 1)

, [customer_name] varchar(50) NOT NULL

, CONSTRAINT [pk_customers] PRIMARY KEY CLUSTERED ([customer_id])

);

CREATE TABLE [dbo].[orders]

(

[order_id] int NOT NULL IDENTITY(1, 1)

, [customer_id] int NOT NULL

, [order_date] date NOT NULL

, [amount] money NOT NULL

, [filler] char(80) NOT NULL DEFAULT ('x')

, CONSTRAINT [pk_orders] PRIMARY KEY CLUSTERED ([order_id])

);

/* 20,000 customers */

INSERT INTO [dbo].[customers]

(

[customer_name]

)

SELECT TOP (20000)

CONCAT(N'customer ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))

FROM

[sys].[all_columns] AS [ac1]

CROSS JOIN

[sys].[all_columns] AS [ac2];

/* 2,000,000 orders spread across the customers */

INSERT INTO [dbo].[orders]

(

[customer_id]

, [order_date]

, [amount]

)

SELECT TOP (2000000)

(ABS(CHECKSUM(NEWID())) % 20000) + 1

, DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 1461, CONVERT(date, '2022-01-01', 120))

, CONVERT(money, (ABS(CHECKSUM(NEWID())) % 100000) / 100.0)

FROM

[sys].[all_columns] AS [ac1]

CROSS JOIN

[sys].[all_columns] AS [ac2];

Now the victim query: the most recent order date and amount for each of the first 500 customers. I am using CROSS APPLY with TOP (1) ... ORDER BY, which is a very common (and perfectly reasonable) pattern for “latest row per group”:

USE [spool_demo];

SELECT
      [c].[customer_id]
    , [c].[customer_name]
    , [o].[last_order_date]
    , [o].[last_order_amount]
FROM
    [dbo].[customers] AS [c]
CROSS APPLY
(
    SELECT TOP (1)
          [last_order_date]   = [ord].[order_date]
        , [last_order_amount] = [ord].[amount]
    FROM
        [dbo].[orders] AS [ord]
    WHERE
        [ord].[customer_id] = [c].[customer_id]
    ORDER BY
        [ord].[order_date] DESC
) AS [o]
WHERE
    [c].[customer_id] <= 500
ORDER BY
    [c].[customer_id];

USE [spool_demo];

SELECT

[c].[customer_id]

, [c].[customer_name]

, [o].[last_order_date]

, [o].[last_order_amount]

FROM

[dbo].[customers] AS [c]

CROSS APPLY

(

SELECT TOP (1)

[last_order_date] = [ord].[order_date]

, [last_order_amount] = [ord].[amount]

FROM

[dbo].[orders] AS [ord]

WHERE

[ord].[customer_id] = [c].[customer_id]

ORDER BY

[ord].[order_date] DESC

) AS [o]

WHERE

[c].[customer_id] <= 500

ORDER BY

[c].[customer_id];

The TOP (1) ... ORDER BY inside the apply matters here. My first attempt at a victim used a correlated SUM, and the optimizer cheerfully decorrelated it into a hash join with no spool anywhere. The optimizer tries to convert an apply to an equivalent join early in compilation, because it knows far more optimization tricks for joins, but TOP is a non-relational construct with per-row semantics that blocks the conversion, as Paul White explains^[1] and Erik Darling demonstrates^[2] with an example very close to this one. Stuck with a nested loops apply, and with no index on [customer_id], the optimizer reaches for the spool.

On my instance this query runs in about 7 seconds per execution, and the plan contains exactly what we are hunting for:

physical_ops
------------------------------------------------------------------------
Nested Loops, Clustered Index Seek, Sort, Index Spool, Clustered Index Scan

physical_ops

------------------------------------------------------------------------

Nested Loops, Clustered Index Seek, Sort, Index Spool, Clustered Index Scan

There it is: an Index Spool (logical operation: Eager Spool) fed by a full clustered index scan of two million rows. Every execution scans the orders table, builds a temporary index on customer_id in tempdb, seeks it 500 times, and throws it away. Seven seconds of CPU, per run, forever.

Digging Spools Out of the Plan Cache

Since the missing index machinery will not help, we go to the plan cache and look for the spools directly. Cached plans are stored as showplan XML, so we can search them with XQuery for any operator where the physical operation is Index Spool and the logical operation is Eager Spool.

This query finds the ten query plans containing eager index spools with the highest average CPU cost, then returns the plan, the query text, and the execution statistics for each:

USE [master];

WITH XMLNAMESPACES
(
    'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS [x]
),
[plans] AS
(
    SELECT TOP (10)
          [deqs].[query_plan_hash]
        , [sort] = SUM([deqs].[total_worker_time] / [deqs].[execution_count])
    FROM
        [sys].[dm_exec_cached_plans] AS [decp]
    INNER JOIN
        [sys].[dm_exec_query_stats] AS [deqs]
            ON [decp].[plan_handle] = [deqs].[plan_handle]
    CROSS APPLY
        [sys].[dm_exec_query_plan]([decp].[plan_handle]) AS [deqp]
    WHERE
        [deqp].[query_plan].exist('//x:RelOp[@PhysicalOp="Index Spool" and @LogicalOp="Eager Spool"]') = 1
        AND EXISTS
        (
            SELECT
                1
            FROM
                [sys].[dm_exec_plan_attributes]([decp].[plan_handle]) AS [pa]
            WHERE
                [pa].[attribute] = 'dbid'
                AND CONVERT(int, CASE WHEN [pa].[attribute] = 'dbid' THEN [pa].[value] END) > 4
        )
    GROUP BY
        [deqs].[query_plan_hash]
    ORDER BY
        [sort] DESC
)
SELECT
      [deqp].[query_plan]
    , [dest].[text]
    , [avg_worker_time]  = [deqs].[total_worker_time] / [deqs].[execution_count]
    , [deqs].[total_worker_time]
    , [deqs].[execution_count]
FROM
    [sys].[dm_exec_cached_plans] AS [decp]
INNER JOIN
    [sys].[dm_exec_query_stats] AS [deqs]
        ON [decp].[plan_handle] = [deqs].[plan_handle]
CROSS APPLY
    [sys].[dm_exec_query_plan]([decp].[plan_handle]) AS [deqp]
CROSS APPLY
    [sys].[dm_exec_sql_text]([deqs].[sql_handle]) AS [dest]
WHERE
    EXISTS
    (
        SELECT
            1
        FROM
            [plans] AS [p]
        WHERE
            [p].[query_plan_hash] = [deqs].[query_plan_hash]
    )
ORDER BY
    [avg_worker_time] DESC
OPTION (RECOMPILE, MAXDOP 1);

USE [master];

WITH XMLNAMESPACES

(

'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS [x]

[plans] AS

(

SELECT TOP (10)

[deqs].[query_plan_hash]

, [sort] = SUM([deqs].[total_worker_time] / [deqs].[execution_count])

FROM

[sys].[dm_exec_cached_plans] AS [decp]

INNER JOIN

[sys].[dm_exec_query_stats] AS [deqs]

ON [decp].[plan_handle] = [deqs].[plan_handle]

CROSS APPLY

[sys].[dm_exec_query_plan]([decp].[plan_handle]) AS [deqp]

WHERE

[deqp].[query_plan].exist('//x:RelOp[@PhysicalOp="Index Spool" and @LogicalOp="Eager Spool"]') = 1

AND EXISTS

(

SELECT

FROM

[sys].[dm_exec_plan_attributes]([decp].[plan_handle]) AS [pa]

WHERE

[pa].[attribute] = 'dbid'

AND CONVERT(int, CASE WHEN [pa].[attribute] = 'dbid' THEN [pa].[value] END) > 4

)

GROUP BY

[deqs].[query_plan_hash]

ORDER BY

[sort] DESC

)

SELECT

[deqp].[query_plan]

, [dest].[text]

, [avg_worker_time] = [deqs].[total_worker_time] / [deqs].[execution_count]

, [deqs].[total_worker_time]

, [deqs].[execution_count]

FROM

[sys].[dm_exec_cached_plans] AS [decp]

INNER JOIN

[sys].[dm_exec_query_stats] AS [deqs]

ON [decp].[plan_handle] = [deqs].[plan_handle]

CROSS APPLY

[sys].[dm_exec_query_plan]([decp].[plan_handle]) AS [deqp]

CROSS APPLY

[sys].[dm_exec_sql_text]([deqs].[sql_handle]) AS [dest]

WHERE

EXISTS

(

SELECT

FROM

[plans] AS [p]

WHERE

[p].[query_plan_hash] = [deqs].[query_plan_hash]

)

ORDER BY

[avg_worker_time] DESC

OPTION (RECOMPILE, MAXDOP 1);

A few notes on what the moving parts are doing:

The XMLNAMESPACES clause declares the showplan namespace so the XQuery exist() predicate can address the plan XML. Without it, the path matches nothing and the query cheerfully returns zero rows.
The exist() test looks for any RelOp node in the plan with PhysicalOp="Index Spool" and LogicalOp="Eager Spool". That combination is specifically the build-an-index-at-runtime behavior; it will not match lazy spools or table spools, which are different animals with different remedies.
The dbid > 4 filter on sys.dm_exec_plan_attributes excludes plans whose home database is a system database, so you are not chasing spools inside msdb maintenance queries you cannot change anyway. The CASE wrapper around the conversion looks redundant, but it is not: SQL Server is free to evaluate the CONVERT before the attribute = 'dbid' filter, and some of the other attributes in that DMF are bigint-sized, which throws an arithmetic overflow. The CASE guarantees the conversion only ever sees the dbid value. (Ask me how I know.)
The CTE ranks plans by average worker time per execution (grouped by query_plan_hash, so trivially different variants of the same query are treated as one), and the outer query brings back the plan XML, the query text, and the raw numbers for the winners.
OPTION (RECOMPILE, MAXDOP 1) keeps this diagnostic query itself out of the plan cache and stops it from going parallel. Shredding XML across the entire plan cache is not free, which brings me to the next point.

A word of warning: this query inspects the showplan XML of every plan in the cache, and XQuery over large plans is CPU-intensive. On a busy production server with a large plan cache, run it during a quiet window, and expect it to take a while the first time. This is a hunting expedition, not a dashboard query. Also note that the XML methods require QUOTED_IDENTIFIER ON; if you run this through sqlcmd, add the -I flag or you will get error 1934.

After running the demo query five times, here is what the hunting query returned on my instance:

query_text                                    avg_worker_time  total_worker_time  execution_count
--------------------------------------------  ---------------  -----------------  ---------------
SELECT [c].[customer_id], [c].[customer_...           7036984           35184922                5

query_text avg_worker_time total_worker_time execution_count

-------------------------------------------- --------------- ----------------- ---------------

SELECT [c].[customer_id], [c].[customer_... 7036984 35184922 5

That is 7.04 seconds of CPU per execution, 35 seconds total across five runs, all of it rebuilding the same temporary index over and over.

One quirk worth knowing: the dbid plan attribute records the database context the query compiled in, not the database the tables live in. My first attempt ran the demo query from a connection whose context was master with a USE statement in the batch, and the plan’s dbid came back as 1, so my own filter excluded it. If the hunting query comes up empty when you know a spool exists, check which database the connection was actually sitting in.

Reading What You Caught

Once you have a plan in hand, click into the XML (or open the graphical plan) and find the Index Spool operator. The spool’s seek predicate tells you exactly what index the optimizer wished it had: the columns being seeked are your key columns, and the columns flowing out of the spool are your includes.

In other words, the spool is a fully-specified missing index request. It is just filed in the wrong place, written in XML, and billed to your tempdb on every execution instead of being suggested politely in a DMV.

You can even extract the specification programmatically. This query pulls the seek column and output columns from the spool operator in our cached demo plan:

USE [master];

WITH XMLNAMESPACES
(
    'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS [x]
)
SELECT
      [seek_column]  = [sp].[c].value('(x:RangeColumns/x:ColumnReference/@Column)[1]', 'varchar(128)')
    , [output_cols]  = STUFF(
      (
          SELECT
              ', ' + [oc].[c].value('@Column', 'varchar(128)')
          FROM
              [n].[c].nodes('x:OutputList/x:ColumnReference') AS [oc] ([c])
          FOR XML PATH('')
      ), 1, 2, '')
FROM
    [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY
    [sys].[dm_exec_sql_text]([qs].[sql_handle]) AS [st]
CROSS APPLY
    [sys].[dm_exec_query_plan]([qs].[plan_handle]) AS [qp]
CROSS APPLY
    [qp].[query_plan].nodes('//x:RelOp[@PhysicalOp="Index Spool" and @LogicalOp="Eager Spool"]') AS [n] ([c])
CROSS APPLY
    [n].[c].nodes('x:Spool/x:SeekPredicateNew/x:SeekKeys/x:Prefix') AS [sp] ([c])
WHERE
    [st].[text] LIKE '%last_order_date%'
    AND [st].[text] NOT LIKE '%dm_exec%';

USE [master];

WITH XMLNAMESPACES

(

'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS [x]

)

SELECT

[seek_column] = [sp].[c].value('(x:RangeColumns/x:ColumnReference/@Column)[1]', 'varchar(128)')

, [output_cols] = STUFF(

(

SELECT

', ' + [oc].[c].value('@Column', 'varchar(128)')

FROM

[n].[c].nodes('x:OutputList/x:ColumnReference') AS [oc] ([c])

FOR XML PATH('')

), 1, 2, '')

FROM

[sys].[dm_exec_query_stats] AS [qs]

CROSS APPLY

[sys].[dm_exec_sql_text]([qs].[sql_handle]) AS [st]

CROSS APPLY

[sys].[dm_exec_query_plan]([qs].[plan_handle]) AS [qp]

CROSS APPLY

[qp].[query_plan].nodes('//x:RelOp[@PhysicalOp="Index Spool" and @LogicalOp="Eager Spool"]') AS [n] ([c])

CROSS APPLY

[n].[c].nodes('x:Spool/x:SeekPredicateNew/x:SeekKeys/x:Prefix') AS [sp] ([c])

WHERE

[st].[text] LIKE '%last_order_date%'

AND [st].[text] NOT LIKE '%dm_exec%';

seek_column   output_cols
------------  --------------------
customer_id   order_date, amount

seek_column output_cols

------------ --------------------

customer_id order_date, amount

The spool seeks on customer_id and outputs order_date and amount. That is not a hint about what index to create. That is the index, spelled out.

Fixing It

The fix is almost always the obvious one: create the permanent index the spool is building for you. Take the seek predicate columns as the index key, add the output columns as includes, and the optimizer will seek the real index instead of constructing a disposable one. For our demo query, the spool told us exactly what to build:

USE [spool_demo];

CREATE NONCLUSTERED INDEX [ix_orders_customer_id_order_date]
    ON [dbo].[orders] ([customer_id], [order_date] DESC)
    INCLUDE ([amount]);

USE [spool_demo];

CREATE NONCLUSTERED INDEX [ix_orders_customer_id_order_date]

ON [dbo].[orders] ([customer_id], [order_date] DESC)

INCLUDE ([amount]);

(I put order_date DESC in the key rather than the includes because the query wants the most recent order; that lets the TOP (1) read exactly one row per customer instead of sorting.)

The results, same query, five more executions:

                    before (spool)    after (index)
avg_worker_time          7,036,984            1,671
avg elapsed              ~7,000 ms            ~3 ms

before (spool) after (index)

avg_worker_time 7,036,984 1,671

avg elapsed ~7,000 ms ~3 ms

The plan now reads Nested Loops, Clustered Index Seek, Top, Index Seek: no spool, no scan, no two-million-row index build per execution. Average CPU dropped from 7 seconds to under 2 milliseconds, a factor of roughly 4,200. I did not have to rewrite a single character of the query.

Before you create an index like this in production, sanity-check the usual suspects:

Does a near-miss index already exist? Sometimes the spool exists because an index has the right key but is missing one include column, and widening the existing index is better than adding a new one.
How often does the query run? A spool in a once-a-month report may not be worth an index that every INSERT, UPDATE, and DELETE has to maintain. The execution count and total worker time columns in the query output are there precisely to inform this judgment call.
Is the query itself reasonable? Occasionally the spool is a symptom of a join predicate wrapped in a function or a type mismatch, and fixing the predicate is the real answer.

But in my experience, the most common outcome of this hunt is finding a handful of queries that have been rebuilding the same index thousands of times a day for years, creating one permanent index each, and watching a chunk of CPU and tempdb usage quietly disappear.

If you followed along, clean up the demo database when you are done:

USE [master];

DROP DATABASE IF EXISTS [spool_demo];

USE [master];

DROP DATABASE IF EXISTS [spool_demo];

Credit where due: the technique of mining the plan cache for spool operators has been written about by several people in the community over the years, and two in particular are essential reading if you want to go deeper. Paul White‘s Eager Index Spool and The Optimizer^[3] is the definitive internals treatment, covering when the optimizer chooses the spool, how it costs the decision, and why the spool only appears on the inner side of an apply. Erik Darling‘s post Indexing SQL Server Queries For Performance: Eager Index Spools^[4] covers the practical tuning side (with video), and his Learn T-SQL With Erik training course^[5] includes a dedicated lesson on eager index spools as an execution plan red flag. All of it is well worth your time.

Found anything interesting lurking in your plan cache? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.

References

Answer to “Understanding this LEFT JOIN vs OUTER APPLY execution plan” – Paul White, Database Administrators Stack Exchange. Explains the optimizer’s early apply-to-join conversion and why non-relational constructs like TOP make you implicitly responsible for the final plan shape. ↩
Answer to “Why does OUTER APPLY with TOP 1 produce a different plan than LEFT JOIN” – Erik Darling, Database Administrators Stack Exchange. Demonstrates TOP (1) inside an apply blocking the join transformation, with an example very close to the one in this post. ↩
Eager Index Spool and The Optimizer – Paul White, sql.kiwi. The definitive internals treatment: when the optimizer chooses an eager index spool, how the decision is costed, and why the spool only appears on the inner side of an apply. ↩
Indexing SQL Server Queries For Performance: Eager Index Spools – Erik Darling, erikdarling.com. The practical tuning side, with accompanying video. ↩
Execution Plan Red Flags: Eager Index Spool – Erik Darling, Learn T-SQL With Erik training course. A dedicated lesson on eager index spools as an execution plan red flag. ↩

Tags: execution plans, indexing, performance, SQL Server, SQL Server 2025

Finding Eager Index Spools in the Plan Cache

What an Eager Index Spool Actually Is

Building One on Purpose

Digging Spools Out of the Plan Cache

Reading What You Caught

Fixing It

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Finding Eager Index Spools in the Plan Cache

What an Eager Index Spool Actually Is

Building One on Purpose

Digging Spools Out of the Plan Cache

Reading What You Caught

Fixing It

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta