SQL Server Performance Forum – Threads Archive

Table spool/Lazy spool

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…

See this article http://www.sql-server-performance.com/query_execution_plan_analysis.asp] in this website can give some idea. HTH
Satya SKJ

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
Webmaster
SQL-Server-Performance.Com

Brad, 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?

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
Webmaster
SQL-Server-Performance.Com

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.

In Addition to what satya and bradmcgehee has mentioned, you can learn about execution plans from this excellent book from Red-Gate: http://www.red-gate.com/our-company/about/book-store/sql-server-execution-plans

Ahem, thanks for posting the link, but the original thread is back from 2003…

Preeti.. may be you have lot of time to go thru these old posts

.. just kidding, good to see yo back here.

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

]]>