TempDB in SQL Server

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.

 

Configuration

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.

 

Objects

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.

Tables

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.

Activity

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.

 

The TempDB View in SentryOne Portal