Divide tempdb Into Multiple Files

By default, when tempdb is created, the MDF and LDF are created as single physical files.

While the LDF should always remain as a single physical file, often dividing the tempdb MDF into multiple physical files can help performance.

Multiple files can reduce contention on various global allocation structures by spreading activity over multiple physical files. This is particularly useful for those tempdb database which spend a large percentage of time allocating and de-allocating tables. If you don’t have contention issues, then you probably don’t  need multiple files.


Keep in mind that using more physical disk files for tempdb can increase switching costs and file management overhead, which could potentially hurt performance.


Thus, a balance must be maintained. How is this done?


Microsoft recommends (very generally) that tempdb should have one physical file per CPU core.
This often makes sense with 8 or fewer cores.


But with more than 8 cores, you may be reaching the point where the overhead of maintaining multiple tempdb physical files overcomes the benefits.


The design of your disk subsystem will affect this.


Only through formal testing will you able to determine the optimum number of physical files for your particular instance’s tempdb.


If you choose to use multiple tempdb files, keep the following in mind:

• Each physical file must be identical in size, and should be large enough to meet the tempdb needs of your instance.
• The autogrowth setting for each physical file must be identical, using the same fixed autogrowth amount.

The above is important because SQL Server uses a proportional fill strategy to fill the physical files, and if the files become different sizes, then tempdb becomes less efficient.

Example for Creating Multiple Files:

Determine where current tempdb file is at, and its size. Determine what file(s) have to be added.

Run ALTER DATABASE command, or use SSMS.

USE master;

GO

ALTER DATABASE [tempdb]

ADD FILE ( NAME = 'tempdev1', 

FILENAME = 'f:\tempdb1.ndf' , 

SIZE = 8192KB , FILEGROWTH = 10%)

Note: Restart SQL Server to get physical files in sync.