SQL Server Internals: Tempdb Part 1

In part one of this topic (“SQL Server Internals: Tempdb”), I told you what your tempdb database is used for, and what the main differences are between tempdb and user databases. In the post, we’ll look at the files created for tempdb. You can see the files that you have for tempdb (or for any database) by using that database and executing the procedure sp_helpfile. Depending on the version of SQL Server you are running, and how much you have already configured your files, your results could be very different than mine.

One thing you might notice is the logical name of the primary data file. The default name is ‘tempdev.’ This name goes all the way back to the earliest versions of SQL Server. Prior to SQL Server 7, space for a database was defined in terms of ‘devices,’ which were really just large files from which individual databases could use space. The CREATE DATABASE command did not refer to specific file locations on the disk, but rather specified space to be used from a previously created device. For tempdb, there was a system-created device name tempdev, and all of tempdb’s space came from that device. When upgrading to SQL Server 7 from an older version, the device names became the logical names of the files. And we’re still using that name, tempdev, even now in SQL Server 2017. However, that is only the default name, and it can be changed through the files page in the database properties sheet in SQL Server Management Studio, or by using the ALTER DATABASE tempdb MODIFY FILE command.

Even though tempdb uses a very old naming convention, Microsoft has not been ignoring it. SQL Server 2016 introduced several changes to tempdb file management that mainly have to do with the way SQL Server allocates space for a database.

Space allocation

Space in a database, used for storing tables and indexes, is managed in units called extents. An extent is made up of eight logically contiguous pages (or 64KB of space). To make space allocation more efficient, in most databases SQL Server doesn’t allocate entire extents to tables with small amounts of data. SQL Server has two types of extents.

• Uniform extents are owned by a single object, and all eight pages in the extent can be used only by the owning object
• Mixed extents are shared by up to eight objects

In versions earlier than SQL Server 2016, SQL Server allocates pages for a new table or index from mixed extents. When the table or index grows to eight pages, all future allocations use uniform extents.

When a table or index needs to grow, SQL Server needs to find available space to be allocated. If the table or index is still less than eight pages total, SQL Server must find a mixed extent with at least one page available. If the table or index is eight pages or larger, SQL Server must find a free uniform extent.

SQL Server uses two special types of pages to record which extents have been allocated and for which type of use (mixed or uniform) the extent is available. There are two special kinds of allocation pages that keep track of which extents have been used, either as uniform extents or as mixed extents. Global Allocation Map (GAM) pages record which extents have been allocated for any type of use. Shared Global Allocation Map (SGAM) pages record which extents are used as mixed extents. GAMs are always page 2 of any data file and SGAMs are page 3. There are additional GAM and SGAM pages every 4GB.

In a database with lots of tables being created, there will be lots of mixed extent allocation and thus lots of activity on the SGAM pages. There is likely to be lots of tables created in tempdb, of course.  The Page Free Space, or PFS page, is another type of allocation page that is heavily used when lots of tables are page created. It is page 1 every file, and there is an additional PFS page every 8,088 pages.

Looking at SQL Server blocked process information can tell you if having to find available pages in mixed extents is causing problems. The DMV sys.dm_os_waiting_tasks tool can show you the resource being waited for. If it’s page 1 or 3 in database 2 (tempdb), we usually call this ‘allocation contention.’ Prior to SQL Server 2016, there were two ways to potentially alleviate allocation contention problems. Since every file has its own GAM, SGAM, and PFS pages, creating more files will allow you to keep track of the needed pages using different SGAMs or PFS pages, spreading out the work. When allocations are spread out over multiple files, there is less chance of conflict over the same SGAM or PFS page. The recommendation is to create one tempdb file for each logical CPU used by SQL Server, unless you have more than eight. If you have more than eight CPUs, you can start with eight files, and add more only if you continue to see allocation contention.

Another solution is to do away with mixed extents entirely. Trace flag 1118 turns off mixed extent usage across the entire SQL Server instance.

New options as of SQL Server 2016

With SQL Server 2016, Microsoft made it a little easier on us. First of all, instead of one file for tempdb created by default, the install process assumes you will have multiple tempdb files, using the algorithm just described. There is a tab in the Database Engine Configuration screen, shown below, that allows you to configure the files. The screen below is the default for my new instance, on a machine with four logical processors. I am running SQL Server 2017. Screen captures and my results will be shown using Microsoft^® SQL Server Management Studio 17.5.

You can see that by default, it will create four data files, but I can change that number during installation. The default directory is shown as SQL Server’s own DATA directory, but that too can be changed. I could also add other directories to spread the files out over multiple disks, if desired. I can choose an initial size and a growth increment. It was always possible to configure your tempdb after SQL Server installation to have multiple files in multiple locations, with different sizes and growth increments, but having these options as part of the installation makes it more likely that new DBAs will pay attention. The default of having multiple files is different than in previous versions.

A completely new feature in SQL Server 2016 is a database option that allows us to do away with trace flag 2018. The option is called MIXED_PAGE_ALLOCATION, and when set to OFF for a database, SQL Server will never allocate mixed extents for your tables or indexes. The value is ON by default for every database except tempdb. Tempdb has this value set to OFF and it cannot be changed.  To see the value for each of your databases, you can run the following query.

SELECT name, is_mixed_page_allocation_on
FROM sys.databases;
GO

A value of 1 means the setting is ON, and 0 means OFF.

Sizing tempdb

An even more important question than the number of files you should have is what the total size of your tempdb should be, and unfortunately, there is no simple algorithm to use. To determine the optimum size of your tempdb, you must test your own applications with your data volumes, but knowing when and how tempdb is used can help you make preliminary estimates. Database maintenance activities such as DBCC CHECKDB or rebuilding indexes with the SORT_IN_TEMPDB option can greatly affect tempdb size as well. (Most sorting uses tempdb space, but by default, the sorting needed to create or rebuild indexes uses space from the user database containing the indexed objects. You can change that with the SORT_IN_TEMPDB option.)

Keep in mind that only one tempdb exists for each SQL Server instance, so one badly behaving application can affect all other users in all other applications. You need to test your workloads and monitor tempdb use until you can determine a good size that will rarely be exceeded. Although you can set tempdb to autogrow so transactions won’t fail due to unexpected growth, you don’t want to have to depend on autogrow. It may mean your transactions won’t fail, but their performance can be seriously hampered by the autogrow process.

There are numerous performance monitor counters you can use to keep an eye on tempdb sizing, but I’ll let you look those up for yourself. Instead, I’ll give you some TSQL queries you can run and incorporate into monitoring solutions as desired.

The first query below shows you the total free space in tempdb. The next three show you the space used by each of the three main components, as mentioned in Part 1: user tables, internal objects, and the version store. The final query shows you how much tempdb space is being used by all internal objects by all tasks running right now.

-- Determining the Amount of Free Space in tempdb

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the Amount of Space Used by User Objects

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the Amount of Space Used by Internal Objects

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the Amount Space Used by the Version Store

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Obtaining the space consumed by internal objects in all currently running tasks in each session

SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Although there is no magic formula to determine in advance how big your tempdb will need to be, monitoring your usage during while testing your applications running on each SQL Server instance can give you a good place to start.