The number of tempdb data files in an SQL Server depends on several factors, including the number of logical processors on the server. As a general recommendation, it’s best to have one tempdb data file for each logical processor, up to a maximum of 8 data files.
However, the optimal number of tempdb data files can vary depending on the specific workload and resource utilization on the server. It’s important to monitor tempdb performance and adjust the number of tempdb data files as needed to ensure optimal performance. The actual optimal number of tempdb data files for a system with a specific number of logical processors can only be determined by monitoring performance and making adjustments based on the observed behavior.
If you determine that increasing the number of tempdb data files is necessary, increase in multiple of four (4) not exceeding the number of logical processors. Suppose a server is having 32 logical processors, the for first eight (8) logical processors eight (8) tempdb data files should be created. In addition for next 4 or 8 or 12 or 16 or 24 tempdb data files should be created. We should keep in mind that the number of data files must not excide the number of logical processors.
Kindly check a few blogs and Microsoft links for justification.
- https://theserogroup.com/sql-server/how-many-tempdb-data-files-should-my-sql-server-have/
- https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/performance/recommendations-reduce-allocation-contention
- https://learn.microsoft.com/en-us/answers/questions/74599/tempdb-file-number-for-many-logical-processor
- https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my
