Recommendations for more than one tempdb file

We have various installations at our shop and we tend to start low with four tempdb files and then add additional files if there is tempdb contention as pointed out by @Kevin3nfs comment, where he references an SQLSkills.com search.

Microsoft has a knowledge base article: Recommendations to reduce allocation contention in SQL Server tempdb database

[...]
As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
[...]

SQLSKills.com has a good article The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention which has some general information about tempdb contention and a bit further down a script which will retrieve information about tempdb contention from some DMVs.

[...]
One of the most common performance problems that exists in SQL Server instances across the world is known as tempdb contention. What does that mean?

Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Consider the scenario of hundreds of concurrent queries that all create, use, and then drop small temporary tables (that by their very nature are always stored in tempdb). Each time a temp table is created, a data page must be allocated, plus an allocation metadata page to keep track of the data pages allocated to the table. This requires making a note in an allocation page (called a PFS page – see here for in-depth info) that those two pages have been allocated in the database. When the temp table is dropped, those pages are deallocated, and they must be marked as such in that PFS page again. Only one thread at a time can be changing the allocation page, making it a hotspot and slowing down the overall workload. [...]

Below is a script we used for very large database server configurations. It creates a tempdb with 8 files of 11GB size with no autogrowth and a tempdb transaction log file of 11GB. This script can be adapted to your requirements.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev',FILENAME = N'G:\tempdb.mdf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'G:\tempdb4.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'G:\tempdb5.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'G:\tempdb6.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'G:\tempdb7.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev8', FILENAME = N'G:\tempdb8.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'G:\templog.ldf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )

As you can see these files were all located on the same drive.

If you still notice contention in the tempdb database, then splitting the tempdb database files over various disks might be a possible solution, but than depends on your configuration (SAN, SSD, ...) and would have to be verified with testing.


Yes even on the same disk you should experience performance improvements with multiple tempdb. A rule of thumb is number of cores.

Recommendations to reduce allocation contention in SQL Server tempdb database