TempDB in SQL Server

Temporary Data with Long-Term Problems


Updated: September 15, 2020

Table of Contents:






The TempDB View in SentryOne Portal

What Is TempDB?

Tempdb is the name of a system database in Microsoft SQL Server. Database developers and the database engine use tempdb as a store for transient data. Tempdb stores data used in various active processing routines. The temporary data does not need persistence, and tempdb provides a functional "scratchpad" for the entire SQL Server instance.


How Does TempDB Work?

Let's discuss the lifecycle of tempdb and the data it holds. We will also cover the types of data in tempdb, who can access it, and when they can access it.


Where Does TempDB Come From?

Because tempdb is such a special database, it can be helpful to understand where it comes from and what happens to it over time. It is temporary, after all, and it is logical to wonder about its lifecycle.

Tempdb is abandoned and recreated when SQL Server starts. The system database "model" is used as a template when SQL Server creates databases. Tempdb schema is derived from the model database as well.


What Goes into TempDB?

Tempdb data can be organized into the following three categories:

  1. User data and objects—What your devs, apps, and users are doing
  2. Internal data and objects—What SQL Server is doing
  3. Version Store—A notable subset of internal data


A further breakdown of the types of data stored in tempdb can be found here. However, if you are less familiar with tempdb, we recommend finishing this article before reading that blog post.

The same structures and data that make up other databases also make up tempdb. There are, however, important distinctions to remain mindful of with tempdb.


TempDB Distinctions

  • The lifetime of data is limited: User data in tempdb can be scoped to a connection or globally. This affects both who can access and manipulate the data and objects and how long the data and objects are available in tempdb. A temporary table, defined using a single '#' sign, will be scoped to the user who created it in the current session. When the user disconnects, the temp table will be dropped. A global temp table defined with '##' before the table name will be scoped to all users and all active sessions. The global temp table will be dropped when the session that created it ends and any in-flight transactions that are using it have completed.

  • Tempdb can have only one file group, but it's also the one database that should have more than one data file in most cases. More on that in the sections about performance below.

  • Tempdb is in simple recovery mode. Don't try to back up tempdb. There isn't a practical reason to do so and SQL Server won't let you—it will throw error number 3147 if attempted.


How Does TempDB Relate to SQL Server Performance?

SQL Server instances can have up to 32,767 distinct databases. In practical use, SentryOne field engineers frequently encounter instances with up to 5,000 databases. Despite the potential for many databases with thousands processes running on each of them at any given time, SQL Server only has one tempdb. The same tempdb becomes the "scratchpad" for every database and process on the instance. The relevant performance risk is that tempdb, when improperly managed, can be a single point of failure for a SQL Server instance.




4 Common TempDB Performance Problems

To better understand how tempdb relates to SQL Server performance, check out the four examples of common performance problems below.


#1. Misconfiguration

Best practices for configuring tempdb can vary between major SQL Server versions. Many professionals will find multiple versions in their data centers or cloud providers. Proper configuration is key to tempdb performance. Getting this right up front can dramatically reduce future run-ins with tempdb.

Brent Ozar has published a succinct list, or cheat sheet, that can be followed to ensure a good starting point for most needs. Pay particular attention to best practices for the size and number of data files and for storage performance. These both relate to remaining scenarios below.


#2. Disk, Log, or Data Files Are Full

DBAs on all platforms dread this situation. Tempdb is not exempt from this problem. In fact, it catches more DBAs off guard with tempdb than user databases in my experience. It isn't hard to take tempdb for granted. It is usually doing what it is supposed to do, and you can forget how much is really going on in tempdb. This is exactly why we talk about Tempdb parasites. There is generally far more going on in tempdb than the known workload your team has created.

When a user database runs out of storage space, we panic and hustle to fix one application. When tempdb runs out of space, we have to hustle to fix the entire instance. Add the already established tendency to forget about tempdb, and you can imagine the interesting situations this can cause. Tempdb can grow wildly due to a transaction that didn't roll back or from index maintenance. Proper capacity planning is essential to tempdb performance.


#3. PFS Page Contention

We often immediately think of storage and IO when we talk about pages in SQL Server. However, this scenario is related more to management objects. There are data pages within the structure of a database used to track information about how storage and other objects are allocated. These pages can have multiple threads vying for time with the page. If there aren't enough data files providing throughput for these operations, then some of those operations will have to wait. When tempdb is allocating hundreds and thousands of tables all the time, those waiting processes translate to waiting users in the application.

Paul Randal has a blog post that goes into more detail about tempdb contention. Paul also provides a method of discovery to use if you think you might be experiencing this type of contention.


#4. Availability Groups and Version Store Bloat

We haven't discussed the version store much yet, but we wouldn't be able to cover tempdb performance without it making an appearance.

This scenario occurs when a transaction runs too long or gets stuck running and the version store cleanup is not able to run. The cleanup does exactly what its name implies—it cleans up old versions from the version store that are no longer needed. Cleanup frees space in tempdb for new version data and other objects. If the version store can't be cleaned up, the version data continues to accumulate until you run out of storage. This is another area in which ongoing monitoring is critical.



Fundamentals of TempDB Monitoring

Effective performance and activity monitoring for tempdb will include several of the same metrics that are important to all SQL Server databases. With tempdb, you'll want to go further by gaining additional insight into the areas covered in the sections below.



Ensuring configuration best practices are followed is the first step toward optimizing tempdb performance. Fortunately, many resources exist from subject matter experts. Below are two online resources to help with tempdb configuration:

  • An older post from Aaron Bertrand that heralds the SQL Server 2016 installation default improvements for tempdb. In the post, Aaron discusses the options that were added and why each option is important.
  • This KB article from Microsoft provides guidance on tempdb sizing, placement, and growth options.

The top Internet search topics for tempdb all have to do with shrinking the tempdb database. This alone is an indication that many of us are still getting our configuration and/or capacity planning wrong. Achieving peak performance in tempdb starts with making sure it is optimally configured.


Version Store

The version store is managed by SQL Server system processes. It should be monitored separately from other internal and user data, because the version store can become large and volatile. As discussed previously, the version store can also be linked to well-known performance concerns. For these reasons, the version store is a prime candidate for ongoing monitoring.



Tempdb objects can be thought of as belonging to one of two groups:

  1. Internal objects—This is data created and used by the SQL Server database engine in fulfilling query and processing requests.
  2. User objects—This is data created and manipulated in tempdb directly by database users or applications.

Version store and temporary tables warrant further attention. Certain performance problems present with a large version store or consistent heavy table creation over time. Monitoring version store and tables separately from other internal and user objects in tempdb will allow you to identify those performance scenarios quickly.


Version Store

Monitoring the size and change rate for the version store is quite important. Snapshot isolation provides a mechanism for improved throughput and concurrency. At the same time, snapshot data in tempdb can become a performance risk factor in enterprise data platforms.

For example, the images shown below are from two systems.

The first image is a standard workload with a fairly balanced distribution of internal and user objects.


The second image is from a system that has had tempdb overwhelmed by the version store.



One can conclude from a quick glance that we should investigate why the version store is so large on this system.


Temporary tables are what we think of first when we consider how tempdb is used. Tempdb will also have system tables and internal work tables. The TempDB Objects chart in SentryOne Portal highlights storage consumed by the various tables and objects stored in tempdb.



Knowing what is happening in tempdb is just as important as understanding object and storage distribution. SentryOne Portal uses a succinct visualization to provide insight on tempdb activity.


Three legend items indicate the chart is relaying Active Temp Tables, Non-snapshot Version Transactions, and Snapshot Transactions.

Active Temp Tables lets you know the creation rate of temp tables. If these values increase sharply, you might experience tempdb contention.

Non-snapshot Version Transactions and Snapshot Transactions help you understand how and why the version store is being manipulated. Increases in these values might indicate an impending performance problem related to having a bloated version store.


Monitoring TempDB with SQL Sentry

Tempdb is a database, and SentryOne is ideal for monitoring database performance. For that reason, SentryOne has always provided functions that help you monitor, diagnose, and optimize tempdb performance. With the new tempdb collection engine and TempDB view in SentryOne Portal, keeping tempdb in peak condition is one less thing to worry about.


TempDB View in SentryOne Portal_08112020

The TempDB View in SentryOne Portal

Ready to start a trial?


Download SQL Sentry Trial

Find out what our customers are saying on TrustRadius