The number of the TempDB data files should match the number of logical processors, up to eight files, on the machine where the SQL Server instance is installed. If the number of logical processors on that machine is greater than eight, set the number of data files to eight, with the ability to extend it by multiple of 4 in case you have a TempDB contention issue
Make sure that the TempDB files have equal size within the same filegroup to provide the highest possible degree of parallel operations efficiency, due to the Proportional Fill writing mechanism that is used to write the data inside the data files. For more information, check Understanding the SQL Server Proportional fill algorithm
Configure the TempDB database data and log files with a reasonable initial size and auto-growth amounts that fits the expected amount of data that will be written on these files, and the different types of workloads performed in your SQL Server instance, including user activities and index maintenance operations, in order to avoid the frequent small increment amounts that affect the overall instance performance
Locate the TempDB files on a fast I/O subsystem in order to avoid any I/O bottlenecks
Locate the TempDB on a separate disk drive, away from the user databases and other system databases. In this way, the heavy read and write operations performed on TempDB will not affect or be affected by the workload of the user databases, enhancing the overall SQL Server instance performance
Comments