Problem
Most DBAs are aware that (prior to SQL 2016) one of the most common, post installation changes that needs to be made in SQL Server is the addition of extra TempDB datafiles. What seems to be less well known is that ALL these files all need to be of equal size and need to expand in equal increments.
Example
iovfs.*,
mf.name,
mf.type_desc
INTO #dm_io_virtual_file_stats_start
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats(2,NULL) iovfs
ON mf.database_id = iovfs.database_id and mf.file_id = iovfs.file_id
The following query is executed 5 times to generated a workload in TempDB
fn = LEFT(o.name, 64 ),
ln = LEFT(c.name, 64 ),em = LEFT(o.name, LEN)c.name )%5+1 ) + '.'
+ LEFT(c.name, LEN( o.name )%5+2 ) + '@'
+ RIGHT(c.name, LEN( o.name + c.name )%12 + 1 )
+ LEFT(RTRIM( CHECKSUM( NEWID())),3 ) + '.com',
a = CASE WHENc.name LIKE'%y%' THEN0 ELSE 1 END
INTO #temptest
FROM sys.all_objects AS o
CROSS JOIN sys.all_columns AS c
And finally the following SQL is executed to compute the results.
DB_NAME(t1.[database_id]) [DatabaseName],t1.[name] [LogicalName],
(SUM(t2.[num_of_bytes_written])-SUM(t1.[num_of_bytes_written]))/1048576
AS [Written_MB]
,SUM(t2.[num_of_reads]+t2.[num_of_writes])-SUM(t1.[num_of_reads]+t1.[num_of_writes])
AS [IO_Count]
FROM#dm_io_virtual_file_stats_start AS t1
INNER JOIN
(SELECTiovfs.*,mf.[name],mf.[type_desc]
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats (2,NULL) iovfs
ON mf.[database_id]= iovfs.[database_id] AND mf.[file_id] = iovfs.[file_id]) t2
ONt1.[database_id]= t2.[database_id] AND t1.[file_id] = t2.[file_id]
GROUP BY DB_NAME(t1.[database_id]),t1.[name]
ORDER BYIO_Count DESC
Results
1 TempDB file
Using a single TempDB datafile...
the following values were observed.
2 Equal TempDB files
Using two equal TempDB files
the following values were observed. Note the almost equal Written_MB and IO_Count.
2 Unequal TempDB files
Using two unequal TempDB files
the following values were observed. Note the unequal Written_MB and IO_Count.
4 Equal sized TempDB files
Using four equally sized TempDB files
the following values were observed.
4 Unequal TempDB files
And finally, Using four unequally sized TempDB files
the following values were observed.
Solutions
There are a couple of ways, when creating multiple TempDB files, to make sure they stay evenly sized. My preference is to dedicate TempDB datafiles to its own drive. Divide the drive size by the number of datafiles, create each datafile that size and dont allow them to autoextend. This can cause problems if an errent query uses all the space in TempDB, in which case the query will need to rewriten to use less TempDB space or additional space will need to be added to the drive.
A trace flag, -T1117, can be also be used. This trace flags grows all datafiles in a filegroup at the same time. Beware that this is a global trace flag and will affect all database on an instance.
Conclusion
Its important, when creating multiple TempDB files, that each file is of equal size. Be aware of TempDBs usage patterns and how each datafile will grow if fully used.
