SQL Sentry Portal TempDB

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

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 SQL Sentry 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 SQL Sentry, you can be sure that you have optimized both the TempDB configuration and your applications that are using it.

Additional Information:

See the SentryOne blog post Be Mindful of SQL Server TempDB Use (aka TempDB Parasites!) for examples of things that use TempDB resources.
For more information about this TempDB feature, see the SentryOne Clears the Fog Around TempDB blog post.

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.

A TempDB Summary chart view showing the hover help with legend and MB used per legend label.

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 because newer versions can't be cleaned up until the oldest version is no longer needed.

An example of a TempDB summary chart displaying high use of the Version Store.

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.

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

sys.dm_db_file_space_usage article on Microsoft Docs
Pages and Extents Architecture Guide on Microsoft Docs
Inside the Storage Engine: Anatomy of an extent on SQLskills

TempDB Objects

Note: TempDB Object Stats must be enabled through Settings.

Go to Settings PaneSQL ServerSQL ServerCollect TempDB Object Stats. Set this option to True. This may be done at the global, site, or target level.

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 ).

The TempDB Objects chart chart displaying values for Reserved Space.

Note: Use the Objects > menu to view the TempDB Objects chart by Reserved Space, User Space, Row Count, or Object Count.

The TempDB Objects chart chart displaying values for Used Space.

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.

A TempDB Activity line chart showing values for Active Temp Tables, Non-snapshot Version Transactions, and Snapshot Transactions.

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.

A TempDB 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.

SQL Sentry Portal TempDB Session Usage

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.

TempDB Session Usage showing a highlighted time frame from the TempDB Activity chart and how that filters down to the TempDB Session Usage table below it.

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

Column	Description
Host	The server or workstation name associated with the session(s).
Application	The name of the application associated with the session(s).
Login	The login name associated with the session(s).
Total TempDB	Aggregate of all TempDB allocations (in MB) that occurred during the defined range.
Active TempDB	Aggregate 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 Mem	The 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 Time	Aggregate of all time spent on the associated session(s) during the defined range.
Total CPU	Aggregate 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 User	Aggregate 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 Internal	Aggregate 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 User	Aggregate 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 Internal	Aggregate 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 Count	The number of sessions associated with this aggregation during the defined range.

Additional Information: 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 SQL Sentry follows the standard Performance Analysis Dashboard Retention & Resolution data retention policies.

TempDB Collection Settings

TempDB Session Usage collection must be enabled through the Collect TempDB Session Usage option in the Settings pane.
- The TempDB Session Usage charts are not populated when this is turned off.
  - It is turned off by default.
TempDB Object Statistics collection must be enabled through the Collect TempDB Object Stats option in the Settings pane.
- The TempDB Objects charts are not populated when this is turned off.
  - It is turned off by default.
- In some environments, you may experience issues with a query that starts with this text data: WITH tempdbObjects AS... being logged in SQL Sentry when collecting these statistics. See the tempdbObjects Query section in the Troubleshooting article for more information.

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) Deallocmetrics within the grid.

An example of the Completed Queries tab in the SQL Sentry client Top SQL view with columns for Tempdb Internal (KB), Tempdb Internal (KB) Dealloc, Tempdb User (KB), and Tempdb User (KB) Dealloc metrics within the grid

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

A section of activity highlighted on the TempDB Activity chart with the context menu displayed and the Top SQL option highlighted.

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 SQL Sentry Reporting.

Report example:

Report Viewer: Performance Counter History screen in the SQL Sentry client showing the tempdb counters on a chart.

Search SolarWinds Support