Configure SQL Server 2005 TempDB for performance

Some processing once reserved for the SQL Server 2000 transaction log, such as row versioning and online index rebuilds, has been moved to SQL Server 2005 TempDB, making it essential that you properly configure your database -- or suffer the performance consequences.

Properly configuring TempDB in SQL Server 2005 is imperative with some of the system database's new responsibilities. With SQL Server 2005, TempDB is responsible for managing temporary objects, row versioning and online index rebuilds. Some of this processing has moved from the transaction log in SQL Server 2000 to TempDB in SQL Server 2005. As such, configuring this database can have major performance implications.

Where should I put TempDB?

TempDB should reside on its own dedicated physical disks. This allows it to split I/O transactions from the remainder of volumes on the SQL Server. The table below outlines a sample drive layout with TempDB residing on its own physical disk:

Disk Disks Drive Size Controller RAID Size Volume Purpose
0 2 72 GB 1 - 1 1 72 C:\ Windows, SQL Server Program Files and System Databases (Master, Model, MSDB) (Random)
1 2 72 GB 1 - 1 1 72 D:\ Tempdb (Random)
2 2 72 GB 1 - 2 1 72 E:\ Database Transaction Logs (Sequential)
3 5 72 GB 2 - 1 5 288 F:\ Databases (Random)
4 3 72 GB 2 - 2 5 144 G:\ Backups and Batch Processing (Sequential)
- 14 - - - 648 - -

For additional information about the SQL Server hard drive recommendations, reference Optimize SQL Server hardware performance, which outlines hardware best practices.

How do I move TempDB to a new disk drive?

ALTER DATABASE is the key T-SQL command to perform this operation. Microsoft offers a good example in SQL Server 2005 Books Online. The article name is ALTER DATABASE (Transact-SQL) and the specific section is 'G. Moving tempdb to a new location.'

How many database files should TempDB consist of in SQL Server?

Conventional wisdom has been to have a single database and transaction log file. Microsoft has subsequently communicated having a single file for each physical CPU on the SQL Server. For example, if you have 2 CPUs, then have 2 TempDB database files.

What other configurations are important?

Ensure that the TempDB files are preallocated and do not auto grow, which means SQL Server does not need to unnecessarily manage the database growth. If TempDB is undersized, then preallocate each of the files in equal increments so all files remain the same size.

For example, here is how TempDB can be configured on a 2-CPU SQL Server in a small environment:

Configure TempDB
Configure TempDB on a 2-CPU SQL Server

What is the rule of thumb when sizing TempDB?

Based on your environment, the minimum size recommendations for TempDB are as follows:

ID Environment Size Database Size (MB) Transaction Log Size (MB)
1 Small 1024 256
2 Medium 5120 1024
3 Large 10024 2048

You are the best judge of your environment and can scale this database to a much larger configuration to meet your needs.

About the author:Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is SearchSQLServer.com's Performance Tuning expert. Ask him a question here.

Next Steps

Webcast: SQL Server performance tuning myths

Tip: SQL Server Clinic: T-SQL performance problems and solutions

Ask the Experts: Ask Jeremy Kadlec your performance and tuning questions

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning