Optimizing Tempdb Performance

The size and physical placement of the tempdb database can affect the performance of a system.

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.
• Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.
• 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.
• 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

---

ALTER DATABASE Tempdb MODIFY FILE
   (NAME = 'Tempdev', SIZE = 200)

Viewing tempdb Size and Growth Parameters

---

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

Link: http://msdn.microsoft.com/en-us/library/ms175527.aspx