SQL Server Performance Forum – Threads Archive
Table Spool
I have a query that is ran by sp_executesql from within a 3rd party application. essentially it has a subquery within it. Often times this query will fill up the txn log since it is storing data in a temp table due to the sorting (I think). At any rate, until I either kill the spid, or the loop finally decides to end, this will often fill up the drive if goes unnoticed. When I run the execution plan I noticed Table Spool/Lazy Pool in the execution plan. But when I run the same query on a different server (that NEVER has this problem) I don’t see the Table Spool Questions: What causes table spooling? is it a server setting or SQL setting that is different that I’m missing? why is it happening on one server and not the other? Thanks.You should start with analysing the statement which is causing this behaviour. This is what BOL says about Table Spools
quote:
The Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query). If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
The Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query). If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
There are many situations possible where spooling can happen. But all troubleshooting would start with code analysis. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
If the table spooling is being caused by the join(s), then only adding/adjusting index(es) on those tables would help, and perhaps not even then. Sometimes temp space is just needed. 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.
Thank you Satya. Still curious as to why it would continue to show as an open transacation and fill up the tempdb txn log, but I will give that a try.
quote:Originally posted by satya
If the table spooling is being caused by the join(s), then only adding/adjusting index(es) on those tables would help, and perhaps not even then. Sometimes temp space is just needed.
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.
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
By default TEMPDB is used extensively with a long running transaction, in this case you need to monitor the contention. You need to check the Joins are working efficiently by using PROFILER and may leading into long running transactions. 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.
quote:Originally posted by satya
By default TEMPDB is used extensively with a long running transaction…
Satya SKJ
Interesting…you mention here "long" running transactions. In these cases, have you always found this "extensive" use of the tempDB to hurt performance of the transaction? If I have a proc that would qualify as one of these "long" running transactions that is using the tempDB, could this be eliminated by breaking the transaction down into smaller transactions using Begin/Commit transaction blocks (if not already)? Perhaps using the tempDB is in the best interest of the specific transaction, maybe not…will keep this in mind, seems it would make for some nice testing.
]]>