Whenever tempdb is the topic of discussion, it seems that colorful terms such as “parasites” and “public toilet” abound. As SQL Server’s common workspace for everything from triggers to cursors to sort spills, tempdb has a well-earned association with unique and sometimes mysterious SQL Server performance problems. The new SentryOne tempdb features I’ll cover in this blog post are all about increasing visibility into what is happening inside tempdb, with the goal of finally clearing the fog around it.
Who’s Using TempDB?
SentryOne has previously delivered insight into tempdb performance from several different angles via our storage, Top SQL, advisory engine, and plan analysis features, but until now we haven’t had a consolidated view of tempdb that brings it all together. This new TempDB view is currently available in the installed SentryOne web UI, SentryOne Portal, and is designed to quickly give you the who, what, and why of tempdb usage. Armed with that information, you can target the applications and queries that are contributing most to tempdb contention and make adjustments to optimize performance.
I’ll walk through a common workflow—determining which applications and queries consumed tempdb during a specific time range. You can access the tempdb functionality in the navigation pane on the left by selecting a SQL Server under Targets, then selecting the TempDB view as shown here.
The first thing that jumps out is the TempDB Summary chart, which shows a spike in activity starting around 3:25PM that consumed all available free space in tempdb and then caused several auto-growths totaling over 8GB. You can see that the growth was a combination of internal objects, user objects, and version store. (See the SentryOne documentation for details on all of these elements and more.)
From here, I want to determine which applications were involved, so I highlight the entire range of the spike by dragging the cursor across that area of the chart and then select the Filter context menu. This will filter the data shown in the Session Usage grid at the bottom to only the activity that occurred during the selected range, indicated by the blue pill.
NOTE: Session usage collection is disabled by default for existing SentryOne environments. You can easily enable it at the global, group, or target level. Once enabled, a lightweight query will be sent to each target every 20 seconds by default, and session data will be collected and stored in a new table in the SentryOne database: TempDbSessionUsage.
You can see on the Session Usage grid that there were two applications responsible for the bulk of tempdb activity during this time period—a SQL Agent job with +-15GB of Total allocations in tempdb, followed by an SSMS query with +-14GB. Zeros in the Active column indicates that the pages for both have since been deallocated and therefore are no longer consuming space in tempdb. To the far right, you can see that there was one session for the job and two sessions for SQL Server Management Studio (SSMS).
From here, I want to see the actual queries involved for both apps, so I select the Top SQL context menu, which will display the Top SQL view prefiltered by the date range. The Waits chart shows that disk waits closely track with the tempdb spike, which is not unusual for certain types of tempdb activity. Enabling the Details mode toggle in the upper right and sorting the grid by physical writes clearly highlights the 3 batches involved with the high IO and tempdb activity.
Since I’m especially interested in which of these batches caused the high version store usage, I go back to the TempDB view and highlight only the part of the range that shows the spike in version store. When I do this, the SQL Agent job drops off, which makes it clear that the SSMS queries are responsible for the version store activity.
On the TempDB Activity chart, note that there is only a single non-snapshot transaction during this time and no snapshot transactions. This indicates that trigger activity is a likely culprit since triggers also make use of version store.
I won’t cover this today, but to confirm that triggers are involved, you can investigate the queries and plans in the batch using the web plan viewer in SentryOne Portal, the desktop SentryOne client, or the free standalone version of SentryOne Plan Explorer. If you’re able to run the batch to get an actual plan, any trigger activity with associated resource consumption metrics will be captured and displayed in the Statements grid, nested under the calling statement.
A Clear View of TempDB Usage
I hope this quick run through of the new TempDB view in SentryOne Portal was helpful for demonstrating how it can effectively augment other SentryOne capabilities to provide a clearer picture of tempdb usage and surface optimization opportunities. We have a laundry list of enhancements lined up for SentryOne TempDB v2, but there’s still time to submit ideas—we want your feedback!
