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

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

Navigation menu