TempDB Database Configuration#

TempDB is critical to performance as it is used by several users and system actions such as cursors, temp tables, hash table for sorts, re-indexing, and so on. It is recommended that you handle TempDB before you separate your OLTP data and log files.

TempDB is typically one of the most active databases on a production SQL Server instance, so it is highly recommended that a separate LUN for the TempDB is used. The TempDB data and log files should be placed on different physical drives, apart from your production database data and log files. Because TempDB is so active, it is also a good idea to make sure the drives are protected with SSD or striped with RAID 10.

The Microsoft SQL Server Customer Advisory Team (SQLCAT) has recommended that TempDB should have one data file for each CPU core. However, this recommendation is best suited for very heavy workloads. It is more commonly recommended that TempDB have a 1:2 or 1:4 ratio of data files to CPU cores. As with most performance recommendations, this is a general guideline. The requirements for your system will vary. If you are unsure of how many data files to use for TempDB, a common recommendation is to start with four data files. Typically, one log file is enough for TempDB. (For more in-depth TempDB recommendations, see the resources listed in the Breaking Up SQL Server Databases into Multiple Files section.)

By default, TempDB files are placed on the same drive as the SQL Server binaries. Even if the user chooses a custom install, the TempDB is still placed in the same drive as the other data files. Instead, the TempDB data files should be stored on their own dedicated drive. This must be corrected by first moving the TempDB to its own separate drive. In the example below, we put the data file on the T: drive and the log file on the L: drive. Note that the directory paths must already exist before running the command.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME='tempdev', FILENAME='T:\MSSQL\DATA\tempDB.mdf', SIZE = 1mb)
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME='templog', FILENAME='L:\MSSQL\LOGS\templog.LDF', SIZE = 1mb)
GO

In the example above, we only set a 1mb file size because SQL Server behaves uncommonly. Even though we are instructing it to use a different drive letter, it will look for this amount of free space on the current TempDB drive. If the SQL Server had been installed on the server's C: drive, for example, and we had tried to create a 10GB TempDB file on a T: drive, that SQL command would have failed if 10GB of free space were not on the C: drive.

After the command above is run successfully, it is necessary to restart the SQL Server instance. That will create the new TempDB file on the new drive. The old TempDB file will have to be manually deleted from the original drive, because SQL Server will not do it itself.

Now that TempDB is on the right drive, expand it to the full size according to your preferences and then create additional TempDB files using the rule explained above (related with the number of processor cores). If you have got a quad-socket, quad-core box, i.e. 16 cores, it is recommended to use 4 to 8 TempDB files.

To create one additional TempDB data file it is necessary to run the command below:

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME='T:\MSSQL\DATA\tempdev2.ndf', SIZE = 10GB, FILEGROWTH = 0)
GO

It is important to highlight the fact that file growth has not been enabled. You should proactively create the TempDB files at their full sizes in order to avoid drive fragmentation. If you have a dual-cpu quad-core server (8 cores total) and an 80GB array for TempDB data, you should create eight 10GB files for TempDB. As a result, each file will be contiguous. If you create them as smaller files and let them grow automatically, then the disk will be fragmented because the files will be growing at random times. In addition, you could end up with differently sized TempDB files if one of them happened to grow faster than the rest. That is why we strongly recommend that all the TempDB files are pre-grown beforehand and then get them at exactly the right size.