Best practices for the SQL Server TempDB

Mark Varnas
May 27, 2020
2 Comments

Category: Performance

Item: SQL Server Error logs not optimally configured.

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?

– Check the number of data files

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

Microsoft’s best practices recommend Increase 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.

– Review the size and autogrow settings

Set a right size (you can start with 10GB) and adjust the autogrow settings to a fixed value. You can learn more about it here.

Don’t use percentages.

– Use a dedicated Hard disk drive

Most high-performance SQL servers place tempdb on a dedicated disk drive.

Do that if you can.

– Use trace flags T1117 & T1118

They will helps 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

Recommendations to reduce allocation contention in SQL Server tempdb
Working with tempdb in SQL Server 2005
Concurrency enhancements for the tempdb database

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

2 Responses

Eva says:

June 9, 2022 at 4:38 pm

Thanks, man!!!! Do you have more tips for SQL 2019 like this?

Reply
Salil says:

August 3, 2022 at 2:18 pm

I have executed above scripts but getting below error. Can you please help or send me the updated scripts.

Msg 4145, Level 15, State 1, Line 19
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ‘;’.
Msg 4145, Level 15, State 1, Line 43
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 4145, Level 15, State 1, Line 44
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 4145, Level 15, State 1, Line 46
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 102, Level 15, State 1, Line 58
Incorrect syntax near ‘1’.

Reply