Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

TempDB – Relocate the TempDB Database Files

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.

Comments

Posted by Glenn Berry on 28 March 2010

I think Microsoft still recommends one TempDB data file per CPU core for SQL Server 2005/2008.  I usually max out at eight TempDB data files.  I also like to put the TempDB log file on the same drive as the user database log files.

Posted by ThomasLL on 29 March 2010

At a recent trip to a MTC (Microsoft Training Center) in Dallas, the technology architect suggested no more than 8 files while we had 16 cores.

God Bless,

ThomasLL

Posted by Jason Brimhall on 29 March 2010

I agree with Glenn.  I max out at 8 files as well.

Posted by Anonymous on 30 March 2010

Pingback from  How Do I Find Out If My Computer Has Been Hacked? | Host Rage

Leave a Comment

Please register or log in to leave a comment.