SQL Server Health Check

TempDB Best Practices for Optimal Performance

Mark Varnas
No comments

What is the tempdb database?

tempdb is one of the system databases in Microsoft SQL Server.

It’s a global resource that holds temporary user objects, row versioning information and internal objects that are created by the SQL Server Engine.

SQL Server creates a clean copy of the tempdb every time the instance is started.

Why should you care about it?

tempdb affects the performance of all SQL Server.

Many background processes use tempdb.

If it does not perform optimally, all databases are affected and will slow the whole server down.

How to adjust tempdb for the best performance?

  1. Check the number of data files.

    SQL Server by default configuration has a single data file for tempdb.

    Microsoft’s best practices recommend increasing the number of data files to maximize disk bandwidth and reduce contention.

    You can use the script below to verify and generate the change script for your server.

    Change the variable @check to 1 to implement the change. The default value 0 only shows the information.
DECLARE @check BIT

SET @check = 0 --For information set 0, for change 1

DECLARE @BASEPATH NVARCHAR(300)
DECLARE @SQL_SCRIPT NVARCHAR(1000)
DECLARE @CORES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT

-- TempDB mdf count equal logical cpu count
SELECT @CORES = cpu_count
FROM sys.dm_os_sys_info

PRINT 'Logical CPU count ' + CAST(@CORES AS NVARCHAR(100))

IF @CORES BETWEEN 9
		AND 31
	SET @CORES = @CORES / 2

IF @CORES & gt;= 32
	SET @CORES = @CORES / 4

--Check and set tempdb files count are multiples of 4
IF @CORES & gt;8
	SET @CORES = @CORES - (@CORES % 4)
SET @BASEPATH = (
		SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
		FROM master.sys.master_files
		WHERE database_id = 2
			AND FILE_ID = 1
		)
SET @FILECOUNT = (
		SELECT COUNT(*)
		FROM master.sys.master_files
		WHERE database_id = 2
			AND TYPE_DESC = N'ROWS'
		)

SELECT @SIZE = size
FROM master.sys.master_files
WHERE database_id = 2
	AND FILE_ID = 1

SET @SIZE = @SIZE / 128

SELECT @GROWTH = growth
FROM master.sys.master_files
WHERE database_id = 2
	AND FILE_ID = 1

SELECT @ISPERCENT = is_percent_growth
FROM master.sys.master_files
WHERE database_id = 2
	AND FILE_ID = 1

IF @ISPERCENT = 0
	SET @GROWTH = @GROWTH * 8

--Current situation
PRINT 'Needed ' + CAST(@CORES AS NVARCHAR(100)) + ' TempDB data files, now there is ' + CAST(@FILECOUNT AS NVARCHAR(100)) + CHAR(10) + CHAR(13)

IF @check = 1
	AND @CORES & gt;
	@FILECOUNT

PRINT 'Commands listed below will be executed' + CHAR(10) + CHAR(13)

IF @check = 0
	AND @CORES & gt;
	@FILECOUNT

PRINT 'Commands listed below will NOT be executed' + CHAR(10) + CHAR(13)

WHILE @CORES & gt;
	@FILECOUNT

BEGIN
	SET @SQL_SCRIPT = N'ALTER DATABASE tempdb

                    ADD FILE (

                                   FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@CORES AS NCHAR)) + '.ndf'',

                                   NAME = tempdev' + RTRIM(CAST(@CORES AS NCHAR)) + ',

                                   SIZE = ' + RTRIM(CAST(@SIZE AS NCHAR)) + 'MB,

                                   FILEGROWTH = ' + RTRIM(CAST(@GROWTH AS NCHAR))

	IF @ISPERCENT = 1
		SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
	ELSE
		SET @SQL_SCRIPT = @SQL_SCRIPT + 'KB'

	SET @SQL_SCRIPT = @SQL_SCRIPT + ')'

	IF @check = 1
		EXEC (@SQL_SCRIPT)

	PRINT @SQL_SCRIPT

	SET @CORES = @CORES - 1
END

Note: SQL Server 2016 has a built-in feature that detects the number of CPU cores and automatically creates the appropriate amount of tempdb data files.

  1. Review the size and autogrow settings.
    Set a right size (you can start with 10GB) and adjust the autogrow settings to a fixed value.
    Here is a post on how to change autogrowth settings. Don’t use percentages.
  2. Use a dedicated Hard Disk Drive (HDD).
    Most high-performance SQL Servers place tempdb on a dedicated disk drive.
    Do that if you can.
  3. Use trace flags T1117 & T1118.
    They will help tempdb perform faster. Check out our post about trace flags.

Note: Microsoft made this behavior standard in SQL Server 2016 and newer versions.

More information

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials