SQL Server Design and Configuration Issues Part 1: Tempdb

This is the first part in a series of articles covering various design, configuration and performance issues we have encountered working with SQL Server over many years. The articles are aimed at anyone with some SQL Server know-how who is interested in getting it working as well as possible.

What is tempdb?

Tempdb is one of the four system databases. It is recreated each time SQL Server starts and can’t be backed up or restored.It is used to handle a number of system tasks as well as to store user-created temporary tables. Various query operations such as sorts, aggregations and joins use tempdb for storing intermediate results. It is also used extensively for row versions in snapshot isolation and for operations such as index rebuilds.

Take a look at your tempdb files

Given its importance for the performance of SQL Server, getting it configured correctly is vital. In versions prior to SQL Server 2016, by default only one data file and one log file for tempdb are created when SQL Server gets installed. You can easily check how many files you have and their size and growth settings by running the following query.

SELECT f.name AS FileName, convert(int, round(f.size/128.0,0)) AS CurrentSizeMB, convert(int, round(m.size/128.0,0)) AS InitialSizeMB,

CASE f.type

WHEN 0 THEN ‘Data’ WHEN 1 THEN ‘Log’

END AS FileType, CASE f.is_percent_growth

WHEN 1 THEN convert(varchar(12),f.growth)+’%’ WHEN 0 THEN convert(varchar(12),convert(int, round(f.growth/128.0,0))) + ‘MB’

END AS FileGrowth, CASE f.max_size

WHEN -1 THEN ‘Unlimited’ ELSE convert(varchar(12),convert(int, round(f.max_size/128.,0)))

END AS SizeLimitMB FROM tempdb.sys.database_files f

INNER JOIN sys.master_files m ON f.file_id = m.file_id AND m.database_id=2;

GO

The first thing to watch out for is differences between the current size and initial sizes of the files. Many tempdb databases will not have been specifically configured, meaning that the initial sizes of the files may be as low as 8MB for the data file and 1MB for the log file. The problem with this is that each time SQL Server is restarted, the tempdb files are recreated with these sizes. Then as the server is used they have to grow again, causing unnecessary load on the system.In order to avoid this, the tempdb data and log files should be sized manually so that they are recreated with sensible sizes whenever SQL Server starts.

Do you need more data files?

You will only ever have one log file, but you could see one or more data files. In many cases one data file will be fine to begin with, but as databases grow and usage increases this configuration can start to become a bottleneck.The reason that performance might start to suffer is due to the way that resources in tempdb are allocated. Each time a new object needs to be created in tempdb, SQL server has to write to system pages to allocate the space for the object. This can lead to contention on the system pages themselves when the SQL Server is particularly busy. In order to find out if this is happening, you need to take a look at your server’s wait stats. If you see PAGELATCH\_SP, PAGELATCH\_UP or PAGELATCH\_EX waits taking up a significant proportion of wait time, this may indicate a problem with tempdb contention.The way to deal with this is to add more data files for tempdb. The reason is that the creation of tempdb objects will be spread across multiple data files, thus multiple sets of system pages will be used resulting in reduced contention.

How many files do you need?

There is no one-size-fits-all answer to this, however the general rule of thumb is that the number of tempdb files should equal the number of logical CPUs (normally processor cores) up to a maximum of 8. If you have more than 8 logical CPUs, you should not add more files unless there is still obvious contention, and then only in multiples of 4 until the contention is reduced or until the number is the same as the number of cores.This is the approach Microsoft recommends in [KB2154845] and has now finally incorporated into the installation process of SQL Server 2016, which preselects the appropriate number of data files based on the server it is being installed on and dedicates a page in the installation process to tempdb configuration.

Considerations for adding files

When you add multiple data files for tempdb it is very important that they are sized correctly. SQL Server will choose to use tempdb files roughly according to the free space available inside them. Therefore, if some files are larger than others, SQL server will end up using those disproportionately, leaving you with the same contention problems you started with.In addition to making sure the data files are sized the same, their autogrowth settings should be identical in order to make sure that when they grow they do so evenly across all files.The log file needs to be sized separately and given its own appropriate growth setting.Finally, if you are going to add more files, it is probably a good idea to schedule some down time for the server. Although it’s not strictly necessary, it makes sense to restart the SQL Server instance and verify that all the tempdb files are initialized correctly.