How to Performance Tune the Microsoft SQL Server tempdb Database

If your SQL Server's tempdb database is heavily used by your application(s), consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly distributed, reducing disk I/O contention issues, and speeding up SQL Server's overall performance. [6.5, 7.0, 2000, 2005] Updated 8-7-2006

*****

If you need to move the tempdb database after SQL Server is first installed, run this script to move it to a more appropriate location:

USE master
go

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf')
go

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf')
go

Where NAME refers to the logical name of the tempdb database and log files, and where FILENAME refers to the new location of the tempdb files. Once this command has run, you must restart the mssqlserver service before it takes affect. [7.0, 2000, 2005] Updated 8-7-2006

*****

If your application uses the tempdb database a lot, and causes it to grow larger than its default size, you may want to permanently increase the default size of the tempdb file to a size closer to what is "typically" used by your application on a day-to-day basis.

As you know, every time SQL Server is restarted, the old tempdb database is deleted and a new one is created. If the tempdb is set to a size smaller than what is typically used by the tempdb, and it is set to auto grow, then the tempdb has to grow to reach its “typical” size, which incurs some overhead.

By having the tempdb file set to the "typical" size when SQL Server is restarted (and when it is recreated from scratch to the size you set), you don't have to worry about the overhead of the tempdb growing during production. [7.0, 2000, 2005] Updated 8-7-2006

*****

Heavy activity in the tempdb database can drag down your application's performance. This is especially true if you create one or more large temp tables and then query or join them.

To help speed queries or joins on large temp tables, be sure the AUTOSTATS database option is turned on for tempdb, and then create one or more indexes on these temp tables that can be used by your query or joins. This means that you will need to create the temp table, and then add the appropriate index(s), for the temporary table(s) you create.

In many cases, you will find that this can substantially speed up your application. But like many performance tips, be sure you test this one to see if it actually helps in your particular situation. In some cases, the overhead of creating the index(s) is greater than the time saved by using them. Only through testing will you know which option is best in your situation. [7.0, 2000, 2005] Updated 8-7-2006