I was going trough a nice article , so sharing apart from it regarding the recommendations for TempDB.
Source: http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx
Pre-size TempDB appropriately. Leave autogrow on with instant file initialization enabled, but try to configure the database so that it never hits an autogrow event. Make sure the autogrow growth increment is appropriate.
· Follow general IO recommendations for fast IO.
· If your TempDB experiences metadata contention (waitresource = 2:1:1 or 2:1:3), you should split out your data onto multiple files. Generally you will want somewhere between 1/4 and 1 file per physical core. If you don't want to wait to see if any metadata contention occurs you may want to start out with around 1/4 to 1/2 the number of data files as CPUs up to about 8 files. If you think you might need more than 8 files we should do some testing first to see what the impact is. For example, if you have 8 physical CPUs you may want to start with 2-4 data files and monitor for metadata contention.
· All TempDB data files should be of equal size.
· As with any database, your TempDB performance may improve if you spread it out over multiple drives. This only helps if each drive or mount point is truly a separate IO path. Whether each TempDB will have a measurable improvement from using multiple drives depends on the specific system.
· In general you only need one log file. If you need to have multiple log files because you don't have enough disk space on one drive that is fine, but there is no direct benefit from having the log on multiple files or drives.
· On SQL Server 2000 and more rarely on SQL Server 2005 or later you may want to enable trace flag -T1118.
· Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.
Source: http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx
Pre-size TempDB appropriately. Leave autogrow on with instant file initialization enabled, but try to configure the database so that it never hits an autogrow event. Make sure the autogrow growth increment is appropriate.
· Follow general IO recommendations for fast IO.
· If your TempDB experiences metadata contention (waitresource = 2:1:1 or 2:1:3), you should split out your data onto multiple files. Generally you will want somewhere between 1/4 and 1 file per physical core. If you don't want to wait to see if any metadata contention occurs you may want to start out with around 1/4 to 1/2 the number of data files as CPUs up to about 8 files. If you think you might need more than 8 files we should do some testing first to see what the impact is. For example, if you have 8 physical CPUs you may want to start with 2-4 data files and monitor for metadata contention.
· All TempDB data files should be of equal size.
· As with any database, your TempDB performance may improve if you spread it out over multiple drives. This only helps if each drive or mount point is truly a separate IO path. Whether each TempDB will have a measurable improvement from using multiple drives depends on the specific system.
· In general you only need one log file. If you need to have multiple log files because you don't have enough disk space on one drive that is fine, but there is no direct benefit from having the log on multiple files or drives.
· On SQL Server 2000 and more rarely on SQL Server 2005 or later you may want to enable trace flag -T1118.
· Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.
No comments:
Post a Comment