Manage Learn to apply best practices and optimize your operations.

SQL Server tempdb configuration simplified in 2016 release

With SQL Server 2016, tempdb will get enhancements to help speed up applications. Find out about the changes and learn how to properly configure tempdb in SQL Server 2016.

One of the first considerations a database administrator has after a SQL Server upgrade is properly sizing the tempdb database files that hold temporary tables and stored procedures, plus other items that aren't saved when a SQL Server system is shut down and then restarted. SQL Server 2016 comes with major tempdb enhancements designed to speed up applications. Let's look at how to set up tempdb in SQL Server 2016 to get the most out of your database servers.

One of the problems DBAs face is that applications that extensively use tempdb can cause SQL Server to experience latch contention issues when allocating new pages in the temporary database. If heavy contention occurs, tempdb-related queries may become unresponsive for short periods of time. These symptoms indicate that SQL Server tempdb needs to be resized to improve overall query performance. To start that process, or to size tempdb for a new installation, DBAs can find information on the tempdb resources that a SQL Server instance needs by running the sys.dm_exec_request or sys.dm_os_waiting_tasks dynamic management view queries in Transact-SQL.

Then you can move on to tempdb configuration. How many data files you configure for tempdb depends upon the number of logical processors allocated to an instance of SQL Server. With eight or fewer logical processors, the tempdb data file total should be the same as the logical processor count. If you have six logical processors, for example, you need to configure six data files for tempdb. With more than eight logical processors, you should have eight data files -- the maximum number recommended by Microsoft unless you run into contention issues.

Configuring tempdb prior to SQL Server 2016

In previous versions of SQL Server, the installation setup created a single tempdb data file by default. The default initial size of a SQL Server tempdb data file was 8 MB, with an autogrowth setting of 10% for automatically increasing the size of the file by that percentage as needed. Similarly, the tempdb transaction log file had a default initial size of 1 MB and a 10% autogrowth setting (see Figure 1).

Default tempdb
Figure 1: Default tempdb configurations for SQL Server 2014 and earlier.

Tempdb enhancements in SQL Server 2016

SQL Server 2016's installation and setup wizard now automatically detects the number of CPU cores available and uses that number to add the necessary number of tempdb data files up to a maximum of eight. This should help avoid SQL Server latch contention problems, which generally occur when you have configured tempdb to use only one data file. In addition, both SQL Server 2016 tempdb data and log files will now have an initial size of 8 MB and a default autogrowth size of 64 MB.

Also, starting with SQL Server 2016, you don't need to enable trace flags 1117 and 1118 at the server level to control the growth of tempdb files and the allocation of data pages to new database objects. Now, if there are multiple tempdb data files available, they'll all automatically grow simultaneously according to your autogrowth settings. Moreover, all page allocations in tempdb will come from uniform extents of eight physically contiguous pages tied to a single object, instead of pulling individual pages together from mixed sets of extents.

Microsoft has also vastly improved the graphical user interface for configuring tempdb in SQL Server 2016. The tempdb screen in the Database Engine Configuration interface lets you easily configure the parameters of the temporary database, including the number of data files, the initial size and autogrowth settings for the data files and log file, and the directory locations for the files (see Figure 2). Furthermore, you can specify multiple volumes for tempdb files and allocate files across directories in a round-robin fashion.

Database engine configuration
Figure 2: Database engine configuration screen for SQL Server 2016 tempdb.

Manual process for tempdb configuration

If you plan to install SQL Server 2016 from the command line, you'll have to add the parameters in Figure 3 to configure multiple tempdb data files and set the initial size and autogrowth settings.

Configuration parameters
Figure 3: Configuration parameters for tempdb in SQL Server 2016.

Figure 4 shows an example of the command-line code required to configure tempdb with four data files that have the default file size and autogrowth settings.

Command-line code
Figure 4: Command-line code to configure tempdb with four data files.

Whichever method you use, properly configuring tempdb in SQL Server to avoid latch contention problems is critical for any production database server. SQL Server 2016 makes that much easier to do, since its installation and setup wizard can detect the available cores and recommend the right configuration according to Microsoft's deployment best practices. This enhancement will help DBAs to better configure tempdb in newer installations, as well as in upgrades to the new version of the database.

Next Steps

Microsoft made changes to AlwaysOn Availability Groups in SQL Server 2016

SQL Server 2016 has new and improved In-Memory OLTP features

PolyBase connects SQL Server 2016 to external data sources

Dig Deeper on Microsoft SQL Server Installation