Significant improvement in tempdb of SQL Server 2016

Time:2020-7-8

A few weeks ago, the latest CTP version of SQL Server 2016 was released: CTP 2.4 (now CTP 3.0). The summary of key features of SQL Server 2016 ctp2.3 will not be discussed here. Please refer to relevant information for specific contents. This preview contains many different enhancements compared to the previous CTP. In this article, I will talk about the significant improvement in tempdb for SQL Server 2016.

Tempdb customization

The first change you will encounter during the installation of SQL Server 2016 is that you can now configure the physical configuration of tempdb during the installation process. We can take a closer look at the screenshots below.

Microsoft now detects several available CPU cores and automatically configures the number of tempdb files based on this number. This is very important to overcome the 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 8 or less CPU cores, you get the same number of tempdb data files as your CPU cores.
If you have more than eight CPU cores, you will get eight tempdb data files out of the box.
I tested the setup wizard on different configurations, and the following table summarizes the different numbers of tempdb data files.

Tempdb data file of CPU core

2        2

4        4

8        8

32        8

This is a great progress! When I do a SQL server health check, tempdb has only one data file, and some latch contention problems are common.

If you provide multiple data paths in the wizard, you can round robin through the folders. One thing I don’t like is that the new configuration has an initial size of 8MB and an automatic growth rate of 64MB.

Distribution and automatic growth

Prior to SQL Server 2016, many people used 1117 and 1118 trace tags to define how SQL server allocates pages in a database and how to handle automatic growth among multiple data files. In previous versions of SQL server, the data pages of temporary tables were allocated in so-called mixed extensions, which were 64 kb in size and shared among multiple database objects (such as tables and indexes).

Using this method, Microsoft ensures that the small tables remain small because the first eight pages of the database are always allocated in the mixed zone. The next pages (starting on page 9) are allocated in the so-called unified area (also 64K size). Every time you assign a uniform area to a database object, the object itself grows by 64KB immediately.

When you enable the 1118 trace flag of SQL server, only the unified zone is allocated for the entire SQL server instance, and the mixed zone will be ignored. This method can reduce the latch contention problem on the SGAM page.

In SQL Server 2016, tempdb allocation always takes place in a unified zone, without using a mixed zone — no trace markers need to be enabled. In addition to the temporary table, the allocation still uses the mixed zone. The following example shows that the 7 allocated pages in a temporary table are directly stored in a unified zone, without using a mixed zone at all.


USE tempdb
 GO
 CREATE TABLE #HelperTable
 (
  Col INT IDENTITY(, ) PRIMARY KEY NOT NULL,
  Col CHAR() NOT NULL
 )
 GO
 -- Insert records, this allocates pages in tempdb
 INSERT INTO #HelperTable VALUES (REPLICATE('a', ))
 GO 
 -- Enable DBCC trace flag 
 DBCC TRACEON()
 GO
 -- Retrieve the temp table name from sys.tables
 SELECT name FROM sys.tables
 WHERE name LIKE '#HelperTable%'
 GO
 -- Retrieve the first data page for the specified table (columns PageFID and PagePID)
 DBCC IND(tempdb, [#HelperTable________________________________________________________________________________________________________B], -)
 GO
 -- Dump the IAM page of the table TestTable retrieved by DBCC IND previously
 -- No pages are allocated in Mixed Extents, a complete Uniform Extent is allocated.
 DBCC PAGE (tempdb, , , )
 GO
 -- Clean up
 DROP TABLE #HelperTable
 GO

In the past, 1117 trace tags and tempdb were combined for simultaneous automatic growth operation. It’s important to make sure that files grow in the same area at the same time. Otherwise, the proportional fill algorithm cannot play its due role. With SQL Server 2016, you can directly have the function of 1117 trace mark without enabling it.

Summary

After a long time, Microsoft finally started to make better default configuration in SQL Server Installation Wizard. Configuring tempdb based on the number of available CPU cores is a big step forward. Let’s take a look at the next version, which will provide the ability to configure maxdop, parallel overhead threshold, maximum server memory, etc

Thank you for your attention!