1

Increase SQL Server tempdb Performance

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:

 

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:

 

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:

Increased SQL Server tempdb performance!

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!

 

  • Daniel Serrano

    The main tempdb.mdf must have same size and grouth settings?