How Many tempdb Data Files Should My SQL Server Have?

We’re frequently asked how many tempdb files a SQL Server should have, especially from those who have downloaded our free 5 Common SQL Server Configuration Issues PDF. That’s because there’s a lot of well-intended but incorrect information posted on the internet about tempdb. 

What Is tempdb?

Let’s start with a very brief description of tempdb. When SQL Server needs some additional workspace to resolve a query, it uses a built-in system database called tempdb. A query may use tempdb for sorting operations, cursors, temporary tables, or even aggregation operations among other things. Since there is only one tempdb database for each SQL Server instance, it can be quite heavily used. 

By default, when you install SQL Server, one data file is created for the tempdb database. Having only one data file, however, can hinder SQL Server’s performance. The solitary file can become a bottleneck for queries that require tempdb. This is a pretty common issue, in fact, it made our Top 5 List. 

How Many tempdb Files Do You Need?

So, if the default value is likely not right for you, how many tempdb data files should you have? The answer is: it depends. According to Microsoft Support, the best approach is to create one tempdb data file per logical processor up to 8 data files.

If your system has more than 8 logical processors, start with 8 data files and monitor your server’s workload to determine if more data files would be beneficial. If you do find that an increase is warranted, add 4 data files at a time, but do not add more than the number of logical processors.

How Many tempdb Files Do You Have?

How many tempdb data files does your SQL Server have? A fairly straightforward query can answer the question. Open Management Studio and run the following query.

--tell me about my tempdb
SELECT
  f.name AS [file_name],
  CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [size_in_MB],
  CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(15, 2)) AS [space_available_in_MB],
  [file_id] AS [file_id],
  ISNULL(fg.name, 'LOG') AS [filegroup_name],
  f.physical_name AS [physical_name]
FROM sys.master_files AS f
LEFT OUTER JOIN sys.data_spaces AS fg
  ON f.data_space_id = fg.data_space_id
WHERE f.database_id = 2;

You’ll notice that the results from the query above include the folder location for each tempdb file. That’s because file placement can also have a dramatic impact on performance and even reliability. But that’s another story.

For more information about tempdb, check out Microsoft’s SQL Docs. 

Want to know more about other configuration options that could affect performance and reliability? Check out Is My SQL Server Configured Properly?

[EDIT Oct 5, 2021] – Also see How to Configure SQL Server tempdb? for other tempdb configuration settings that can affect performance.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation.

Schedule a call with us to get started.