Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Eager Spool Expand / Collapse
Posted Sunday, April 14, 2013 3:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

I am deleting rows from huge table. I am doing it in the batch of 1000 rows. This tables has got cluster index, and many indexes. In the execution plan its shows deletion on the cluster index is feeling up the Eager spool and this eager spool table is used for deletion of other indexes.

Cost of feeling the eager spool is shown Zero% but for other idexex is 8% each.

Becasuse of eager spool it is increasing tempdb_log file also and perfomance of the query is slow.

Any suggestion to improve the perfromance.
Post #1442095
Posted Sunday, April 14, 2013 4:28 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Without seeing the exec plan, I'm guessing...

The spools are likely an optimisation for the deleting of the rows from all the nonclustered indexes. You could try dropping the indexes and rebuilding them after the deletes are complete.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1442099
Posted Monday, July 29, 2013 7:12 AM


Group: General Forum Members
Last Login: Thursday, November 24, 2016 9:59 AM
Points: 108, Visits: 306
Hi Gila,

Yeah, Its true, when I removed two un-wanted indexes over my table, I found the Eager Spool is removed from the Execution plan.

Post #1478572
Posted Tuesday, July 30, 2013 3:41 AM



Group: General Forum Members
Last Login: Tuesday, November 8, 2016 4:31 AM
Points: 2,222, Visits: 2,760

can you please tell me about this Eager pool? I am not aware of this thing

To get quick answer follow this link:
Post #1478892
Posted Tuesday, July 30, 2013 8:01 AM



Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 17,173, Visits: 32,139
Spools are a way for the query engine to store data, either for reusing it, or for rollbacks (other situations too, imagine where it might need to keep stuff for repeated use within a single query). Most of the time in a delete statement I would assume that we're seeing a spool as a means for rollback and as part of protection from the Halloween problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1479015
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse