The New TempDB Tab in the SQL Server 2016 Installer

The TempDB tab on the SQL Server 2016 installer is a really nice addition to the install wizard (along with the tick box to enable Instant File Initialisation). I was interested to see if I could remove the manual step of setting the TempDB files to something sensible via a script after the install.

SQL 2016 TempDB Config
This new tab lets you choose the number of files, their initial size and their auto growth size. You can spread files over multiple drives and configure the log file separately. One thing I found disappointing, was that the defaults are still really bad. With that said Number of Files has a default of 4 which is nice. So, like most people, I altered these default settings to better suit my requirements.

TempDB Configuration Limits

Ah red is not good. I’ve configured my TempDB files in what seem like a fairly reasonable way but the installer is not happy.

The messages are as follows:

The value XXX for the TempDB file size exceeds the allowed limit. Setup allows the value up to 256MB because of the impact to installation time. You can set it to a higher size after the installation.
The value XXX for the TempDB file growth exceeds the allowed limit. Setup allows the value up to 256MB. You can set it to a higher size after the installation.

I tried lowering the file count to see if the limit was spread across the files but no joy.

256MB seems like such a small amount. Will it really impact installation time to 0 out a 1GB file (especially as I enabled IFI in a previous step in the install)? I think this will mean a lot of people will ignore this tab and stick to their post install scripts to configure TempDB.

This is still the Community Technology Preview of 2016 so it might still change (again).

Aaron Bertrand (t|b) wrote a post covering the really good TempDB changes coming in SQL Server 2016. The links at the bottom of that post are well worth a read through.

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

Leave a Reply

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

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile