Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Eager Spool Expand / Collapse
Author
Message
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: Wednesday, April 02, 2014 9:12 PM
Points: 215, Visits: 542
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


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
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.

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
Gila,

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



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1478892
Posted Tuesday, July 30, 2013 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:20 PM
Points: 14,793, Visits: 27,268
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse