Managing TempDB database to manage performance

Recently I came across a situation where SQL Server would perform remarkably well for couple of days and then become sluggish to virtually non-responsive. A simple reboot of the server would resolve the issue for the next few days.

Although there were many issues with this database, one thing that jumped out was how the TempDB was configured. First let's discuss TempDB and the crucial role it plays in RDBMS.

TempDB is just like any other database with some caveats. It is globally available to all the users and is used for temporary or transitory storage of data and objects. For example, when you create temporary tables, temporary stored procedures, variables or cursors, they all are stored in TempDB. When you create a cursor for example, you can provide a STATIC hint which will copy the content in TempDB and then read from their.

SQL Server also uses TempDB to store internal objects. If you are using row versioning or snapshot isolation level, the versions are copied into TempDB. Work tables that are creating during sorting etc. are also stored in TempDB. Online indexing operations also store temporary resulset in TempDB database.

Needless to say, TempDB is your work horse and is extremely crucial for SQL Server to perform at optimal level. Everytime you restart your SQL Server, TempDB is automatically created using a clean copy. Any temporarily stored objects are dropped. You also cannot perform backup and restore operations on TempDB.

Now back to the problem this particular SQL Server was experiencing.

• Initial size of the TempDB data file (mdf) was set to 8 MB (default) and it was set to autogrow by 10%. In a high transaction environment this will result in TempDB trying to autogrow too often and too little. First time growth will be 10% of 8 MB, rougly 800 K. Since the growth increments are so small, once TempDB reaches 8 MB limit, it will be autgrowing often, resulting in data fragmentation. Once you restart the server, TempDB will be reset and start from scratch and repeat the same process again once it reaches 8 MB.
• TempDB was on the same disk that had all other databases. While this in itself shouldn't be a problem, but imagine 50 or so databases (as was the case here) all set to autogrow by 10% and all fighting for disk space, resulting in a lot of disk fragementation.

As I stated before, there were obviously other issues with this environment, but today we will mainly focus on TempDB.

One of the ways you could alleviate problem like this is by allocating a dedicated hard-drive for TempDB if possible. Start off with sufficient initial size based on your environment. This will vary from environment to environment. Allocate too much space and you are practically wasting space, allocate too little and TempDB is trying to grow more frequently.

In a high transaction environment, you can also distribute the data load into more than one data files. You can create secondary data files (it is recommended to use .ndf extension) and even distribute secondary data files on multiple hard drives (although drive latency may come into play here, so this is generally not recommended).

To change the initial size or autogrowth ratio (10% is sufficient if you start off with big enough initial size) or to add secondary data files, right click on TempDB > Properties > Files via Management Studio and then click on Add button to create additional files.

When adding a file, you can define file location, initial size etc.  You can also move TempDB files to a different location if you are running out of space on the hard drive or if you want to separate them from other databases. Remember, the current files will not move until you restart SQL Server, although new data/logs will be written at the new locaiton.

Moving TempDB files

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempDB, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

TempDB plays a big part in SQL Server performance and keeping your TempDB healthy is one of the crucial ways you can keep your SQL Server performing as expected.

Thank you