Eager Spool

  • 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.

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

  • 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/

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply