I was reading [this](https://learn.microsoft.com/en-us/archive/blogs/craigfr/scalar-subqueries) And I did the following:

go
create table fiirst (
col1 int,
col2 int
);
create table seecond(
col1 int,
col2 int
);
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into fiirst(col1,col2)
select id,id 
from ids;
with
n1(c) as (select 0 union all select 0 ),
n2(c) as ( select 0 from n1 as t1 cross join n1 as t2),
n3(c) as ( select 0 from n2 as t1 cross join n2 as t2),
n4(c) as (select 0 from n3 as t1 cross join n3 as t2),
ids(id) as (select ROW_NUMBER() over (order by (select null)) from n4)
insert into seecond(col1,col2)
select id,id
from ids;
----Craig Freedman's query

select *
from fiirst
where fiirst.col1 > (
    select min(seecond.col1)
    from seecond
    where seecond.col2 < fiirst.col2
);

And I got an [index spool](https://www.brentozar.com/pastetheplan/?id=QpaT80Kj8T) , even though the table is a heap. The question is, how did this happen? Why do I get an index spool on a heap? In the example mentioned in the above link, there was no rows, so no spools, but here I see them?

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 X_10000 (ID INT NOT NULL); truncate table X_10000; INSERT INTO X_10000 WITH (TABLOCK) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values t1 CROSS JOIN master..spt_values t2; And force a nested loop join with MAXDOP 1: SELECT * FROM X_10000 a INNER JOIN X_10000 b ON a.ID = b.ID OPTION (LOOP JOIN, MAXDOP 1); This is a rather unfriendly action to take towards SQL Server. Nested loop joins often aren't a good choice when both tables don't have any relevant indexes. Here's the plan: The query takes 13 seconds on my machine with 100000000 rows fetched from the table spool. However, I don't see why the query has to be slow. The query optimizer has the ability to create indexes on the fly through index spools . This query seems like it would be a perfect candidate for an index spool. The following query returns the same results as the first one, has an index spool, and finishes in less than a second: SELECT * FROM X_10000 a CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca OPTION (LOOP JOIN, MAXDOP 1); This query also has an index spool and finishes in less than a second: SELECT * FROM X_10000 a INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID OPTION (LOOP JOIN, MAXDOP 1); Why doesn't the original query have an index spool? Is there any set of documented or undocumented hints or trace flags that will give it an index spool? I did find this related question , but it doesn't fully answer my question and I can't get the mysterious trace flag to work for this query.

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 missing index warning, yet the optimizer chooses not to create a temporary index. The query I am using is SELECT z.a FROM dbo.t5 AS z WITH(INDEX(0)) WHERE EXISTS ( SELECT y.a FROM dbo.t4 AS y WHERE y.a = z.a ) OPTION (MAXDOP 1); Table schemas are: CREATE TABLE dbo.t4 ( a integer NULL, b varchar(1000) NULL, p varchar(100) NULL ); CREATE TABLE dbo.t5 ( a integer NULL, b varchar(1000) NULL ); CREATE UNIQUE CLUSTERED INDEX c1 ON dbo.t5 (a); Both tables have 10,000 rows, which you can simulate with: UPDATE STATISTICS dbo.t4 WITH ROWCOUNT = 10000, PAGECOUNT = 1000; UPDATE STATISTICS dbo.t5 WITH ROWCOUNT = 10000, PAGECOUNT = 1000; The query plan is: It even tells me to create this index: USE [planoper]; GO CREATE NONCLUSTERED INDEX [] ON [dbo].[t4] ([a]);

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 operator followed by a very costly Sort. Overall, the updates of the indexes consume about 50% of the execution time. Is there a way to optimize the indexes and minimize the costs?

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, ScoreGoal float NOT NULL ); I have another table with ≈100M rows in it that can be created with the following statments: CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL, Score1 decimal(3, 2) NOT NULL, Score2 decimal(3, 2) NOT NULL, Score3 decimal(3, 2) NOT NULL, Score4 decimal(3, 2) NOT NULL, Score5 decimal(3, 2) NOT NULL, Score6 decimal(3, 2) NOT NULL, FromToday bit NOT NULL ); ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC) WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE ); CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday) INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6) WHERE FromToday = 1 WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE ); i02TodayRecords has ≈1M rows in it. When I run the following query—I struggled formatting it to both look clean and prevent a horizontal scrollbar—it takes over 5 minutes to finish: SELECT b.RecordKey, COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix ORDER BY b.Score6 ASC ) - 1 ) ) / COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0 ), 1 ) ) + 0.005, 2 ), 0.96 ), 0.95 ) AS NewScore FROM (SELECT LEFT(s.RecordKey, 2) AS Prefix, CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS int) AS Quant FROM dbo.SlowCrossApply AS s CROSS JOIN dbo.SmallTable AS sm WHERE s.FromToday = 1 AND sm.ScoreMonth = MONTH(GETDATE()) GROUP BY LEFT(s.RecordKey, 2), sm.ScoreGoal ) AS a CROSS APPLY (SELECT TOP(a.Quant) s2.RecordKey, s2.Score6 FROM dbo.SlowCrossApply AS s2 WHERE s2.FromToday = 1 AND s2.Score6 > 0 AND LEFT(s2.RecordKey, 2) = a.Prefix ORDER BY s2.Score6 DESC ) AS b; The outer subquery returns only 10 rows; and if I supply a hint to use i02TodayRecords or put the results of the outer subquery in a table variable, it takes less than 1 second. The final result returns just over 8000 rows. The execution plan shows that 64% of the cost is due to an eager index spool on the clustered index in the Cross Apply portion. I know the index hint works (at least for now), but I'm hoping to avoid using one. Ideally, I wouldn't go the table variable route either. Is there something I can do to get the query optimizer to "know" to utilize i02TodayRecords? I realize there is *a lot* more information that is probably important, and I'll do my best to supply said information if requested. Some potentially useful information: the indexes have less than 1% fragmentation. The statistics for both indexes have been updated via a FULLSCAN, and the database is set to have simple parameterization and parameter sniffing—unfortunately, I can't changes those settings. In regards to the latter, the query optimizer did *not* replace any values with parameters unlike other simple queries I have run where I was forced to use a hint to utilize a particular filtered index.

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 I noticed is the costly Eager Spool operator after an index scan in slow executions. However, index seek is used when executing fast. I updated the stats but it didn't help to speed up. I was just wondering why sometimes optimiser chooses to use Index Scan (and Eager Spool) rather than Index Seek and what causes it to do so? Attaching SS of both slow and fast execution plans. SS is saved as a file from Plan Explorer so may upload some other tabs if need be. Thank you Edit: The query runs fast in the mornings, slow in the afternoons, always.

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 and provide some wisdom as to how i could speed it up. I've read a few threads about spooling, but they were all mostly a discussion about weather an Eager Table spool is good or bad, and the answer is always "it depends". My execution plan looks like it's Spooling and Sorting the same #Temp Table multiple times, and it's eating up a lot of execution cost. My understanding of a Table Spool is that it is temporary storage to be used later, but if the data is already stored for later use, why would it spool the same data over and over again? My query doesn't require any ordering so why would it need to sort the same #TempTable/Spool multiple times? I'm so new to this, i can't figure out how to attach the entire execution plan.... so i attached an image of the bottom half of it... Help me experienced analysts. You're my only hope. A Little Context. I currently have a transaction table that tracks all changes made to a lead in my CRM, and i am attempting to create a new table from this data to speed up reporting. I am pulling data from this transaction table and flagging the first action, first user, and other firsts of a lead by using Row_Number(). I am then inserting every "first" into a #Temp Table, as i know i am going to utilize this data multiple times. SELECT ID, Action, ROW_NUMBER() OVER (PARTITION BY ID, Action ORDER BY DATE) AS ActionNum, ROW_NUMBER() OVER (PARTITION BY ID, Actor ORDER BY DATE) AS USERNUM INTO #Temp FROM Table ; I am then Left joining this #Temp Table many times (10 times actually). I have tried multiple other ways of solving this issue but using Row_Number multiple times seems like the best solution. SELECT * FROM #temp T1 LEFT JOIN #Temp T2 ON T2.ID = T1.ID AND T2.Action = A2 AND T2.ActionNum = 1 LEFT JOIN #Temp T3 On T3.ID = T1.ID AND T3.Action = A3 AND T3.ActionNum = 1 LEFT JOIN #Temp T4 ON T4.ID = T1.ID AND T4.UserNum = 1 WHERE T1.Action = A1 AND T1.ActionNum = 1 I've looked into creating a clustered index on the #TempTable, but i must not be doing it right because it didn't change anything about my execution. Thanks in advance for all your help! Any good reading material is also greatly apprecaited! Best, Austin

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 when I am updating or deleting on partition table, same query when I run on non partition table it does not do eager spool.