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:
[IMAGE]
What ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
More information from SearchSQLServer.com
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
