What is the capacity requirement for tempdb?

Answered

Comments

1 comment

  • Avatar
    Tom Andres Lomsdalen

    It is very difficult to predict the required size for tempdb. Tempdb is used for storing temporary data of some kind. It is indeed used more or less directly for temporary tables and table variables, but I barely ever see this being an issue. More important is the fact that there are a large number of tasks and situations possibly affecting tempdb usage, which often cannot be controlled (or eliminated), such as:

    • Sorting of data (for instance getting distinct values or index maintenance)
    • Joining large amounts of data
    • Aggregating data
    • Internal memory pressure in SQL Server

    If for instance, multiple reports are being executed simultaneously, each of these may require space in tempdb for sorting or joining data, possibly causing tempdb to grow. Thus I have the same recommendation as Microsoft for capacity planning of tempdb:

    • Have autogrowth enabled for tempdb, set to a fixed growth as for instance 250MB
    • Apply workload to the database system, most easily done by having the server running for some time, including maintenance jobs
    • Check how much tempdb space has been used

    Once the space tempdb capactity requirement is found, set an appropriate initial size of your tempdb database, so that it normally does not need to grow. In addition, configure instant file initialization as described in the reference

    In other words, restricting tempdb to a rather small partition is not a good idea, as it may cause issues.

    As memory pressure may (or I would rather say will) cause increased use of tempdb, adding more memory to SQL Server could be beneficial. The following two performance counters could be used to check for memory pressure:

    • SQL Server Buffer: Buffer Cache Hit Ratio (should be over 99%)
    • SQL Server Buffer: Page Life Expectancy (should be over 900)

    Of these, the latter is the one most commonly indicating issues, having values close to zero.

    We’re constantly optimizing our code for performance, and this will in some cases reduce the memory requirement and thus possibly also the use of tempdb.

    References

Please sign in to leave a comment.