SQL Server TempDB is a shared system database that affects the performance of the entire instance. Tempdb is one of the system databases in Microsoft SQL Server.
It’s a shared resource that holds temporary user objects, row versioning information, and internal objects created by the SQL Server engine.
SQL Server creates a fresh copy of TempDB every time the instance starts.
Configuring SQL Server TempDB
Why should you care about this?
Tempdb affects the performance of the entire SQL Server instance.
Many background processes use TempDB.
If it doesn’t perform optimally, every database is affected and the whole server slows down.
How do you configure TempDB for the best performance?
Check the number of data files
By default, a SQL Server configuration has a single data file for TempDB.
Microsoft recommends increasing the number of data files to maximize disk bandwidth and reduce contention.
You can use the script below to validate and generate the change script for your server.
- Change the @check variable to 1 to apply the change. The default value of 0 only shows 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 >= 32 SET @CORES = @CORES / 4
--Check and set tempdb files count are multiples of 4
IF @CORES > 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 > @FILECOUNT PRINT 'Commands listed below will be executed' + CHAR(10) + CHAR(13)
IF @check = 0 AND @CORES > @FILECOUNT PRINT 'Commands listed below will NOT be executed' + CHAR(10) + CHAR(13)
WHILE @CORES > @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
ENDNote: SQL Server 2016 has a built-in feature that detects the CPU core count and automatically creates the appropriate number of TempDB data files.
Need expert support for SQL Server?
Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.
Review the size and autogrowth settings
Set a sensible initial size (you can start at 10 GB) and set autogrowth to a fixed value. Don’t use a percentage.
Use a dedicated disk drive
Most high-performance SQL servers place tempdb on a dedicated disk drive.
Use trace flags T1117 and T1118
These help TempDB run faster.
Note: Microsoft made this behavior standard starting with SQL Server 2016 and later.
Related Reading
SQL Server Performance Consulting
Is Your TempDB Configuration Creating a Bottleneck?
Aryasoft evaluates your TempDB file configuration, contention points, and overall server performance end to end.
