Share via

  • Article

Optimizing tempdb Performance

The size and physical placement of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file. For information about determining the appropriate amount of disk space required for tempdb, see Capacity Planning for tempdb.

tempdb Size and Placement Recommendations

To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

  • Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

    For more information, see ALTER DATABASE (Transact-SQL) or How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).

  • Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.

    Note

    If the production environment cannot tolerate the potential for application time-outs that may occur during autogrow operations, preallocate space to allow for the expected workload.

  • Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance. We recommend the following general guidelines for setting the FILEGROWTH increment for tempdb files.

    tempdb file size

    FILEGROWTH increment

    0 to 100 MB

    10 MB

    100 to 200 MB

    20 MB

    200 MB or more

    10%*

    * You may have to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. To avoid potential latch time-outs, we recommend limiting the autogrow operation to approximately two minutes. For example, if the I/O subsystem can initialize a file at 50 MB per second, the FILEGROWTH increment should be set to a maximum of 6 GB, regardless of the tempdb file size. If possible, use instant database file initialization to improve the performance of autogrow operations.

  • Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

  • Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.

  • Make each data file the same size; this allows for optimal proportional-fill performance.

  • Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

  • Put the tempdb database on disks that differ from those that are used by user databases.

Modifying tempdb Size and Growth Parameters

You can modify the size and file growth parameters of the tempdb data or log files by using one of the following methods:

The values for the file size and filegrowth parameters are used whenever tempdb is created. For example, if you increase the size of the tempdb data file to 20 MB and increase the file growth increment to 15 percent, the new values immediately take affect. If subsequent transactional activities cause tempdb to grow in size, the data file will return to the size of 20 MB every time you restart the instance of SQL Server.

Viewing tempdb Size and Growth Parameters

You can view the size and file growth parameters of the tempdb data or log files by using one of the following methods:

  • SQL Server Management Studio

  • Running the following query.

    SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Detecting Disk I/O Path Errors

When set to CHECKSUM, the PAGE_VERIFY option discovers damaged database pages caused by disk I/O path errors and reports these errors, such as MSSQLSERVER_823, MSSQLSERVER_824, or MSSQLSERVER_825, in the SQL error log. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk. For more information about I/O errors, see Microsoft SQL Server I/O Basics, Chapter 2.

In earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb database and cannot be modified. In SQL Server 2008, the default value for the tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an installation SQL Server, the default value remains NONE. We recommend that you set the PAGE_VERIFY option for the tempdb database to CHECKSUM.