SQL Server Performance

SQL Server 2012 TempDB ballooning to more than 75 GB

Discussion in 'ALL SQL SERVER QUESTIONS' started by Mrinal Kamboj, Jul 30, 2013.

  1. Mrinal Kamboj New Member

    In one of our database while executing a procedure serving an important business logic, which has 11 joins, then a filtering on a specific column, sorting using order by on a specific column. In the join condition, as I see them separately, there are millions of rows fetched. Now I see that TempDB is ballooning to 75 GB size, which is the available disk space and the query fails.
    What could be the possible reason:
    - Is the TempDB, behaving incorrectly, can I really expect that kind of size (75 GB).
    - If the TempDB behavior is correct, is there something I can do to mitigate the situation, will the faster execution of the query, having more statistics, relevant index, more seeking than scanning of index / table, will that help is solving the situation.
    I know a possible situation would be relocate the db to a much a disk space, but I want to figure out the tuning options first, since I do not know, what is the maximum size TempDb will bloat up to
    Any relevant suggestion would be great.
  2. Luis Martin Moderator

  3. davidfarr Member

    I concur with Luis; your tempdb size behaviour is very likely to be expected for that type of Query with those data volumes.

    Consider the following points regarding what tempdb is actually storing, as per MSDN notes;
    The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
    •Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
    •Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
    •Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
    •Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

    If your query is incorporating a large result that impacts on any of the points above, then your tempdb will grow accordingly.
    Logically therefore; if your query can be altered to avoid temporary objects and other factors mentioned, then the impact on tempdb will be less.
    Trev256b likes this.

Share This Page