We've been running into the errors described in the Microsoft article "FIX: Concurrency enhancements for the tempdb database" (http://support.microsoft.com/?kbid=328551), which explains that when the tempdb database is heavily used, SQL Server might experience contention when it tries to allocate pages. The article offers a workable solution but isn't clear when it discusses processors. Does the solution refer to physical or logical processors?
I first wrote about this potential resource-allocation contention problem more than 18 months ago in "Is Your Tempdb Stressed Out?" (SQL Server Perspectives, October 23, 2003, InstantDoc ID 40615). I like to revisit the topic from time to time because it continues to be a common performance problem at many customer sites. In fact, I generally encourage all my customers to configure their tempdb files in the manner the Microsoft articles describes (i.e., that they have one tempdb file for each physical processor in their systems). There's no performance degradation in doing so, and it can head off problems before they occur even if your site isn't currently experiencing the problem.
Nevertheless, you raise a good point. The Microsoft article simply refers to "number of processors" and doesn't differentiate between the number of logical processors and the number of physical processors. As you probably know, the maximum degree of parallelism (MAXDOP) should be set to the number of physical processors rather than the number of logical processors. The default value for MAXDOP leaves the server in a state in which single queries can end up with a degree of parallelism based on the number of physical, rather than logical, processors, and that's not what we want.
When you're looking at the number of tempdb files, it's the number of logical processors that matters—strictly speaking, the number of logical processors that you can use based on current affinity mask settings. You set MAXDOP to the number of physical processors to reduce concurrency problems with parallel queries. However, the tempdb problem that the Microsoft article addresses can easily occur in a high-volume online transaction processing (OLTP) environment in which no parallel queries are running. To minimize the concurrency problem in this case (see the Microsoft article for details), set the number of files based on logical processors, which will directly affect the number of users who can attempt to simultaneously create objects in tempdb.
This problem might seem pretty obscure. But if you run a high-volume OLTP site and aren't aware of the problem the Microsoft article addresses, you should research the potential problem as soon as possible.
