Tempdb is a system database in SQL Server that is shared by all databases running on the instance. It plays a crucial role in the performance of the server and can be tuned to improve overall performance. In this article, we will explore how SQL Server determines the initial size of tempdb files and discuss some options to avoid excessive tempdb autogrowth.

Background

Tempdb is initialized each time the SQL Server instance is started. It uses the model database as a template to initialize its primary data file. There are two options available to influence the size of tempdb’s primary data file:

1. Alter the model database to set the initial size of the data file.
2. Alter the tempdb database directly to set the initial size of the data file.

The larger initial size setting between the modeldev data file and the tempdev data file determines the actual size of the tempdev file when SQL Server initializes tempdb. However, this is not true for the tempdb log file.

Checking for Autogrowth

To check if tempdb has been autogrown since the last restart, you can use the following query:

USE tempdb;
WITH cte AS (
  SELECT DB_NAME(database_id) AS name,
         mf.name AS db_filename,
         mf.physical_name,
         CAST((mf.size / 128.0) AS DECIMAL(20, 2)) AS initial_size_MB,
         CAST((df.size / 128.0) AS DECIMAL(20, 2)) AS actual_size_MB,
         CASE mf.is_percent_growth
           WHEN 0 THEN STR(CAST((mf.growth / 128.0) AS DECIMAL(10, 2))) + ' MB'
           WHEN 1 THEN STR(mf.growth) + '%'
         END AS auto_grow_setting
  FROM sys.master_files mf
  JOIN sys.database_files df ON mf.name = df.name
  WHERE mf.database_id = DB_ID()
)
SELECT *,
       actual_size_MB - initial_size_MB AS change_in_MB_since_restart
FROM cte;

If the “change_in_MB_since_restart” column is not 0, it means that SQL Server has grown that file since the service started, and you should consider adjusting your tempdb initial size settings.

Action Items

After running the query, there are two main items to consider:

1. Tempdb’s autogrow settings: If the autogrow settings are still set to their default values, it is recommended to change them to something more sensible. Consider the potential overhead of zeroing out log files when deciding on autogrow settings.
2. Tempdb’s initial size settings: While it is important to have an initial size that can accommodate normal system activity, it is recommended to observe tempdb files at their current size after at least two future service restarts before making any changes to the initial size settings.

Remember that tuning tempdb is a complex topic, and the optimal configuration may vary for each instance. It is important to be proactive and monitor tempdb usage to make informed decisions about initial size and autogrow settings.

Closing Notes

In addition to adjusting initial size and autogrow settings, creating multiple tempdb data files is generally recommended for optimal performance. However, the default number of data files for any given system is a controversial topic.

Understanding why tempdb might have grown is another important aspect to consider. Reviewing autogrow events from the default trace can help establish a clear timeline and identify the cause of growth.

Overall, tuning tempdb requires testing, observation, and experience. It is a critical component of SQL Server, and any changes should be carefully considered and tailored to the specific instance.

References:

• Books Online: tempdb Database
• Books Online: model Database
• SQLSkills.com: Comprehensive tempdb blog post series by Paul Randal
• SQLSkills.com: A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core by Paul Randal
• SQLSkills.com: Does the tempdb Log file get Zero Initialized at Startup? by Jonathan Kehayias
• Reviewing AutoGrow events from the default trace by Aaron Bertrand
• Books Online: Maximum Capacity Specifications for SQL Server
• Books Online: ALTER DATABASE

Thank you to Gail Shaw and James Walsh for reviewing this content for readability and technical accuracy.