SQL Server Performance

Table Spool

Discussion in 'T-SQL Performance Tuning for Developers' started by mistrdj, Oct 13, 2005.

  1. mistrdj New Member

    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.
  2. FrankKalis Moderator

    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.

    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)
  3. satya Moderator

    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.
  4. mistrdj New Member

    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.
  5. satya Moderator

    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.
  6. null New Member

    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.

Share This Page