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.