Best Practices for tempdb

The tempdb is a system database which is available for all the users connected to that instance of SQL Server.
This database is used to hold

• Temporary User Objects
• Internal Objects created by the Database Engine
• Row Versions that are generated by data Modifications

Since tempdb is used by all users and the system by itself for all their temporary operations, it is very much important that we optimize the tempdb and follow best practices for getting better performance out of it.

Below are some best practices that can be followed for tempdb

• Create the number of data files for tempdb based on the number of CPU's present on that system. Example: if the system has 4 CPUs then create 4 data files for tempdb with one Log file.
• Place tempdb files on the fastest available Drive.
• Isolate tempdb on a separate disk from other databases.
• Make all the data files of tempdb the same size.
• Disable autogrow option for all tempdb files and make sure you have enough space in them.
• Make sure to Commit or Rollback the transactions and if not done, then any space allocated for that transaction may not be released.

Moving the tempdb database

There are cases when you might want to move tempdb database from an existing drive to a new drive.

1. When the drive is full and you are in a situation where you cannot extend that drive.
2. Move tempdb to a separate drive to increase its performance.

This is a simple process and cannot be done by detaching and attaching the database, as we cannot attach or detach a system database.
Also we need to restart the SQL services.

Here is the process how we can move the tempdb to a new location.

1. First get the list of tempdb files by using this query
select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'
2. Then for each tempdb file that you need to move, execute statements like below

Alter Database tempdb modify
file (NAME = 'tempdev' , 
FILENAME = 'Drive:\Path\tempdb.mdf') -- Mention the new location

Alter Database tempdb modify
file (NAME = 'templog' , 
FILENAME = 'Drive:\Path\templog.ldf') -- Mention the new location

3. Stop SQL Services
4. Start SQL Services
5. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'