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.