Uneven TempDB

With more than a singe TempDB data file it is critical to ensure that all data files stay in the same proportion in order that proportional fill algorithms can operate.

To check for uneven tempdb run the following :

       
;with tab as (  -- identify the tempdb database files that are online and have uneven file sizes 
    select  d.database_id 
    from  sys.master_files mf
    inner join sys.databases d on d.database_id  = mf.database_id 
    where  d.name = 'tempdb'
    and   mf.type_desc = 'ROWS'
    and   d.state = 0    -- ONLINE
    group by d.database_id 
    having  count(mf.file_id) > 1 -- no point checking if only one file (could be a design decision, assume designed)=
    and   avg(mf.size) != min(mf.size)
   )
   select  mf.name
   ,   mf.physical_name
   ,   mf.size*8/1024
   from  tab t
   inner join sys.master_files mf on mf.database_id = t.database_id
   where  mf.type_desc = 'ROWS'
       
 

We need to check all of the datafiles (type_desc='ROWS') and a simple method of deciding if there is an issue is to compare the average file size logged in sys.master_files to the smallest file size - if they are not identical then at least on of the tempdb datafiles has gone out of step.

Fixing it can simply mean increasing all the smaller files to the same size as the largest or stopping the SQL service, deleting the tempdb files and restarting the SQL Service if a data file grew due to a one off event (tempdb is automatically created a start up).

Tempdb

Useful article on tempdb and the numbers of files required : tempdb article. I tend to start with just two files and extend if experiencing performance problems simply because I've had a number of cases where tempdb activity is slower spread across lots of small files than running everything against fewer, large files.