Tempdb the “shared resource” or the “scratch pad” or even the “waste bin” as described by many names in SQL Server has of course a lot many important things to do but by very nature of any shared resource there is always issues in concurrent usage. For example creation and deletion of temp tables.
Tempdb contention is one of the most common performance issues in the field. One of the ways around contention problem without diving too deep into how and why it occurs, is to divide the tempdb data file into multiple files.
The tribe of DBAs under the guidance of grand priests of the craft and MS engineers have implemented trace flags and multiple files and it has been one of the things you don’t intuitively but have to research a little into.
If you will or already have deployed SQL Server 2016, you would notice that one of the tab in dialogues during setup that look different is the one where you setup Tempdb distribution.
SQL Server 2016 has come a long way forward and has offered much better if not entirely perfect solution. If you look at the following screenshot and consider the explanation below you will understand the whole story.
- 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.
- 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.
- 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.
- 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.
- Initial size – is specified in MB and log file with that size is created.
- 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.
Lastly but important
- Trace Flag 1118 reduces Shared Global Allocation Map (SGAM) contention.
- Trace Flag 1117 strictly forces all data files within the filegroup to grow at the same time.
With SQL Server 2016, the functionality of trace flags 1117 and 1118 have been enabled by default.