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.
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: See the SentryOne blog post Be Mindful of SQL Server TempDB Use (aka TempDB Parasites!) for examples of things that use TempDB resources.
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.
TempDB Summary Metrics
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.
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.
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.
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.
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:
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 ).
|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.|
Additional Information: See the Tables article on Microsoft Docs to learn more about these table objects.
This chart provides a high-level view of the type and level of activity occurring in tempdb.
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.
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.
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 Miscellaneous Collect Session TempDB Usage. Set this option to True. This may be done at the global, site, or target level.
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.
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 Activity chart. Highlight a duration on the chart and select Filter from the context menu to zoom into a specific period of time.
TempDB Session Usage Metrics
|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.|
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.
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.
Disk Space Analysis
Disk Activity Analysis
SQL Server Metrics
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:
- Tempdb Data Files
- Tempdb Large Version Store
- Tempdb Low Unallocated Page Count
- Tempdb Unequal File Size
- Tempdb/CPU Configuration Warning
- Tempdb/CPU Configuration Warning > 8 CPUs
The TempDB metrics collected are available via the Performance Counter History report in SentryOne Reporting.