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: 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


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 @ 12:11 PM
Points: 42,470, Visits: 35,541
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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:57 AM
Points: 66, Visits: 179
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: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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