I have seen in a couple of places where it is recommended to continue to use multiple files for TempDB in SQL Server 2005. Recently we had a MS consultant on site that was able to contact the SQL Product Development group about that question in particular. Here is the responce that I got in return.
______________________________________
The information in KB 328551 is exactly why I don’t think any recommendation based on number of processors is of much use.
This recommendation has been cut and pasted for several versions of SQL Server now, and while it may have worked great for SQL 7 and SQL 2000 when most machines had just 1-2 processors or a really large server may have had 4 processors. Now with SQL Server 2005, many more servers are starting with 2 sockets with dual core processors (4 cores) and then enabling Hyper threading to give the appearance of 8 processors to the OS.
Configuring a server with either 4 or 8 tempdb files may by itself cause performance problems (due to the overhead of SQL having to manage too many files), so the recommendation should be changed to start with just 1, and monitor for tempdb contention. ONLY if you see contention then add more files until the contention is resolved. However it’s worth noting that just adding more files won’t always avoid tempdb performance issues, as it’s entirely possible that the issue is with a slow I/O subsystem. Adding more files ONLY helps resolve contention for the schema lock when creating new objects, which in tempdb can happen at a very high rate. However not all applications make use of tempdb. It’s possible that specific application may make no use of tempdb. In that case creating multiple tempdb files is a big overhead and a waste of time.
On the other hand, the application might make extensive use of tempdb, but the bottleneck is not on the schema lock, but rather on the I/O throughput. Unless the additional files are created on different disks, then just adding more tempdb files won’t help this either.
Adding tempdb files only helps if the issue is around schema lock contention. This is another point worth bearing in mind, and one that’s not addressed at all in the number of files = number of processors recommendation.
All these points help illustrate why the recommendation for tempdb on a completely new system should be
1. Start with a single tempdb file
2. Monitor for tempdb related performance issues
3. If the bottleneck is the schema lock then consider adding more tempdb files until the contention is resolved.
4. If the bottleneck is I/O throughput then consider adding disks to the array backing the tempdb files, or create additional tempdb files on additional disks.
Since you already have an existing system, you should monitor the tempdb usage and bottlenecks (you have the perfmon logs already), and use this information to help determine the starting number of tempdb files, and the number of disks backing the tempdb files.
As always this is just a starting point as new server hardware is invariably faster, has more memory, and faster disks, which will most likely change where the next bottleneck is.
________________________________________
Anyway, thought I would pass it along.
-Zach