How to Optimize Tempdb in SQL Server

SQL Server has four databases system by default and one of them is called tempdb. The tempdb is used for many SQL operations, which include creating user-defined temporary objects, internal temporary objects and version stores, and other features like online re-indexing, multiple active record sets (MARS), and others. Optimizing the temp database for the server is one of the greatest ways to increase Server performance. Keep on reading to learn some points on how to optimize tempdb in SQL Server.

One fact to note is that everyone is using an instance shares of the same tempdb; you cannot have more than one within an instance of SQL Server but you can get detailed information about what is been done in tempdb using DMVs.

Features and Attributes of tempdb:

• Tempdb is always set in a simple recovery mode, that means the transaction log records for committed transactions are marked for reuse after every checkpoint.
• Tempdb can only have one filegroup, you can’t add more.
• Tempdb can store three types of objects: user objects, internal objects, and the version store.

How to Optimize Tempdb in SQL Server

It seems obvious to pay attention to tempdb. But how? I have summarized a few rules for this:

1. The tempdb is rebuilt after each reboot which give the tempdb a sufficiently large initial size.

2. Since the tempdb is accessed very often and outsourcing the tempdb to another drive is a good measure for accelerated access.

Script to move the tempdb files.

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME =, E: DATAtempdb.mdf ');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME =, E: Datatemplog.ldf ');

GO

3. Always give the tempdb a sufficient capacity. Leave it to grow on automatic. 

4. Set the recovery mode to a Simple. Not everything is recorded in the transaction log, means when a transaction completed it is removed from the transaction log. Therefore the log file does not continue to grow unnecessarily.

5. The creation of additional data files can optimize to get access to the data carrier, as this storage conflicts can be avoided. There is a pi * thumb rule: number of CPUs equals the number of files. This improves the access to the data carrier.

6. If you are using several files which specify the same file sizes. This facilitates the proportional filling of the files.

7. Disable Auto-Update to increase the performance of temp data in the SQL database.

Conclusion

The tempdb must be paid much more attention, especially under SQL Server 2005. Whoever thinks of backing up tempdb, deleting tempdb, database mirroring, or optimizing by filesets doesn’t help you out in optimizing the SQL database.

I hope this provided some useful and quick points to optimize tempdb in SQL Server.