I took control of a new server recently and noticed that Tempdb was not configured to best practice. It was not on its own drive and it consisted of one data file and one log file…The server had a couple of cores, I’m not sure how relevant this is anymore but SQL server 2000 it was recommended you create a TEMPDB data file for CPU…I’m not sure that you need a file for each CPU/core in later versions but as Brad McGehee says in his TEMPDB optimisation chapter in MVP deep dives (This is my review of the book) it does give you a place to start.

My first script here moves both the current log and data file to its new home.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'S:\SQLTEMP\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'S:\SQLTEMP\templog.ldf');
GO

Once you have run this you will need to restart SQL Server for the changes to take affect. Also you will have two copies of the old TEMPDB files in the in the old location, I like to delete these after the restart  to avoid confusion later.

The next script below adds an additional data file to my tempdb database, this ensures the number of data files in tempdb matches the number of cores on my machine. The reason for multi-data files is a reduction in contention on each file as IO requests are spread across the multiple files. This can be beneficial, even if the files reside on the same volume as is the case here, if you put each file on a volume of their own the performance gain is even greater…With a greater cost attached of course.

Anyhow, the following script adds an additional data file to the tempdb database. Its important to keep the size of each data file identical preferably sized according to TEMPDB usage. Also ensure that the same auto grow settings are set for each file. The algorithm SQL Server uses for filling pages will distribute the load equally if the files are the same size.

 
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 153600KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'TempDev2', FILENAME = N'S:\SQLTEMP\TempDev2.ndf' , SIZE = 153600KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 51200KB )
GO

As with all things please take the time to test the optimal configuration in your environment, as multiple files may or may not benefit you.