On the progress of tempdb in SQL Server 2016 _mssql

A few weeks ago, the latest CTP version of SQL Server 2016 had been released: CTP 2.4 (CTP 3.0 is now). This preview version contains a number of different improvements than the previous CTP. In this article I'll talk about a significant increase in SQL Server 2016,tempdb.

tempdb customization

During the SQL Server 2016 installation, the first change you will encounter is during the installation process and now you can configure the physical configuration of tempdb. We can look at the screenshot below in detail.

Microsoft now detects several available CPU cores and automatically configures the number of tempdb files based on this digital installer. This is important to overcome the latch contention problem (Latch contention problem), usually when you run tempdb, only one data file will occur. Here the Setup Wizard uses the following formula:

When you have less than 8 CPU cores, you will get the number of tempdb data files that are the same number as your CPU cores. If you have more than 8 CPU cores, you will get 8 tempdb data files in the Open box.

I tested the Setup Wizard on different configurations, and the following table summarizes the different numbers of tempdb data files.

CPU Kernel tempdb data file

2 2

4 4

8 8

32 8

This is a great progress! When I perform a SQL Server health check, tempdb has only one data file, and there are some latch contention issues (Latch contention problem) that are common.

If you provide multiple data paths in the wizard, you can circulate (Round-robin) among the various folders. One thing I don't like is the new configuration 8MB Initial size and 64MB automatic growth rate.

Allocation and automatic growth

Before SQL Server 2016, many people used the 1117 and 1118 trace tags to define how SQL Server allocates pages in a database and how to handle automatic growth operations across multiple data files. In previous versions of SQL Server, the data pages for temporary tables were allocated in the so-called mixed zone (Mixed Extends), which was 64KB shared among multiple database objects, such as tables and indexes.

Using this method Microsoft ensures that small tables remain small because the 1th 8 page of the database is always allocated in the mixed area. The next page (starting on page 9th) is allocated in the so-called unified area (also 64k size). Each time you assign a unified area to a database object, the object itself grows 64kb immediately.

When you enable SQL Server's 1118 trace flag, the mixed area is ignored for the entire SQL Server instance, only in the unified area. Using this method, you can reduce the latch contention issues (Latch contention problem) on the SGAM (Shared Global Allocation map) page that manages the mixed zone.

In SQL Server 2016, the tempdb allocation always occurs in the consolidated area without using a mixed zone-no trace flags need to be enabled. Use a mixed zone in addition to temporary table allocations. The following example shows that 7 allocated pages in a temporary table are stored directly in the consolidated area without using a mixed zone at all.

In the past 1117 trace tags and tempdb are combined to simultaneously automate growth operations. It is important to ensure that files grow at the same time in the same district. Otherwise the proportional filling algorithm (proportional fill algorithm) cannot play its part. With SQL Server 2016, you have a direct 1117 trace of this feature, without the need to enable it.

Summary

After a long time, Microsoft finally started to make a better default configuration in the SQL Server Setup Wizard. Configuring tempdb based on the number of available CPU cores is a huge improvement. Let's take a look at the next release that will provide the ability to configure MAXDOP, parallel overhead thresholds, server maximum memory, and so on.