Overview

Tempdb is a system database and is shared by all databases running on the instance. Tuning tempdb can improve overall server performance and can usually be done in such a way that it is transparent to code running on the instance, a win-win. The database engine uses tempdb for many things behind the scenes, including temporary storage for certain elements of the query execution process. At times queries will require more space in tempdb to store temporary data during execution than is available, and like any other database the database engine will attempt to grow the database file that is short on space.

In this article I will explain how SQL Server determines the initial size of tempdb files and show you how to check whether the file sizes have changed since tempdb was initialized. Once we have identified whether SQL Server has grown any of the tempdb files we'll discuss some options available to help avoid excessive tempdb autogrowth.

Disclaimer

Tempdb is a critical component of SQL Server and tuning it is a vast and complicated topic that can only be mastered through testing and observation, i.e. experience. Like most things in SQL Server the optimal settings for each tempdb will be specific to the instance where it resides. If by reading this article you're looking to identify one definitive setting to alter that will make your systems go from snail-slow to lightning-fast this is not the article for you. This article aims to shine a light on one aspect of configuring tempdb that you may or may not be aware of, as well as to assist you in identifying a few ways in which you might improve the performance of your database instance. Whether you choose to implement a change at all, and specific settings surrounding a change should be carefully considered and the details are completely up to you.

Background

Tempdb is initialized (read about why Books Online is not technically accurate when it says tempdb is re-created every time SQL Server is started here) each time SQL Server (referred to later as the service or the instance) is started. The model database, another of the five system databases, is used as a template of sorts (a model if you will) to initialize tempdb. We have two options available to influence the size of tempdb's primary data file when the database is initialized:

1. Alter the model database to set the Initial Size of the data file.
2. Alter the tempdb database to set the Initial Size of the data file.

Yes, you can alter tempdb's file sizes directly, and independently of the model database, and is part of what we'll explore in this article. The larger Initial Size setting of the two data files, modeldev data file of the model database or tempdev data file of the tempdb database, is what will determine the actual size of the tempdev data file when SQL Server initializes tempdb. The same is not true of the tempdb log file, i.e. if modellog has an initial size of 2 MB and templog has an Initial Size of 1 MB then templog will be 1 MB after tempdb has been initialized, meaning the only way to affect templog’s initial size is to alter tempdb directly.

Here are some scenarios to consider surrounding how the tempdb data file, tempdev, arrives at its Actual Size each time SQL Server starts and initializes tempdb:

To summarize, the largest of the two settings determines how large the tempdev file will be after SQL Server has finished creating tempdb. Note that adding additional data or log files to tempdb is possible (and the right number is a controversial topic) however this can only be achieved by altering the tempdb database directly because creating additional data or log files in model is not supported.

Analysis

Below are the default settings for model and tempdb as observed immediately after installing SQL Server 2012 Developer Edition supplemented with information gathered from Books Online. The Database Properties dialog in SSMS (i.e. the GUI, right-click database in Object Explorer and select Properties) rounds numbers and incorrectly interprets some values as Unlimited. In the case of log files the GUI shows a maximum size of Unlimited while Books Online tells us the maximum size of a log file is 2 TB and sys.master_files shows -1 in the max_size column. The story is similar for data files, while sys.master_files shows -1 in the max_size column and the GUI shows a maximum size of Unlimited, Books Online tells us the maximum size of a data file is 16 TB.

Notice the very small Initial Size setting for both data and log files. We learned in Table 1 that the Initial Size setting of 8 MB for tempdev will take precedence over the same setting of 4 MB for modeldev and after SQL Server has started tempdb.mdf will have an Actual Size of 8 MB on disk. templog.ldf will be 1 MB per the default settings of templog.

As you might imagine, it does not take much activity before SQL Server requires more than 8 MB of tempdb data file space or 1MB of log file space. As such, if you have never adjusted the default Initial Size settings for tempdev, modeldev or templog there is a good chance the database engine has had to grow a tempdb database file since the service was started. How can we check if tempdb has been autogrown since the last restart? There are multiple ways but here is a query I use to show some the relevant file statistics for the tempdb database files:

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;

Notice the last column in the resultset, change_in_MB_since_restart. If the number is not 0 it means SQL Server has grown that file since the service started and you should begin evaluating your tempdb Initial Size settings. It is important to mention that tempdb has a special property that other user and visible system databases do not have that allows the change_in_MB_since_restart column to be non-zero. If SQL Server has to grow a data or log file for a database other than tempdb the Initial Size setting for that file is automatically updated to reflect the new size after the autogrow which is not the case for tempdb. Remember what we said about how tempdb is initialized when the SQL Server instance is started:  the greater of the two Initial Size settings between model's data file and tempdb's data file determines the actual size of tempdev when it is initialized. If you consider that tempdb's files may be initialized much smaller in size than what is required to support normal operations on your instance then tempdb's files will most certainly need to be autogrown sometime after each service restart. When SQL Server has to autogrow data and log files it can be a disruptive operation to normal system activity, especially in the case of log files as they cannot be instantly initialized. Depending on the autogrow settings (e.g. if they were left at their default values, refer to Table 2) it can be extremely disruptive to queries that make heavy use of tempdb until tempdb's files have been autogrown to a size where all system activity can be accommodated.

Note that I am mentioning model in this discussion to illustrate how SQL Server determines the initial size of the tempdev data file. For tuning tempdb I would recommend altering tempdb directly, i.e. do not indirectly alter tempdev by altering the Initial Size setting for the modeldev file. Also note that when the templog file is initialized the size of modellog is not considered therefore to increase the Initial Size of templog you must alter tempdb directly. You can alter tempdb data or log file sizes using the ALTER DATABASE statement (see the References section at the end of this article). There are reasons why you might want to alter the Initial Size settings of the modeldev data file but they are out of scope for this discussion. To read more about the model database see the references section at the end of this article.

Action Items

After running Query 1 if  change_in_MB_since_restart is shown to be greater than 0 it does not necessarily mean you should immediately alter tempdb's Initial Size settings to meet or exceed the value in the actual_size_MB column. There are two main items to consider when reviewing the results of the query:

1. tempdb's autogrow settings (Query 1 column auto_grow_setting)
2. tempdb's Initial Size settings of both the tempdev data file and the templog log file. (Query 1 column initial_size_MB)

Regarding Item 1, if your autogrow settings are still set to their default values (see Table 2) I would recommend you change them to something more sensible right away. I prefer to grow both data and log files by MB, not by Percent, but that decision is up to you. When deciding on autogrow settings for log files consider the potential overhead SQL Server imposes when zeroing out files. Data files can be instantly initialized so the impact is minimal if enabled, but log files cannot be instantly initialized and large log growth activities can weigh a system down considerably. For systems requiring maximum performance from tempdb adding data files to tempdb and storing tempdb's data files on a separate I/O subsystem are good directions to consider. As stated earlier, tuning tempdb is a vast and complex topic so most discussions about complex and optimal configurations that apply to all systems is out of scope for this article.

Regarding Item 2, while I have very few reservations about immediately changing the tempdb autogrowth settings, I have some about changing the Initial Size settings. While you want tempdb to be initialized using an Initial Size that can accommodate all normal system activity, one measurement is not enough to determine how the settings should be altered. If your tempdb files have been autogrown I recommend reserving the altering of Initial Size settings up to or beyond the current size until you have observed tempdb files at that size after at least two future service restarts. If the disparity between the Initial Size setting and the current size is enormous a conservative move would be to alter the Initial Size settings to something in between the current setting and the actual size, continue to observe tempdb autogrowth after future restarts and adjust up or down as necessary.

Closing Notes

In addition to deciding which Initial Size and Autogrow settings to implement for your tempdb, creating multiple tempdb data files is also generally agreed upon as a good idea however the default number of data files to begin with for any given system is a controversial topic. Discussions about adding files to tempdb are out of scope for this article but see the References section of this article for additional reading, specifically the "DBA Myth a day" blog post by Paul Randal.

This article dealt mostly with determining if tempdb had grown since the last service restart and by how much. Determining why it might have grown is another huge topic but I will say a few words about it in case you need to explore that in your environment to justify a proposed change to tempdb or possibly to altogether alleviate the need to make a change. You can review the Default Trace for autogrow events which can sometimes establish a very clear timeline as to when the majority of autogrow events are occurring, which can be invaluable in determining the why. The Default Trace logs autogrow events for user databases as well as the four visible system databases. I have employed and recommended this approach many times to help determine why a transaction log has grown significantly at a time when supposedly no one was working on the system. The autogrow-trail almost has always lead back to a scheduled job (usually doing index maintenance) that caused the transaction log to grow in between scheduled log backups.

In closing I'll reiterate that the optimal tempdb configuration is specific to the instance on which tempdb resides. The best thing you can do to achieve optimal tempdb performance is to be proactive. I actively monitor tempdb usage and adjust the Initial Size and Autogrow settings, among other configurations like number of data files, whenever I think it can benefit performance, and then I continue to monitor. For more information about tuning tempdb see the References section for relevant articles from resources I trust.

Thank you to Gail Shaw and James Walsh for reviewing this content for readability and technical accuracy. I am grateful for their feedback. Any remaining errors or munged thoughts belong solely to me.

References

• Books Online: tempdb Database
• Books Online: model Database
• SQLSkills.com: Comprehensive tempdb blog post series by Paul Randal < a portal of sorts to some valuable information about tempdb
• 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
• SQLSkills.com: Misconceptions around instant file initialization by Paul Randal
• Books Online: Maximum Capacity Specifications for SQL Server
• Books Online: ALTER DATABASE

    -- Example: modify Initial Size settings of the default tempdb files
    USE [master];
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, SIZE = ###MB);
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, SIZE = ###MB);
    -- Example: modify Autogrow settings of the default tempdb files
    USE [master]
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILEGROWTH = ###MB);
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILEGROWTH = ###MB);