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”.
Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.
So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:
Leave a Reply
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 
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.