SQL Server Performance

Eager Spool

Discussion in 'Performance Tuning for DBAs' started by colin leversuch-roberts, Mar 22, 2004.

  1. I have a query that uses 64% for an Index Spool/Eager Spool. msdn doesn't tell me if this is a good thing or a bad thing and I can't find any info .. any assistance would be appreciated.
  2. satya Moderator

    http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=3305&SearchTerms=eager,spool - any relevance to the topic.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. yes and no .. seems that this most likely indicates poor indexing, out of date stats ... the usual things. It's nice we have all this information at our finger tips, it's just sometimes a shame microsoft fails to explain what these things are - the wait types are another example
  4. satya Moderator

    Agreed on some part of your comments.

    In general Eager Spool logical operator will consume the entire input, storing each row in a hidden temporary object stored in the tempdb database.

    HOw many rows are involved in insertion to the tabel?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. bradmcgehee New Member

    I submitted this question to Microsoft Support, and here is there response:


    Spools are sometimes added to query plans to speed up multiple executions of otherwise expensive subtrees, and can in many cases significantly speed up the overall runtime of the query. The 64% per se does not mean much - without the spool you would probably have a higher cost for the subtree over which it sits, because it would be executed more times.

    This posting is provided "AS IS" with no warranties, and confers no rights.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  6. I thank you very much for your response and assistance - I looked at the query and deduced a lack of indexes ( no clustered index 500k rows ) no nci on where clause ( that's developers for you!! )
    Anyway I added a clustered index and a secondary index and re-ran, the eager spool has now gone to 1% and now I have two sort/top n parts taking 36% each up from 8% each - however as far as I can see the query hasn't actually speeded up much.

    It was my first viewing of an eager spool hence my interest, especially when I couldn't seem to find out much about them. I only have limited time to analyse queries in a busy prod environment, this one caught my eye as a developer runs it against a prod database and it was generating cxpacket waittypes. my testing is not in a similar environment although it isagainst the same data.

    Again many thanks
  7. satya Moderator

Share This Page