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:
- 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