SQL Server - Multiple TempDB Files

When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be unresponsive for short periods of time.

Per Microsoft “As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.”

It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine. In the past (up through SQL Server 2014) you had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server. Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.

It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine. In the past (up through SQL Server 2014) you had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server. Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.