Increase SQL Server tempdb Performance
In this article I hope to shed light on some very simple ways to increase SQL Server tempdb performance.
As an SQL developer, I’ve spent countless hours doing everything I can to optimize tables and rewrite queries for optimum performance. Despite all our efforts as developers, there are times when some simple changes to SQL Server itself, especially the tempdb, can yield enormous performance gains.
Here I want to highlight changes one can make, especially if being a DBA isn’t your specialty!
So what are some of these simple tricks to increase SQL Server tempdb performance?
Here they are in the order I think they should be performed (the rest of the SQL community may not agree) depending on your environment:
Trick #1: relocate the log files
These files store the database transactions that allow a database to be restored back to a specific point in time. SQL Server transaction log files have a file extension of .ldf.
Since all transactions are written to the log files, there is a lot of writing happening! This will slow down your server as there is resource contention for the drive.
Unfortunately, if you use SQL Server Management Studio to create a new database, the data and log files are stored on the same drive by default!
Typically the path is:
<Drive letter>:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA
This is not good.
So it’s best to move the .ldf file to another physical drive which has fast write speeds, thereby lessening resource contention for your tempdb files.
Here is SQL code that will accomplish the task of relocating the files:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE master SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('AdventureWorks2012'); ALTER DATABASE AdventureWorks2012 SET offline GO ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME=AdventureWorks2012_Log, FILENAME="L:\SQLLogs\AdventureWorks2012_log.ldf") ALTER DATABASE AdventureWorks2012 SET online GO |
Trick #2: relocate the tempdb files
The tempdb tends to be one of the most active databases on a production level SQL Server instance. Thus, as like with the log files mentioned above, the tempdb files should be moved to a different physical drive from both the log files and the production database data and log files.
Trick #3: create multiple tempdb files
By having more tempdb files, it increases the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O that SQL Server can push down to the disk level, the faster the database will run.
The general rule of thumb I like to follow is to create one additional tempdb file for each physical CPU core on the machine up until about 8 files, then gauge the performance from there and adjust as appropriate.
When adding more tempdb files, it’s important to configure the files at the same initial size and with the same growth settings. That way, SQL Server will write the data across them as evenly as possible. If the database files end up being different sizes, SQL Server
will attempt to fill the files with the most free space first in order to balance the amount of free space within all of the files.
Here is an SQL Script which will create a specified number of additional tempdb files:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
/****** OBJECTIVE: Add TempDB database files to reduce file lock conflict on high use SQL servers. METHOD: Identify where the current files are and use for the path in the script below. SIZE: Check the size of the new files - currently set at ******** 6 GB ************* SPACE - use Exec msdb..uspCollectDriveSpace to ensure there is enough space for these files. ******/ -- Ensure there is space on the drive before adding files. USE tempdb GO DECLARE @FileSize VARCHAR(12) SET @FileSize = '5734400KB' DECLARE @TempTbl TABLE (name sysname, physical_Name sysname) DECLARE @fileCount INT DECLARE @TFilePath NVARCHAR(128) DECLARE @TFile NVARCHAR(128) DECLARE @Inc INT DECLARE @numfiles INT DECLARE @cmd NVARCHAR(2000) SET @numfiles = 8 -- How many files do you want to add? INSERT INTO @TempTbl (name, physical_Name) SELECT name, LEFT(physical_Name, LEN(physical_Name) - 4) physical_Name from sys.database_files where type = 0 -- SELECT * FROM @TempTbl -- used for testing SET @Inc = 1; -- initialise "next" SELECT top 1 @TFile=name, @TFilePath=physical_Name FROM @TempTbl WHILE (@Inc <= @numfiles ) BEGIN -- PRINT 'ALTER DATABASE [tempdb] -- ADD FILE ( NAME = N''' + @TFile + Cast(@Inc as varchar(2)) + ''', -- FILENAME = N''' + @TFilePath +Cast(@Inc as varchar(2)) +'.ndf'' , -- SIZE = ' + @FileSize + ' , -- FILEGROWTH =10%)'; SET @cmd = 'ALTER DATABASE [tempdb] ADD FILE ( NAME = N''' + @TFile + Cast(@Inc as varchar(2)) + ''', FILENAME = N'''+ @TFilePath + Cast(@Inc as varchar(2)) +'.ndf'' , SIZE = ' + @FileSize + ' , FILEGROWTH = 10%)'; EXECUTE (@cmd); SET @Inc = @Inc + 1 END GO -- Display the TempDB files SELECT * FROM sys.database_files WHERE type = 0 |
Verify how much SQL Server will love you
After rejigging the files, you should be able to see first-hand through the resource monitor how much SQL Server is loving the fact you gave it more tempdb files to work with.
Here’s what happened on my development server after creating several more tempdb files:

Check out the disk usage on the multiple tempdb files! SQL Server is loving it! Imagine how much wait time and file contention there would be if we only had one or two tempdb files!
Notice how much each tempdb file is being utilized! No more significant file resource contentions.
Hopefully you’ll also have a noticeably faster, more efficient database.
And justification for a pay increase. 😉
If you have any other simple tricks, leave a comment as it’s always great to learn something new!