A few weeks ago the latest CTP version of SQL Server 2016 was released: CTP 3. This preview version contains a lot of different improvements over the previous CTP. There are also some significant changes in TempDb for SQL Server 2016 that I want to cover in this blog posting.

TempDb Sizing

One of the first changes that you will encounter during the setup of SQL Server 2016 is that you are now able to configure the physical setup of TempDb during the installation process. Let’s have a detailed look at the following screenshot.

Microsoft now detects how many CPU cores are available and based on that number they automatically configure the number of TempDb files. This is very important to overcome the Latch Contention problem that normally occurs when you run TempDb just with one data file. The installation wizard uses the following formula here:

• When you have less (or equal) than 8 CPU cores, you will get as many TempDb data files as you have CPU cores.
• If you have more than 8 CPU cores, you will get 8 TempDb data files out of the box.

I have tested the installation wizard on various configurations, and the following table summarizes the various amounts of TempDb data files.

This is a huge step forward! When I perform SQL Server Health Checks it is not uncommon to see TempDb configurations with just one data file and some heavy Latch Contention problem.

If you provide multiple data directories in the wizard the files will be also allocated in a round-robin fashion between the individual folders. The only thing that I don’t really like about the new configuration is the initial size of 8 MB and the Auto Growth factor of 64 MB.

Allocations & Auto Growth

Prior to SQL Server 2016 a lot of people used the Trace Flags 1117 and 1118 which define how SQL Server allocates pages in databases and how Auto Growth operations across multiple data files are handled. In previous SQL Server versions data pages for Temp Tables were always allocated in so-called Mixed Extents, which are 64kb in size and are shared across multiple database objects (like tables and indexes).

With this approach Microsoft made sure that small tables stayed small, because the first 8 pages of a database object were always allocated in a Mixed Extent. All subsequent pages (starting with page 9) were allocated in a so-called Uniform Extents (which is again 64kb large). Every time that you allocated a Uniform Extent to a database object, the object itself immediately grew by 64kb.

When you enabled Trace Flag 1118 SQL Server, for the whole SQL Server instance, made allocations only in Uniform Extents, and just ignored Mixed Extents. With this approach it was possible to reduce Latch Contention problems on the SGAM page (Shared Global Allocation Map page) that manages Mixed Extents.

With SQL Server 2016 TempDb allocations will always happen in Uniform Extents without using Mixed Extents – but without enabling any trace flag. Allocations besides temp tables are still using Mixed Extents. The following example shows that 7 allocated pages in a temp table are directly stored in a Uniform Extent without using Mixed Extents at all.

USE tempdb
GO

CREATE 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 tempdb
INSERT INTO #HelperTable VALUES (REPLICATE('a', 8000))
GO 7

-- Enable DBCC trace flag 3604
DBCC TRACEON(3604)
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________________________________________________________________________________________________________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 up
DROP TABLE #HelperTable
GO

Trace Flag 1117 was used in the past in combination with TempDb to perform Auto Growth operations at the same time. This was also very important to make sure that the files are growing at the same time and in the same fragments. Otherwise the Proportional Fill Algorithm couldn’t work very well. With SQL Server 2016 you will also get the functionality of Trace Flag 1117 out of the box for TempDb without enabling the trace flag itself.

Summary

It took quite a long time until Microsoft finally started to tweak the SQL Server installation wizard to perform better default installations. Configuring TempDb according to the number of available CPU cores is a huge step forward. Let’s see what happens over time and if they will be also able to configure the MAXDOP, the Cost Threshold for Parallelism and the Max Server Memory setting accordingly…

Thanks for your time,

-Klaus