Tips To Optimize Tempdb in SQL Server

Tempdb is used for many operations such as user created temporary objects, internal temporary objects, version stores, ,ARS etc. It is shared across all databases and connections in SQL Server and if it is not configured properly it might become a point of contention. Optimizing them is one of the greatest way to increase SQL Server performance. Here we will discuss some important points on how to optimize Tempdb in SQL Server.

If some would know what is or can be processed in the background about the tempdb, then you could have put on the performance screw very early. Roughly speaking, the tempdb becomes more important as more databases run on the Server, as it is a central resource for a variety of activities. But who causes accesses to the tempdb?

In the first instance, there are two “objects” that causes access: One is the user and on the other hand, SQL server itself.

Contents of Tempdb

The following objects that can be created by a user in tempdb:

• Custom Tables and Indexes
• Global and local temporary tables
• Table variables
• Tables returned from functions
• System tables and indexes

The SQL Server itself uses the tempdb, for example:

• Worktables for Cursor or LOB (large objects)
• Joins for hash joins
• Sorting such as group by, order by, union, or even the creation of indexes with the specification sort_in_tempdb

Another major feature of tempdb is the version store. Since SQL Server 2005, there are two version stores: one for general version store and another for online index build version store.
The version stores organize, among others, the following:

• CTE
• MARS
• Row versioning
• Dialog administration of the Service Broker
• Triggers (deleted and inserted tables)
• Large XML values ​​(up to 2 GB max.)

How to Optimize Tempdb in SQL Server

Based on the above features, which are either organized or managed through tempdb, it now seems obvious to pay more attention to tempdb. But how? I have summarized a few rules for this:

1. The tempdb is rebuilt after each reboot: Give the tempdb a sufficiently large initial size.

2. Since the tempdb is accessed very often also write, the outsourcing of the tempdb to another (possibly own) drive (RAID 0) is a good measure for accelerated access.
Here is the 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. Give the tempdb always sufficient capacity. Leave growth on automatic. A magnification of 10% as a pi * thumb value has proven itself in practice.
Incidentally, the tempdb can grow to a maximum of 2 TB.

4. Under Options, set the recovery mode to Simple. Not everything is initially recorded in the transaction log, and on the other hand, completed transactions are immediately removed from the transaction log. Thus, the .ldf file does not continue to grow unnecessarily.

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

6. If you use several files , specify the same file sizes. This facilitates the proportional filling of the files.

7. Disable Auto-Update stats to increase the performance of Tempdb.

Conclusion

As you can see, the tempdb should be paid much more attention especially under SQL Server 2005. But even those if you host many databases on SQL Server should definitely take a closer look at the load of the tempdb and configure it accordingly. Whoever thinks of a backingup of tempdb or deleting tempdb or database mirroring or to optimize by filesets doesn’t help you out in optimizing the SQL database. The blog discusses some quick points to optimize tempdb in SQL Server.