Monitor TempDB

Applies to the following products and features: The on-premises SentryOne Portal feature for SQL Sentry. See the Getting Started with SentryOne Portal article for more details.

This feature is not available with Essentials licensing. If you'd like to use this feature, please visit our product licensing page to learn more.

Overview

The performance of the TempDB system database is critical to your overall SQL Server performance. Things like temporary tables, work tables, the version store, sort operations, index rebuilds (when using SORT_IN_TEMPDB) all rely on the TempDB database.

The TempDB view in SentryOne provides you with insight into what is using your TempDB database and how effectively it is being used. In conjunction with other TempDB monitoring in SentryOne, you can be sure that you have optimized both the TempDB configuration and your applications that are using it.

Additional Information:

TempDB Summary

The TempDB Summary is the default chart when opening the TempDB view. It provides an overview of file space usage across your TempDB data files. This chart helps you see how space is being used between uniform and mixed extents. The values shown are in MB.

SentryOne Monitor TempDB Summary ChartTempDB Summary chart

TempDB Summary Metrics

Version Store

The total space reserved for the version store. When using snapshot isolation, the old versions of the rows are stored here until they can be cleaned up by SQL Server. Trigger activity also uses the version store.

Note:  Long running open transactions can cause the version store to grow as it means newer versions can't be cleaned up until the oldest version is no longer needed.

SentryOne Monitor TempDB Summary Chart with Version Store UsageExample of TempDB Summary with high use of the Version Store compared to other metrics

Internal Objects

The total space allocated for internal objects (e.g. work tables, work files, etc.) across all TempDB data files. This space is within uniform extents and includes all allocated space, even if it is unused.

User Objects

The total space for user objects (e.g. table variables, temporary tables, etc.) in the TempDB database from uniform extents. This includes space that has been allocated, but is unused.

Mixed Extents

The total space that has been allocated to mixed extents across all TempDB data files. The pages in a mixed extent can be shared and owned by different objects in SQL Server, but they do not get allocated for the version store.

Free Space

The total amount of free space across all TempDB data files. This doesn't include unused space that is allocated in an extent.

See the following articles to learn more about the metrics on the TempDB Summary chart and the differences between uniform (dedicated) and mixed extents:

TempDB Objects

The TempDB Objects charts provide a breakdown of the various TempDB objects by type (User Tables, User Temp Tables, Global Temp Tables, Internal Tables, System Tables, User Tables, and Query Objects ). 

SentryOne Monitor TempDB Objects Chart Reserved SpaceTempDB Objects chart Reserved Space

TempDB Objects Chart Options
Note:  Use the Objects menu to view the TempDB Objects chart by Reserved Space, User Space, Row Count, or Object Count.
SentryOne Monitor TempDB Objects Chart Used Space
Used Space
SentryOne Monitor TempDB Objects Chart Row Count
Row Count
SentryOne Monitor TempDB Objects Chart Object Count
Object Count

Additional Information: See the Tables article on Microsoft Docs to learn more about these table objects.

TempDB Activity

This chart provides a high-level view of the type and level of activity occurring in tempdb.

SentryOne Monitor TempDB ActivityTempDB Activity chart

TempDB Activity Metrics

Active Temp Tables

The number of active temp tables (system or user-generated) that exist.

Non-snapshot Version Transactions

The number of active transactions that are using the TempDB version store, but are not part of Read Committed Snapshot Isolation (RCSI). The Non-snapshot Version Transactions metric paints an overall profile of the server workload. 

Note:  If you're not running RCSI or doing any snapshot level isolation on a system, this value is usually caused by triggers. Triggers use snapshot isolation, so this can be a top source of TempDB consumption if you have a lot of triggers.

Additional Information: See the How Does SQL Server 2019 Accelerated Database Recovery Affect TempDB and TempDB Parasites blog posts for examples of triggers using TempDB.

Snapshot Transactions

The number of active transactions that are using the TempDB version store as part of RCSI. This is an explicit representation of RCSI or snapshot isolation overhead.

SentryOne Monitor TempDB Activity Chart with SpikeTempDB Activity chart showing a spike in Active Temp Tables and Snapshot Transactions

Additional Information: See the SQL Server, Transactions Object article on Microsoft Docs to learn more about the TempDB Activity metrics.

TempDB Session Usage

Note: 

  • TempDB Session Usage must be enabled through Settings.
    • Go to Settings Pane SQL Server SQL Server Collect Session TempDB Usage. Set this option to True. This may be done at the global, site, or target level.
      • On older versions, it was located under Settings Pane SQL Server Miscellaneous Collect Session TempDB Usage.
  • This feature requires SQL Server 2012 or later on the watched target.

This chart shows details related to the number of sessions that were using space in TempDB, as well as how much of TempDB was in use, grouped by the Host, Application, and Login. Use this information to understand if there were a small number of sessions with each consuming a large amount of space, or many sessions, each consuming a small amount of space that caused a large amount of consumption when aggregated.

SentryOne Monitor TempDB Session UsageTempDB Session Usage table

Note:  The values in the TempDB Session Usage table represent the aggregated consumption over the time period in the top navigation bar, or highlighted time period from the TempDB Summary or TempDB Activity charts. Highlight a duration on either the charts and select Filter from the context menu to filter into a specific period of time.

SentryOne Monitor TempDB Session Usage for a highlighted period of time from TempDB ActivityTempDB Session Usage showing a highlighted time frame from the TempDB Activity chart

Note that selecting TempDB from this context menu will zoom the TempDB Summary and TempDB Activity charts into the highlighted time period.

TempDB Session Usage Metrics

ColumnDescription
HostThe server or workstation name associated with the session(s).
ApplicationThe name of the application associated with the session(s).
LoginThe login name associated with the session(s).
Total TempDBAggregate of all TempDB allocations (in MB) that occurred during the defined range.
Active TempDBAggregate of allocations (in MB) that were active during the defined range. This removes deallocated usage from the total to provide the active consumption for the associated session(s) by showing only the allocations since the last collection of metrics.
Max Granted MemThe maximum amount of memory granted (in MB) for the associated session(s) during the defined range.

Note:  Insufficient memory grants are a common cause of spills to TempDB. The insufficient memory grants are caused by poor estimates, which may be caused by inaccurate statistics, missing indexes, and similar scenarios. Consider using Plan Explorer to learn more about your queries, including estimated vs. actual plans and indexes and statistics.
Total TimeAggregate of all time spent on the associated session(s) during the defined range.
Total CPUAggregate of all scheduled CPU time (in milliseconds) for the associated session(s) during the defined range.
Total Reads (L)Aggregate of logical reads completed for the associated session(s) during the defined range.
Total Writes (P)Aggregate of physical writes completed for the associated session(s) during the defined range.

Note:  The writes may be coming from operations or snapshot isolation which explicitly use TempDB. You can correlate this value against other metrics (e.g. Snapshot Transactions, Non-snapshot Version Transactions, Version Store, and Query Objects) to get a better picture of your Total Writes (P). If the writes are coming from queries that are unintentionally spilling to TempDB, these are often query tuning opportunities.
Total TempDB UserAggregate of all space (in MB) reserved or allocated for user objects (e.g. table variables, temporary tables, etc.) by the associated session(s) and task(s) during the defined range.
Total TempDB InternalAggregate of all space (in MB) reserved or allocated for internal objects (e.g. work tables, work files, etc.) by the associated session(s) and task(s) during the defined range.
Active TempDB UserAggregate of all space (in MB) reserved or allocated for currently active user objects (e.g. table variables, temporary tables, etc.) by the associated session(s) and task(s) during the defined range.
Active TempDB InternalAggregate of all space (in MB) reserved or allocated for currently active internal objects (e.g. work tables, work files, etc.) by the associated session(s) and task(s) during the defined range.
Session CountThe number of sessions associated with this aggregation during the defined range.

See the following articles on Microsoft Docs for additional information about the TempDB Session Usage metrics:

TempDB Data Retention

The counter and session data collected for TempDB in SentryOne follows the standard Performance Analysis Dashboard Retention & Resolution data retention policies.

TempDB Collection Settings

  • TempDB Session Usage must be enabled through Settings. It is turned off by default.
  • TempDB Object Statistics is turned on by default. There is not currently a client setting for disabling this collection, but it can be turned off through a SQL statement if you experience issues with the query that starts with this text data: WITH tempdbObjects AS... being logged in SentryOne. See the tempdbObjects Query section in the Troubleshooting article for more information and instructions.

Additional TempDB Monitoring

You have access to additional TempDB monitoring through the following features in the SQL Sentry client.

Top SQL

Top SQL in the SQL Sentry client allows you to view Tempdb Internal (KB), Tempdb Internal (KB) Dealloc, Tempdb User (KB), and Tempdb User (KB) Dealloc metrics within the grid.

Example of TempDB metrics in Top SQL

Note:  You can highlight an area of activity and use the context menu to jump to Top SQL within SentryOne Portal for that time to get more information about queries and wait stats.

SentryOne Monitor TempDB Jump to Top SQL

Jump to Top SQL from TempDB

Disk Space Analysis

The Disk Space tab shows a high-level status of TempDB consumption, including the number of TempDB files.

Disk Activity Analysis

The Disk Activity tab shows a high-level status of TempDB activity.

SQL Server Metrics

Many SQL Server metrics on the dashboard can be correlated to TempDB as explained in the SQL Sentry Performance Metrics article.

Advisory Conditions

The default advisory conditions pack includes the following TempDB conditions to allow you to create alerts related to the size and number of TempDB files:

Reporting

The TempDB metrics collected are available via the Performance Counter History report in SentryOne Reporting.

Report example: