SQL Server Performance

Table spool/Lazy spool

Discussion in 'T-SQL Performance Tuning for Developers' started by arb, Jan 9, 2003.

  1. arb New Member

    What techniques can be used to re-write queries so that they do not use table spools? All the material I see mentioning query optimisation says that table spools are generally bad, but nothing explains how to get rid of them...
  2. satya Moderator

  3. bradmcgehee New Member

    Technically speaking, a table spool scans the input and puts a copy of each row returned in a hidden spool table, which is stored in the the tempdb database (as the query is running). Under certain conditions, such as if the operator in the query is rewound and no rebinding is needed, the spooled data can be used for the query, preventing the need to rescan the input. This is a paraphrase of the definition I found the the SQL Server Books Online.

    I don't know about you, but I find this explanation a little confusing, so I decided to do some research on this topic.

    Here's some of what I found:

    --Using the TOP operator can lead to Table Spools.
    --Using SET ROWCOUNT to a "too high" figure can lead to Table Spools.
    --Using JOINs that return a lot of data.
    --Using the OR clause can cause Table Spools.
    --Using UDFs can cause Table Spools.
    --Outdated index statistics may cause a Table Spool.
    --A query that returns a lot of data on a non-clustered index can result in Table Spools.

    I spent a lot of time looking up this topic, and there is very little to be found. Hopefully others will be able to provide some definitive answers. But essentially, Table Spools are a bad thing from a performance perspective, but the reason they exist in the first place it to prevent even worse performance. In other words, they are actually a good thing. But, on the other hand, if they can be avoided, all the better. I think standard query optimization and indexing techniques will go a long way to preventing them in the first place. You should consider a table spool as a clue that you might want to review your query to see if there is any way you can optimize it.

    Brad M. McGehee
  4. arb New Member


    I agree that the official explanation of table spools is very confusing and far from clear. The picture in my mind however is that SQL is effectively doing to a cross-join, then filtering the results. Based on the number of rows in the two tables involved and the number of rows spooled, this seems to make sense.

    Anyway, in the query I am working on this time, I found that changing one of the joins to a LEFT join instead of an INNER join, I removed the table spool and the perfomance was markedly better. Looking at the IO statistics, it now shows 'Scan count 60, logical reads 240' instead of 'Scan count 2319, logical reads 9276'. What seems to be happening is that SQL has decided to do all the INNER JOINs in the query first (which result in 60 rows), then does the LEFT join, where it was joining the suspect table earlier in the query execution plan when it was an inner join. For this data set, the results are identical due to the business rules we have implemented (we are guaranteed that there will always be matching rows in this table) so we wont have problems with NULLs.

    Thinking about this, it seems that I could probably achieve similar results using hints to force the table join order, although I am a little hesitant to do this - any thoughts?
  5. bradmcgehee New Member

    If you can achieve your goal without hints, that's what I would do. I only use hints when I can't find any other options. Thanks for your feedback.

    Brad M. McGehee
  6. cdaly33 New Member

    Thanks for the info. I was actually able to remove a very costly table spool by reworking a query that used an OR in the WHERE statement.

    Thanks again.
  7. cdaly33 New Member

    Thanks for the info. I was actually able to remove a very costly table spool by reworking a query that used an OR in the WHERE statement.

    Thanks again.
  8. preethi Member

  9. FrankKalis Moderator

    Ahem, thanks for posting the link, but the original thread is back from 2003... :)
  10. preethi Member

  11. Luis Martin Moderator

  12. satya Moderator

    Preeti.. may be you have lot of time to go thru these old posts ;) .. just kidding, good to see yo back here.
  13. preethi Member

    Iam still figuring out what made me to pick this post. I didn't search for topics for sure. It just caught my eyes. Why? Still figuring out

Share This Page