quote:

---

Optimizing tempdb Performance
General recommendations for the physical placement and database options set for the tempdb database include:

Allow the tempdb database to automatically expand as needed. This ensures that queries that generate larger than expected intermediate result sets stored in the tempdb database are not terminated before execution is complete.


Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected.


Set the file growth increment percentage to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb may need to constantly expand, thereby affecting performance.


Place the tempdb database on a fast I/O subsystem to ensure good performance. Stripe the tempdb database across multiple disks for better performance. Use filegroups to place the tempdb database on disks different from those used by user databases.

---

quote:

---

To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

For more information, see ALTER DATABASE (Transact-SQL) or How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).


Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.

Note:
If the production environment cannot tolerate the potential for application time-outs that may occur during autogrow operations, preallocate space to allow for the expected workload.



Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance. We recommend the following general guidelines for setting the FILEGROWTH increment for tempdb files.


tempdb file size FILEGROWTH increment
0 to 100 MB
10 MB

100 to 200 MB
20 MB

200 MB or more
10%*

* You may have to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. To avoid potential latch time-outs, we recommend limiting the autogrow operation to approximately two minutes. For example, if the I/O subsystem can initialize a file at 50 MB per second, the FILEGROWTH increment should be set to a maximum of 6 GB, regardless of the tempdb file size. If possible, use instant database file initialization to improve the performance of autogrow operations.


Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.


Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.


Make each data file the same size; this allows for optimal proportional-fill performance.


Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.


Put the tempdb database on disks that differ from those that are used by user databases.

---

quote:

---

Updated: 14 April 2006

This topic describes how to move system databases in SQL Server 2005. Moving system databases may be useful in the following situations:

Failure recovery. For example, the database is in suspect mode or has shut down because of a hardware failure.


Planned relocation.


Relocation for scheduled disk maintenance.


The following procedures apply to moving database files within the same instance of SQL Server. To move a database to another instance of SQL Server or to another server, use the backup and restore or detach and attach operations.

The procedures in this topic require the logical name of the database files. To obtain the name, query the name column in the sys.master_files catalog view.

Important:
If you move a system database and subsequently rebuild the master database, you must move the system database again because the rebuild operation installs all system databases to their default location. For more information about rebuilding the master database, see "Rebuilding System Databases, Rebuilding the Registry" in How to: Install SQL Server 2005 from the Command Prompt.



Planned Relocation and Scheduled Disk Maintenance Procedure
To move a system database data or log file as part of a planned relocation or scheduled maintenance operation, follow these steps. This procedure applies to all system databases except the master and Resource databases.

For each file to be moved, run the following statement.

Copy Code
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )


Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.


Move the file or files to the new location.


Restart the instance of SQL Server or the server. For more information, see Starting and Restarting Services.


Verify the file change by running the following query.

Copy Code
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

---