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.
Consider implementing the best practice recommendations in the Technet article titled Working with tempdb in SQL Server 2005.
If the previous steps do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. Note that this trace flag affects every database on the instance of SQL Server. For information about how to determine whether the allocation contention is on SGAM pages, see Monitoring contention caused by DML operations subtopic under Working with tempdb in SQL Server 2005 page on Technet.
Starting with SQL Server 2016, some of these configuration changes are automatic and do not need user intervention. For more information, see:
- SQL Server 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
- SQL Server 2016 – It Just Runs Faster: Automatic TEMPDB Configuration
An improvement has been made that further reduces contention in SQL Server 2016 and SQL Server 2017. In addition to the round-robin allocation across all tempdb data files, the fix improves PFS page allocation by performing round-robin allocations across several PFS pages in the same data file. For more information, see the following Microsoft Knowledge Base article and MSDN blog:
- 328551 Concurrency enhancements for the tempdb database
- TempDB Monitoring and Troubleshooting: Allocation Bottleneck
- Managing TempDB in SQL Server: TempDB Configuration
- SQL Server TempDB – Number of Files – The Raw Truth
- SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage