Adding Additional Data Files To The TempDB Database In SQL Server

TempDB is one of four system databases within SQL Server.  The TempDB database is used, among other things, to store temporary user created objects, temporary internal objects, and manage real time re-indexing. Each SQL Server environment consist of a single TempDB database, which all user databases will share.  Due to this, TempDB can often become a point of contention when not properly configured.

There are several key configuration options to take note of when managing the TempDB database.  First and foremost is the location of the TempDB database.  This database should never reside on the physical C:\ drive.  TempDB has a tendency to grow if not managed properly.  If the data file grows beyond the available memory on the C:\ drive its game over for your SQL Server.  The best practice is to house TempDB on a separate drive space with plenty of available memory and proper alerts configured to notify you of any file growth.  It’s also important to consider disk speed when choosing the location of TempDB.  Ideally you want to choose a high performance, high speed drive, such as flash memory, to house TempDB.

So the big question is, “How do I know how many TempDB data files I should have?”  The simple answer is based on the number of logical processors available on the server.  It is recommended that you have one TempDB data file per CPU core.  For example a server with 2 duel core processors would recommend 4 TempDB data files.  With that in mind, Microsoft recommends a maximum of 8 TempDB files per SQL Server.  I’ve seen instances where more than 8 TempDB files were being used, though the performance advantage became unclear beyond 8.

The script below can be used to accomplish the task of modifying the existing TempDB data file and creating 3 additional files.

One important thing to note when adding additional data files is that you want all TempDB data files to be configured the same way.  The file SIZE should be identical across all files as well as the FILEGROWTH setting.

In the example above I am re-configuring the original tempdev file to be 2GB with a FILEGROWTH of 100MB.

In the second part of the script I am creating 3 additional files (tempdev2, tempdev3 and tempdev4), each with a 2GB initial size and FILEGROWTH of 100MB.

As you can see from the script, the TempDB data files reside on a separate R:\ drive within a TEMPDB_DATA folder.  If using this script you will need to modify the path when creating additional data files.  You want to make sure all TempDB data files reside in the same directory.

The screenshot below illustrates the results of properly configuring the existing TempDB data file and creating 3 additional data files

Once the additional files have been created, it’s not always necessary to perform a service restart.  However, in many production environments, simply modifying the filesize of the existing TempDB datafile (tempdev) is not possible without restarting services first.  Since a data file can not shrink below it’s initial size you will likely need to restart the SQL Server service within SQL Configuration Manager and then go back to the TempDB properties window and manually adjust the original data file size to match that of the newly created data files.