Friday, March 06, 2009

How to configure SQL Server TempDB

As a general guideline, create one data file for each CPU on the server.
Note that a dual-core CPU is considered to be two CPUs. logical procs (hyperthreading) do not

Only create one Log file

Do not use autoshrink on TempDB

If your'e using SQL Server 2008 set Page verify to ChecksumInvestigate the possibility if you can switch off the properties ‘Auto create’ and ‘Auto Update’ statistics,it can speed up the creation of objects in TempDB. Be careful however: measure the impact these changes can have on production! Change and measure only one parameter at the time.

After installation move the TempDB database to its own disks that differ from those that are used by user databases

ALTER DATABASE TempDB
MODIFY FILE (NAME=tempdev, FILENAME= '\tempdb.mdf');
GO

ALTER DATABASE TempDB
MODIFY FILE (NAME=templog, FILENAME= '\TempLog.ldf');
GO


Restart SQL Server service (not the Windows server), to make changes permanent

Pre-allocate data and log device sizes (create files with same equal size), do not rely on small auto growth steps

If you use auto growth set it to a reasonable size in MB, not a percentage

TempDB file size default FILEGROWTH increment
0 to 100 MB growth:10 MB
100 to 200 MB growth:20 MB
200 to 1000 MB growth:50 to 75 MB
1 GB or More growth:150 to 250 MB

Measure TempDB usage over time with fn_virtualfilestats
New releases of your –vendor- software could have a different impact on TempDB load.



Bookmark and Share