Reducing the number of TempDB Files

There has been many articles written around and available to find and read about TempDB and the recommended practices on the interwebs. Given there are so many articles available giving you advice on how you should configure TempDB we still come across many little gems even now that make us stop and think about why. This is what led me to write this post. Recently while undertaking an engagement I came across an environment where the configuration of TempDB that really did make me stop, look, look again to make sure my eyes were not playing tricks on me.

So where should we start? By default when SQL Server is installed TempDB is created with a single data file and single log file. Now not all workloads are the same and not every configuration of TempDB is going to be the same. That said though there are very good reasons as to why some standard recommendations have been being made for TempDB since the day of dawn (or relatively speaking).

So what are the recommendations that we should be taking note of?

1. If you have less than 8 cores provisioned, then use a 1:1 ratio of cores to tempdb data files. If you have more than 8 cores provisioned use 8 tempdb data files and if you are experiencing contention add 4 files and test until no contention is being experienced. For more refer to KB 2154845
2. Each of the TempDB data files need to be exactly the same size.
3. Each of the TempDB data files should have the same growth size configured
4. Trace Flag 1118 should be configured
5. Trace Flag 1117 should be optionally configured

Trace Flag 1117 – Grow all files in a filegroup equally

The default behaviour in SQL Server is that a file is to grow by the size set. Either a % of the existing size or in specific grow sizes. Implementing this Trace Flag does impact all databases in the Instance, however if your user databases do not have multiple files allocated there is not going to be any issues. Ideally you will want to pre-size each of the TempDB datafiles to assist in reducing the waits while the TempDB files undertake a growth operation. The correct size for the TempDB data files is a big “It depends on what is happening in your environment”. Keeping track of the utilisation of your TempDB files is an ongoing process.

Trace Flag 1118 – Full Extents only

The default behaviour of SQL Server is to utilise Mixed Page allocations. By implementing this trace flag this changes the instance level behaviour from allocating individual pages in TempDB for the first 8 pages to allocating 8 continuous pages or an extent to assist in alleviating contention in TempDB. Paul Randal has a great post on the Misconceptions around TF 1118 if you wish to read more specifically around this TF.

Every time an object is created in TempDB there is some administration overhead for the database engine to keep track of what is being created and where they are located and how much space they are consuming. The recommendations around increasing the number of data files is reduce the single bottle neck on a single file being able to keep track of all of this in busy systems. However, if you have more than you require then you are going to add additional overhead to the environment for managing all of the objects being used in TempDB with the potential for some degradation in performance. That is why the recommendations are based on the number of Cores your system has and gives you a starting point and how to increase the number and test until the contention is gone.

Now that we understand the recommendations and why these recommendations are made, we may sometimes come across an environment that has been allocated more TempDB data files than is required or recommended. To resolve this we can use the following steps during an approved outage window.

-- Step 1 in removing unnecessary tempdb data files
-- Empty out the identified files

Use TempDB
Go

DBCC ShrinkFile (TempDev5,EmptyFile)
Go

Alter Database TempDB
Remove File TempDev5

Go

-- Repeat the Empty and Remove File for all identified files

-- With Less files and having shifted the pages out of the identified
-- TempDB files into the remaining files you will want to resize all
-- of the remaining files to the same size.

Alter Database TempDB
Modify File (
 Name = 'TempDev',
 Size = 1000 MB,
 MaxSize = 4000 MB,
 FileGrowth = 512 MB
);

Alter Database TempDB
Modify File(
 Name = 'TempDev2',
 Size = 1000 MB,
 MaxSize = 4000 MB,
 FileGrowth = 512 MB
);

Alter Database TempDB
Modify File(
 Name = 'TempDev3',
 Size = 1000 MB,
 MaxSize = 4000 MB,
 FileGrowth = 512 MB
);

Alter Database TempDB
Modify File(
 Name = 'TempDev4',
 Size = 1000 MB,
 MaxSize = 4000 MB,
 FileGrowth = 512 MB
);

Additional Reading

• Understanding Pages and Extents – https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
• Managing Extent Allocations and Free Space – https://technet.microsoft.com/en-us/library/ms175195(v=sql.105).aspx
• For more information around working with TempDB read Working with TempDB in SQL 2005. All still applies today. (https://technet.microsoft.com/en-us/library/cc966545.aspx)