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.