Testing of Muiltiple tempdb files and its performance

Ok. We are told by Microsoft that SQL server requires the same number of tempdb data files as the number of cores. So, is this really true? In order to test this, I created a simple test case using the Microsoft OSTRESS tool. The idea is simple, we create a script that hammers the tempdb and we run them from multiple threads. First we create a log table in our monitoring database: use dbmonitor go create table tempdbStressTest (NoofFiles int, duration float) then we compose a tempdb hammering script like so: use tempdb go set nocount on create table #kkk (col1 char (7999)) declare @starttime datetime, @stoptime datetime, @i int, @noofTempdbfiles int select @noofTempdbfiles =count (*)-1 from sysfiles select @starttime =getdate() select @i=0 while @i<100000 begin insert #kkk values (replicate ('a',7999)) select @i=@i+1 end select @stoptime =getdate() insert into dbmonitor.dbo.tempdbStressTest values (@noofTempdbfiles,cast (@stoptime-@starttime as float)) drop table #kkk after all the setup, we run OSTRESS against our test server which has 4 cores Ostress -SServername -E -iD:\DBA\tempdbstresstest.sql -n20 Notice we created 20 concurrent connections. We then gradually add the number of tempdb data files. ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2 ', FILENAME = N'D:\MSSQL\Data\tempdb2.ndf' , SIZE = 3072000KB , FILEGROWTH = 10%) GO USE [master] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3 ', FILENAME = N'D:\MSSQL\Data\tempdb3.ndf' , SIZE = 3072000KB , FILEGROWTH = 10%) GO USE [master] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1 ', FILENAME = N'D:\MSSQL\Data\tempdb1.ndf' , SIZE = 3072000KB , FILEGROWTH = 10%) GO USE [master] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev0', FILENAME = N'D:\MSSQL\Data\tempdb.ndf' , SIZE = 3072000KB , FILEGROWTH = 10%) GO we restart the SQL server each time we add a file. Note that we add the same size files for all data files. we then run the reporting query as follows: select avg(duration)*1000, NoofFiles from dbmonitor.dbo.tempdbStressTest group by NoofFiles And the result is astounding.