Create Multiple TempDB files for best performance

Microsoft recommends that you create multiple files to reduce contention issues within the TempDB.

It is recommended that you create one file per CPU core- a server with (2) 4 core processors would need 8 data files.

Hint- to find the number of cores, go to Task Manger and count the number of Graphs under CPU Usage History

This is a very straight forward procedure in SQL 2005-

1) Open SQL Server Management Studio

2) Expand System Databases and right click tempdb and select "properties"

3) With in Properties, select "Files" and you should see the window below

4) Click the Add button (lower right)

5) As you can see below I have already added a couple data files called tempdev2- tempdev4

6) Continue adding files in this manner, making sure the Initial Size, Autogrowth and Path are all the same (SQL will create File Name)

-- For some help with setting the initial size and autogrowth size see http://msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx

7) Once you have added one file per core, click ok

Notes-

There is no need to create multiple Log files, one is sufficient.

You don't need to restart SQL in order for it use the new files, it will start using them immediately