Advertisements
Home > DBA Stuff, SQL Server 2016 > SQL DBA – Configure multiple TempDB Database Files while installing SQL Server 2016 (new feature)

SQL DBA – Configure multiple TempDB Database Files while installing SQL Server 2016 (new feature)


SQL Server 2016 allows you to scale up your database with Enhanced Database Caching, using support for multiple TempDB files per instance for multi-core environments. This reduces metadata- and allocation contention for TempDB workloads, improving performance and scalability.

Now you can configure the number of TempDB Database Files during the installation of a new Instance. While installation process in the Database Engine Configuration page you will see an extra option to set number of TempDB files.
 

Or, you can specify the number of files by using the new command line parameter: /SQLTEMPDBFILECOUNT

setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS 
 /FEATURES="SqlEngine" /INSTANCENAME="SQL15" .. 
 /SQLTEMPDBDIR="D:\tempdb" /SQLTEMPDBFILECOUNT="4"

 

While installing via UI the label besides the Input Control below mentions: “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.

SQL Server 2016 Install 06
 

Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.

SQL Server 2016 CTP 2.4 Setup 02

So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:

SQL Server 2016 Install 07
 

Check & Like my FB Page


Advertisements
  1. Naveed
    November 17, 2015 at 10:11 pm

    I would be grateful to you if you could kindly explain what exactly the enhancement is impacting workloads when compared to SQL 2014. Also would like to know your point of view on number of TEMPDB files. Example a system with 16 cores some create 16 TEMPDB files and some don’t. What are the best practices in this regards. And finally a huge thanks for knowledge sharing.

  1. March 14, 2017 at 8:48 pm

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

%d bloggers like this: