SQL Server 2016: configure TempDB during installation

Proper configuration of the TempDB system database is crucial for optimal performance of a SQL Server instances during heavy loads. The recommended strategy is to create multiple data files, one per CPU core (logical processor), but not more than 8.  Those files should be equal in size and with the same autogrowth settings. In past, this had to be done manually after the initial installation of SQL Server so many instances went to production with default values. SQL Server 2016 allows the configuration of TempDB DURING the setup process and it also sets better default settings than ever before.

Here’s a screenshot from the current community preview version of SQL 2016:

TempDB

The first TempDB data file is still named tempdev.mdf for compatibility reasons while all other files are set to temp2, temp3, and so on. .

The default number of files is set according to the recommendations from Microsoft: if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors.

The default initial size is set too low, so everyone should adjust that parameter according to his needs. Same goes for the Autogrowth parameter. Notice that there is no option to set the autogrowth in percents which is a great thing as it prevents taking that bad option which, btw, used to be default in previous versions of SQL Server.

Finally, the directories for data and log files can be changed from default values.

Advanced settings

There are a couple of important points that are not visible from that setup screen:

If there are multiple TempDB database files then all files will grow at the same and all allocations will use uniform extents. In other words, the behavior of the trace flags 1117 and 1118 is enabled for TempDB by default and those trace flags are not required any more.

Instant file initialization can now be requested during setup on this screen:

 

instant

 

Conclusion

In my opinion, the possibility to configure TempDB during the installation of SQL Server is a great improvement for several reasons:

  • it will enforce best practices
  • it will prevent some bad practices like percentage autogrowth
  • it will enable the trace flags behavior that are often overlooked

I’m curious to see what improvements will be added in the release version of SQL 2016.

 

 

 

 

Advertisements

6 thoughts on “SQL Server 2016: configure TempDB during installation

  1. In our virtual environment, I find using more than 1 or 2 tempdb files leads to disk latencies in the seconds. Gone are the days when I can expect a dedicated path or dedicated disks for a partition. Maybe SQL 2016 won’t assume that each file can be pushed to the extreme – or pushed at all – with limited infrastructure?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s