Configuring TempDB for SQL Server 2016 instances

Configuring TempDB for SQL Server 2016 instances

As I troubleshoot performance problems with SQL Server, I often find that the configuration of the TempDB system database can be a cause, or a major contributor to poor performance. It is amongst the most prolific offenders and is an easy fix. In previous versions of SQL Server, TempDB was a bit of an afterthought when it came to installation however with SQL Server 2016, you can set it up the way you want from the outset, albeit with some minor restrictions.

Best practice for TempDB

Set aside 1 drive for TempDB data and log files and have nothing else on there. In some very high transaction environments, you could also consider putting the TempDB log file on its own drive but this isn’t usually needed.

Start with 8 data files for TempDB as this is the most up to date advice. Even if your server has a far higher number of cores, 8 data files should still be sufficient. If performance isn’t great and if you receive page contention, increase the number of data files. (Increasing by 4 at a time is a good number to work with). In the unlikely scenario that your server has less than 8 logical cores, then only create 1 data file for each core. You should not have more data files than cores.

Size all the files equally to avoid the allocation contention issue discussed in this Microsoft KB. This also ensures any growth will be equal amongst all data files. (More about growth below)

Forget about growth. If you do set one drive aside for TempDB, it makes no sense to leave a ton of spare space lying around to allow TempDB files to grow into at a future date. Instead, just grow the data files and log file out to fill the drive.  Some people aren’t comfortable using all the space. If you want to leave a buffer, use at least 95% of the available space. Once the files are pre-sized, just cap them and you won’t need to worry about growth again.

Configuring TempDB during installation

As I mentioned earlier, in previous versions; TempDB may have seemed like an afterthought as it was not mentioned in the installation wizard. However, this has changed in SQL Server 2016 as setup.exe now gives a tab for configuring TempDB from the outset. You should amend the values in this tab and not rely on the default values offered by Microsoft.

The TempDB database should be configured as follows:

• Number of data files should be set to the number of cores available for the instance or 8, whichever is lower. So in an 8 core or more server, this should be set to 8.
• Depending on the size of your TempDB drive, the initial data file size should be around one ninth of the available space. The GUI Caps this at 1024MB so we will need to change in T-SQL after installation if you want bigger files than this.
• Set the Data directory to the dedicated drive you have chosen. If you manage multiple environments, try to standardize this as much as possible across all servers.
• Like step 2, the initial TempDB log size should be set around one ninth of the available space. This is also capped at 1024MB so this may also need to be changed after installation if you want bigger files than this.

Fixing TempDB files to fill drive after installation

As I mentioned, for 8 data files and 1 log file, each should be around one ninth of the available drives space. As the setup GUI limits drive size to 1024 MB, we need to make some changes. The script below sets each of the 8 data files and the log file to 4GB and it stops autogrowth. If you set up your TempDB like this, it shouldn’t require much tlc.

-- Set all data files and log file to same size and prevent autogrowth
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp5', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp6', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp7', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp8', SIZE = 4194304KB, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 4194304KB, FILEGROWTH = 0 )
GO

Have fun configuring TempDB for SQL Server 2016 and I hope this was helpful. Any comments or suggestions as always, will be greatly appreciated.