How many tempdb 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. 

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. 

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 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;

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

Hope this helps, 

Joe 

P.S. 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.