Adding Additional Data Files To The TempDB Database In 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.
With that said, there is really no set rule for how many TempDB files you should have to maximize performance. Each system is different. The only real way to know what will work best for your system is to scale out the number of TempDB files one at a time and analyze the performance after each additional file is added.
The script below can be used to accomplish the task of modifying the existing TempDB data file and creating 3 additional files.
/* Re-sizing TempDB */
USE [master];
GO
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE=2GB, FILEGROWTH = 100);
GO
/* Adding three additional files */
USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE
(NAME = N'tempdev2', FILENAME = N'R:\TEMPDB_DATA\tempdev2.ndf' , SIZE = 2GB , FILEGROWTH = 100);
ALTER DATABASE [tempdb] ADD FILE
(NAME = N'tempdev3', FILENAME = N'R:\TEMPDB_DATA\tempdev3.ndf' , SIZE = 2GB , FILEGROWTH = 100);
ALTER DATABASE [tempdb] ADD FILE
(NAME = N'tempdev4', FILENAME = N'R:\TEMPDB_DATA\tempdev4.ndf' , SIZE = 2GB , FILEGROWTH = 100);
GO
One important thing to note when adding additional data files is that you generally 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. Though these settings will very likely need to be changed to meet the needs of your database, this should give you a pretty good idea of how to script out the changes that you will need.
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. In many cases you probably want all TempDB data files reside in the same directory, though there are situations where you may want to split these out to different drives. Situations like this can range from large scale SQL Server environments to scenarios with limited disk space. Each deployment is unique and you will need to consider these these options before pulling the trigger.
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.