locked
Table Spool - Good Thing Or Bad Thing? RRS feed

  • Question

  • I noticed during an insert that there are 3 table spool object, then 3 sorts and then 3 updates to to my indexes in my SQL query plan. Are table spools good or bad?  Are there some optimizations to make table spools work better?
    DJ Baby Anne's Biggest Fan................
    Wednesday, May 5, 2010 8:05 PM

Answers

  • You are asking a tricky question, because there is no correct answer to this question.

    Anyway to get familiar with spools quickly refer to http://scarydba.wordpress.com/2009/09/09/spools-in-execution-plans/.

    And as an answer to your question my understanding of spools is related to the context.

    They appear because either they are forced by the way you write your statement (Recursive CTE for example),

    or they used by optimizer to optimize your query.

    So if they appear as an optimization you may have something to tune up or not,

    and that should be answered for each statement specifically.

    To sum up you should post your statement to be able to decide.

    • Proposed as answer by Naomi N Wednesday, May 5, 2010 9:56 PM
    • Marked as answer by KJian_ Tuesday, May 11, 2010 3:25 AM
    Wednesday, May 5, 2010 8:28 PM

All replies

  • You are asking a tricky question, because there is no correct answer to this question.

    Anyway to get familiar with spools quickly refer to http://scarydba.wordpress.com/2009/09/09/spools-in-execution-plans/.

    And as an answer to your question my understanding of spools is related to the context.

    They appear because either they are forced by the way you write your statement (Recursive CTE for example),

    or they used by optimizer to optimize your query.

    So if they appear as an optimization you may have something to tune up or not,

    and that should be answered for each statement specifically.

    To sum up you should post your statement to be able to decide.

    • Proposed as answer by Naomi N Wednesday, May 5, 2010 9:56 PM
    • Marked as answer by KJian_ Tuesday, May 11, 2010 3:25 AM
    Wednesday, May 5, 2010 8:28 PM
  • As Muhammed indicated, that question really can't be answered unless we see the actual query and its plan.

    Certain language constructs (like Recursive CTE as Muhammed mentioned) and the OVER clause will bring about spooling, and if it's causing a problem with performance, there are alternatives to those.

     


    --Brad (My Blog)
    Wednesday, May 5, 2010 9:06 PM
  • This is the code that generate the issue. As background it has the following:

    * Full text index

    * One clustered index (column not used in this query)

    * 3 Nonclustered indexes on fields used in the query (They have different sort orders)

    INSERT INTO [M].[Narratives] 
    ( 
     [ContextID] ,
     [ContextTypeID] ,
     [NarrativeText] ,
     [CreatedBy] 
    ) 
    select 
     E.EventID, 
     [ContextTypeID] = convert(uniqueidentifier,'6A0ED2D8-CA19-4725-ADF6-7EA97EE3BBFE'), 
     [MiscText] ,
     [EnteredBy] = convert(uniqueidentifier,'42C8AC1C-E25B-4A8D-B9B8-32156EFCA44C') 
    from 
     #tblEventLoad EA 
     inner join M.Events E 
      on E.SourcePK = EA.SourcePK 
      and E.SourceID = EA.SourceID 
    where 
     not exists 
      ( 
      select 1 from M.Narratives N 
      where N.ContextID = E.EventID 
      and [ContextTypeID] = convert(uniqueidentifier,'6A0ED2D8-CA19-4725-ADF6-7EA97EE3BBFE')
      ) 
     and len(EA.[MiscText]) > 0
    

     


     

    DJ Baby Anne's Biggest Fan................

    Thursday, May 6, 2010 5:31 PM
  • Is there a way you can post a picture (or upload a screenshot somewhere) where we can see the query plan?

    Either that or do SET SHOWPLAN_TEXT ON and run the query and then copy/paste the text plan here?

    Also, I know you mentioned the indexes in general, but can you be more specific?  What is the clustered/nonclustered index definitions for the NARRATIVES table and the EVENTS table (and the #tblEventLoad table if any)?

     


    --Brad (My Blog)
    Thursday, May 6, 2010 5:43 PM
  • Hi

    ,

    INSERT

    INTO [M].[Narratives]

    (

    [ContextID]

    ,

    [ContextTypeID]

    ,

    [NarrativeText]

    ,

    [CreatedBy]

    )

    select

    E

    [ContextTypeID]

    [MiscText]

    .EventID, = convert(uniqueidentifier,'6A0ED2D8-CA19-4725-ADF6-7EA97EE3BBFE'), ,

    [EnteredBy]

    = convert(uniqueidentifier,'42C8AC1C-E25B-4A8D-B9B8-32156EFCA44C')

    from

    #tblEventLoad EA

    inner join M.Events E on E.SourcePK = EA.SourcePK and E.SourceID = EA.SourceID Left JOIN M.Narratives N ON N.ContextID = E.EventID and [ContextTypeID] = convert(uniqueidentifier,'6A0ED2D8-CA19-4725-ADF6-7EA97EE3BBFE')

    where

    Please

    len(EA.[MiscText]) > 0use this code instead of yrs.

    As

    Thanks

    Subhash

    Table spool is bad situation , so Please optimize as same& regards
    Wednesday, November 7, 2012 11:40 AM