Spread the love

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:

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

USE master

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('AdventureWorks2012');

ALTER DATABASE AdventureWorks2012

SET offline

ALTER DATABASE AdventureWorks2012

MODIFY FILE (NAME=AdventureWorks2012_Log, FILENAME="L:\SQLLogs\AdventureWorks2012_log.ldf")

ALTER DATABASE AdventureWorks2012

SET online

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:

/******
  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

/******

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

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

-- 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:

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!

Spread the love

Increase SQL Server tempdb Performance

Trick #1: relocate the log files

Trick #2: relocate the tempdb files

Trick #3: create multiple tempdb files

Verify how much SQL Server will love you

David Lozinski