TempDB database is a system database used to hold temporary user objects and row versions etc. IN addition, TempDB holds internal objects that are created by the SQL Server Database Engine. TempDB plays an important role with respect to database operations. Since TempDB is used heavily, DBAs tend to allocate multiple data files to the tempdb to improve performance. Comprehensive article at SQLSkills (http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/) explains the optimal number of files for tempdb database.
In the previous installations of SQL Server, only one data file is added to the tempdb and after installing and you will need to add the files to the TempDB. For the newly added files to effect, you need to restart the SQL Server service.
In SQL Server 2016, there is an additional feature of an option to specify number of temp DB files as shown in the below screenshot.
The default value for the TempDB file is 8 or the number of cores whichever is lower. You cannot have a value higher than the number of cores for number of temdb files.
This option is available in the Database Engine Configuration options.
You don’t have an option of providing the different parameter values ( file size, growth properties) for each file in TempDB where as you only have the option of providing the number of files and each file size will be 8 MB with auto growth set to 10%. These are not configurable during the SQL Server installation.
]]>