Database Administrators

Questions tagged [index-spool]

Ask Question

The index spool execution plan operator optimizes access to an intermediate result set by building a temporary nonclustered index in tempdb.

7 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
-1
votes
1answer
45 views

Index Update with Eager Spool and Sort operators in Execution Plan

After an UPDATE statement on a large table, the execution plan shows updates of indexes (all non-clustered) that include the updated columns. Before each index update, there is an Eager Spool ...
3
votes
1answer
622 views

Speed Up Cross Apply Without Index Hint

I have a very small table with 12 rows in it that can be created with the following statement: CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY, ...
0
votes
1answer
523 views

Plan changes to include Eager Spool causes the query to run slower

We have a reporting query which is erratic in terms of execution plan and run duration. It is either 5 seconds or as slow as 5 minutes. The query is a Select statement with no DML involved. One thing ...
0
votes
1answer
58 views

New Analyst SQL Optimization - Multiple Spools

Unfortunately my ability to Query has outgrown my knowledge of SQL Optimization, so i am hoping someone would help a young analyst by looking at this atrocious execution plan and provide some wisdom ...
23
votes
1answer
2k views

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE ...
1
vote
1answer
137 views

Eager spool for update and delete on partitioned table

When I update or delete on partition table it shows eager spool in execution plan. Image for Update query execution plan showing eager spool:- I can’t understand why it is doing eager spool only ...
14
votes
1answer
2k views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...