SQL Server 2016 is loaded with a lot of new enhancements and features. One of them is the default tempdb configuration chosen during setup. A properly configured tempdb database is often critical for better throughput for SQL Server applications.
When we install SQL Server 2016, the installation setup wizard does not create a single data file by default. Instead, it creates a default number of files based on the number of logical processors that setup detects on your machine, up to a maximum of 8 tempdb data files. This helps reduce contention on PFS, GAM and SGAM pages. To understand more about PFS, GAM and SGAM pages I recommend “Inside the Storage Engine: GAM, SGAM, PFS and other allocation maps”. Creating multiple data files is a great place to start, however you still need to make sure that tempdb is sized properly for your workload to avoid tempdb having to regrow each time the SQL Server service is restarted.
I have 4 core machine, and the tempdb installation tab, by default, detected four processors and configures four files, as you see in the following image:
Out of the Box Enhancements in tempdb
Before SQL Server 2016, it was recommended that users enable trace flags 1117 and 1118 for applications that have heavy tempdb utilization. The benefits for enabling the trace flags are:
- 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.
A few reference links – 2012 & 2014, 2008, 2005.
With SQL Server 2016, the functionality of trace flags 1117 and 1118 have been enabled by default for tempdb, avoiding the need to enable them manually.
Trace Flag 1118 – The below screen shot shows that the is_mixed_page_allocation_on value is 0 for only tempdb. This means the trace flag 1118 is enabled. This occurs by default.
Here is the definition of the value.
MIXED_PAGE_ALLOCATION Control Switch (ON/OFF) | IS_MIXED_PAGE_ALLOCATION_ON (sys.databases) | Description |
OFF | 0 | Tables and indexes in the database always allocate initial pages from uniform extents. |
ON | 1 | Tables and indexes in the database can allocate initial pages from mixed extents. |
Reference Link : MIXED_PAGE_ALLOCATION
Trace Flag 1117 – The below screen shot shows that the is_autogrow_all_files value is 1 for only tempdb. This indicates that it is the only system database that supports Autogrow_All_Files ON option.
Conclusion
These out-of-the-box enhancements for tempdb in SQL Server 2016 reduces tempdb contention issues by default by having multiple tempdb data files. This helps reduce contention on the PFS, GAM and SGAM pages. Don't forget to make sure that you size your data files to your workload, as that is not done automatically. Trace flags 1118 and 1117 were available in versions prior to SQL Server 2016, however, you had to manually enable them. With SQL Server 2016, this benefit is available for tempdb out of the box. The user experience for tempdb should be drastically improved for users of SQL Server 2016.
