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= 'SeparateDrive:\>path\tempdb.mdf');
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME=templog, FILENAME= ' SeparateDrive:\>path\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.
Post a Comment
Post a Comment