Advertisements

Archive

Archive for July 15, 2015

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

July 15, 2015 2 comments

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