SQL 2016 - It Just Runs Faster: Automatic TEMPDB Configuration

  • Article

Various KBs, whitepapers and blogs have outlined the need for the creation of multiple, TEMPDB files, same sized files, trace flags and the like. All of these configuration options increase the scalability of your SQL Server.

 

In an effort to simplify the tempdb configuration experience, SQL Server 2016 setup has been extended to configure various properties for tempdb for multi-processor environments.

 

  1. A new tab dedicated to tempdb has been added to the Database Engine Configuration step of setup workflow.
  2. Configuration options:

Data Files

  1. Number of files - this will default to the lower value of 8 or number of logical cores as detected by setup. The value can be increased as required for a specific workload. The file names for secondary data files will follow the tempdb_mssql_#.ndf naming convention where # represents the file ordinal for each additional file.
  2. Initial size - is specified in MB and applies to each tempdb data file. This makes it easier to configure all files of same size. Total initial size is the cumulative tempdb data file size (Number of files * Initial Size) that will be created.
  3. Autogrowth - is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth) and applies to each file. The default value of 64MB was chosen to cover one PFS interval. Since TF 1117 is enabled by default for tempdb, all files will grow at the same time by the specified size. Total autogrowth reflects the cumulative size the database will grow each time an autogrow is triggered.

 

Note: If you specify a very large initial size or autogrowth value, ensure that Instant File Initialization is enabled for SQL Server service startup account.

 

  1. Data directories - specify multiple folders/drives to spread the data files across several volumes. Each file will be placed in a round-robin manner. For example: if you have specified 8 data files and 3 volumes - files 1,4,7 will go to vol 1; files 2,5,8 will go to vol 2; files 3,6 will go to vol 3.

Log File

  1. Initial size - is specified in MB and log file with that size is created.
  2. Autogrowth - is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth)

A default value of 64MB is provided to so that the number of Virtual Log Files (VLFs) during initial creation is a small and manageable number and with appropriate size so that the unused log space can be reclaimed easily.

 

Enabling all these configuration settings as part of the standard setup experience allows one to deploy and run at peak scalability levels.

 

The following is a screen capture from SQL Server 2016 CTP3 setup showing the various TEMPDB options and defaults setup allows.

 

clip_image001

 

Command line setup parameters:

 

If you are installing SQL Server using command line or configuration file, the above options can be specified using several new parameters as highlighted in the example below:

 

Please refer to Install SQL Server 2016 from the Command Prompt for additional information on the new parameters.

 

The command line parameters for tempdb settings are only effective in the following setup scenarios (/action):

 

Install, CompleteImage (sysprep), InstallFailoverCluster, CompleteFailoverCluster (sysprep), RebuildDatabase

 

Example:

setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES="SQL" /INSTANCENAME="SQL2016" .. /SQLTEMPDBFILECOUNT="8" /SQLTEMPDBFILESIZE="16" /SQLTEMPDBFILEGROWTH="256" /SQLTEMPDBDIR="C:\tempdb" "D:\tempdb" /SQLTEMPDBLOGFILESIZE="256" /SQLTEMPDBLOGFILEGROWTH="0" /SQLTEMPDBLOGDIR="E:\tempdblog"

 

 

'It Just Runs Faster' - Apply a new instance of SQL Server 2016 and setup assists in optimizing TEMPDB for your multi-processor environment.

 

Ajay Jagannathan - Principal SQL Server Program Manager

Bob Dorr - Principal SQL Server Development Engineer