TempDb Changes in SQL Server 2016

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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.

You can specify now multiple TempDb data files during the installation

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.

CPU Cores TempDb Data Files
2 2
4 4
8 8
32 8

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.

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…

Like or share to get the source code.

Thanks for your time,

-Klaus

6 Comments

  • The script is not the right one, would you update to the new one?

    • Klaus Aschenbrenner

      Sorry, I’ve just corrected the link – thanks for the info 🙂

      -Klaus

  • “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.”

    So if we do configure it right, based on # of cores, they would of course need to be on different disks. Otherwise, it’s pointless to have multiple. Correct?

  • Hi I am a fan of wait and queue statistics. You have a wait type called PAGELATCH. If you have contention on the allocation pages on TEMPDB you get more PAGELATCH waits. My customers usually do not have waits of type PAGELATCH at all or maybe max 2%. Everybody wants to have as many tempdb files as they have cores. By splitting the IO on different files the IO system have to work harder so you get more waits of type PAGEIOLATCH_XX. If you do not have waits of type PAGELATCH why should you split the files then? It could get you verse performance because you make it harder for the IO system?

  • Would be great if MS were to have an optional tempdb per database, especially for TDE use cases.

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top