Reducing allocation contention in SQL Server tempdb database

In SQL Server, tempdb database can become the bottleneck when there are allocation contention problems. To resolve the contention, Microsoft suggests one file per each CPU core. It seems this is a generally accepted rule. But, Paul Randal suggests it may not always work. If the database spills out large volume of data, multiple tempdb files may reduce the performance due to round-robin allocation, or random IO patterns.

The following is a quote from Microsoft about tempdb files. It follows by related hyperlinks of the topic.

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.

Recommendations to reduce allocation contention in SQL Server tempdb database

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

Misconceptions around TF 1118

Working with tempdb in SQL Server 2005

SQL Server: Temporary Objects*3#sqlserver-temporary-objects-m4-considerations