SQL Server Performance

work tables in tempdb

Discussion in 'Performance Tuning for DBAs' started by stefanoale, Jan 14, 2005.

  1. stefanoale New Member

    In SQL books online I read:

    ...tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server...

    A stored procedure that last week took about 1 hour to run all of the sudden now takes ~8hrs and the tempdb grows to a ridiculous 170Gbs.
    I restared the server and the tempdb is back to normal but I started looking into why this happened. I found a work around but I really would like to know the reason behind the issue. I believe it may be related to SQL generating many more work tables and I'd like to know:
    1) if anyone knows what is the logic that SQL uses to create work tables in the tempdb 2) what could have possibly made SQL think it needed 170Gbs of tempDb space(significantly more then all data contained in all the tables used by the stored procedure).

  2. joechang New Member

    it could be an explicit temp table, but in your case it sounds like an other temp table,
    a large hash join or a many-to-many merge join could generate such a table.
    i would guess someone forgot a join condition or otherwise wrote a bad query that generated a massive intermediate result. a cross join could do it
  3. Luis Martin Moderator

    I think that store procedure use temporary tables to work. That could be the razon for tempdb size.
    Also SQL use tempdb, but as far I know is not responsable for 170Gb. The application yes.

    About 1 to 8 hours: Did you defrag indexs, update statistics?

    Luis Martin

    All postings are provided “AS IS” with no warranties for accuracy.

  4. satya Moderator

    For the correct figure about worktables you can capture PERFMON*SYSMON) counters during this process for further asessment, review the contents as mentioned by JOe and try to eliminate the number of steps or the use of worktables.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page