Tip

Configure SQL Server 2005 TempDB for performance

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

    Requires Free Membership to View

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:

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.

More information from SearchSQLServer.com

 

This was first published in April 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.