The TempDB View in SentryOne Portal
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.
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.
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.
Tempdb data can be organized into the following three categories:
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.
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.
To better understand how tempdb relates to SQL Server performance, check out the four examples of common performance problems below.
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.
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.
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.
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.
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:
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.
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:
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.
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.
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.
The TempDB View in SentryOne Portal