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:

[TABLE]

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, i

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Administration Top load balancing methods for SQL Server Performance implications of transaction log autogrowth in SQL Server The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server Implementing SQL Server 2008 FILESTREAM functionality Improving SQL Server full-text search performance Using the OPENROWSET function in SQL Server New replication features in SQL Server 2008 and what they mean to you Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness Microsoft SQL Server 2005 (Yukon) SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 How to configure Database Mail in SQL Server 2005 to send mail Microsoft SQL Server 2005 (Yukon) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

f 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 is the rule of thumb when sizing TempDB?

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

[TABLE]

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