The progress of SQL Server's TempDb in 2016 and tempdb in 2016

The progress of SQL Server's TempDb in 2016 and tempdb in 2016

A few weeks ago, the latest CTP version of SQL Server 2016 has been released: CTP 2.4 (currently CTP 3.0 ). This preview version contains many different improvements compared to the previous CTP version. In this article, I will talk about the significant improvement in SQL Server 2016 and TempDb.

TempDb Customization

During the installation of SQL Server 2016, the first change you will encounter is that during the installation process, you can now configure the physical configuration of TempDb. Let's take a closer look at the screenshot below.

Microsoft now detects several available CPU cores and automatically configures the number of TempDb files based on this number installer. This is very important to overcome Latch Contention problem. Generally, when you run TempDb, only one data file will happen. Here, the installation wizard uses the following formula:

If you have less than or equal to 8 CPU cores, you will get the same number of TempDb data files as the number of CPU cores. If you have more than 8 CPU cores, you will get 8 out-of-the-box TempDb data files.

I tested the Installation Wizard on different configurations. The following table summarizes the different numbers of TempDb data files.

CPU kernel TempDb Data File

2 2

4

8

32 8

This is a huge improvement! When I perform SQL Server health check, TempDb only has one data file, and Latch Contention problem is common.

If you provide multiple data paths in the wizard, you can allocate them cyclically between folders. One thing I don't like is the initial size of 8 MB and the automatic growth rate of 64 MB.

Allocation and automatic Growth

Before SQL Server 2016, many people used 1117 and 1118 trace tags to define how SQL Server allocates pages in the database and how to process automatic growth among multiple data files. In earlier versions of SQL Server, the data pages of temporary tables are always allocated in the so-called Mixed zone (Mixed Extends). The size of the Data Pages is 64 kB in multiple database objects (such as tables and indexes) shared.

Using this method, Microsoft ensures that small tables are kept small, because 1st 8 pages of the database are always allocated in the hybrid zone. The next page (starting from page 9th) is allocated in the so-called unified zone (also 64 KB. Each time you allocate a unified area to the database object, the object itself increases by 64 KB.

When you enable the SQL Server 1118 trace tag, the entire SQL Server instance is allocated only in the unified zone, and the hybrid zone is ignored. By using this method, Latch Contention problem can be reduced on the SGAM (Shared Global Allocation Map) page to manage hybrid zones ).

In SQL Server 2016, TempDb allocation always occurs in the unified zone, instead of hybrid zone-No trace flag needs to be enabled. In addition to temporary tables, hybrid partitions are used for allocation. The following example shows that the seven allocated pages in the temporary table are directly stored in the unified partition without using the hybrid partition.

USE tempdbGOCREATE TABLE #HelperTable(  Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,  Col2 CHAR(8000) NOT NULL)GO-- Insert 7 records, this allocates 7 pages in tempdbINSERT INTO #HelperTable VALUES (REPLICATE('a', 8000))GO 7-- Enable DBCC trace flag 3604DBCC TRACEON(3604)GO-- Retrieve the temp table name from sys.tablesSELECT name FROM sys.tablesWHERE name LIKE '#HelperTable%'GO-- Retrieve the first data page for the specified table (columns PageFID and PagePID)DBCC IND(tempdb, [#HelperTable________________________________________________________________________________________________________00000000000B], -1)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, 5, 33, 3)GO-- Clean upDROP TABLE #HelperTableGO

In the past 1117, the trace tag and TempDb were combined for automatic growth at the same time. It is important to ensure that files grow in the same region at the same time. Otherwise, the proportional Filling algorithm (proportional fill algorithm) cannot play its due role. When SQL Server 2016 is used, you directly have the 1117 trace tag function, which does not need to be enabled.

Summary

After a long time, Microsoft finally began to perform better default configuration in the SQL Server Installation Wizard. Configuring TempDb based on the number of available CPU cores is a huge improvement. Let's take a look at the next version to provide the ability to configure MAXDOP, parallel overhead threshold, and maximum server memory according to actual conditions...

Articles you may be interested in:

• SQL Server misunderstanding: on the 30 th day, the number of TempDB files in 12th days should be consistent with the number of CPUs.
• Analysis and examples of the log mechanism of tempdb in SqlServer
• SQL Server database prompts "tempdb" log is full solution
• The important role of tempdb in SQL Server
• SQL Server prompts "column prefix tempdb. invalid: Table name not specified". Solution
• How to change the location of tempdb in SQL Server 2005 database
• What is the impact of Tempdb on SQL Server performance optimization?