Introduction
The tempdb system database is a workspace used to hold temporary user objects, results created through queries and sorts, and other objects created by the SQL Server Database Engine. Because of the significant amount of data stored in tempdb, users can run out of disk space, which causes the entire server and all of its databases to become unresponsive. Resolve some of these issues by providing a monitoring solution that features a series of charts, views, and alerts specifically for tempdb.
The tempdb system database, along with the master, model, and msdb databases, is provided by default with SQL Server and is a shared resource available to all users connected to a single SQL Server instance. Each time you start SQL Server, it recreates tempdb based on the model database. Tempdb can fill up quickly when you are low on disk space or have a low maximum size for database growth. Specific workloads may cause excessive space usage or create contention in tempdb, which can affect performance on the entire server.
Tempdb Version Store
The tempdb version store collects the data necessary to support row versioning. Each time a data value changes, a new version of the row is created and stored for as long as the oldest active transaction needs to access it. Once the row version is no longer needed it is removed from tempdb by a cleanup job which runs once per minute. As a result, long-running transactions prevent cleanup of older entries into the tempdb version store, causing growth which can affect performance and cause tempdb to run out of space.
Tempdb Contention
Tempdb resource contention or waits is usually the result of heavy use on too few tempdb files and occurs when the system attempts to allocate pages. The tempdb contention chart displays latch wait time (in milliseconds) for the allocation pages of tempdb. The three tracked allocation page types are page free space, global allocation map, and shared global allocation map.
Refer also to the Geek Sync webcast Guide to Understanding and Monitoring Tempdb, the webcast SQL Server Performance Tuning – TempDB Best Practices, and the whitepaper Demystify Tempdb Performance & Management.
Monitor TempDB
Monitor tempdb databases with various charts and alerts to avoid costly performance issues caused by a full tempdb with IDERA SQL Diagnostic Manager.
Available Alerts
Tempdb includes the seven specific alerts Data File Autogrow, Log File Autogrow, Longest Running Version Store Transaction (Minutes), Session Tempdb Space Usage (MB), Tempdb Contention (ms), Version Store Generation Ratio, and Version Store Size (MB).
Refer also to the documentation Monitor your tempdb database.
Tempdb Summary Tab
The Tempdb Summary tab allows you to view the status of your tempdb database on the selected SQL Server instance. These charts include options for viewing your current capacity usage and recent trends of your files over time. These statistics appear in the Database Statistics report. This tab displays a list of sessions currently using tempdb along with their cumulative usage and tempdb space. These statistics help you get an overview of the health of your tempdb database and gives you a good indication whether you have enough space allocated or whether tempdb issues are causing a performance problem on your server.
Charts
The Tempdb Summary tab includes charts that display capacity usage and recent trend information for your tempdb database.
- Tempdb Space Used by File: You can choose to view how each file is using space on your tempdb database, displayed in megabytes.
- Tempdb Space Used Over Time: You can choose to view how your database is used over time based on object type by comparing charts that offer metrics for the Space Used and Space Allocated.
- Version Store Cleanup Rate: The tempdb database version store collects data rows necessary to support snapshot isolation. This chart helps you see the current cleanup rate to avoid filling up tempdb.
- Tempdb Contention: The Tempdb Contention chart displays latch wait time (in milliseconds) for the allocation pages of tempdb. The three tracked allocation page types are page free space, global allocation map, and shared global allocation map.
Refer also to the documentation Get the tempdb status summary.
Tempdb Panel
The Tempdb panel tracks the status of the tempdb database on your monitored SQL Server instance. Unexpected spikes in the use of tempdb usage and space may warn you to allocate additional space to prevent tempdb issues from causing a performance issue on your server. Problems with tempdb can cause an entire SQL Server instance to become inoperable.
Tempdb Space Used Chart
The Tempdb Space Used chart provides different views of how your database is used over time based on the object type. Tempdb can fill up quickly when you are low on disk space and or have a small maximum size for database growth. In environments where read committed snapshot isolation is in use, long-running transactions may also cause rapid growth in tempdb.
Tempdb Contention Chart
The Tempdb Contention chart provides the latch wait time for the allocation pages of your tempdb. These latch waits are associated with performance degradation for the related queries. Latch contention is usually an indication that you should create additional tempdb data files or if you already have multiple files, make adjustments so that they are equally sized.
Version Store Cleanup Rate Gauge
The Version Store Cleanup Rate gauge displays the current version store cleanup rate and the version generation rate of the data rows necessary to support snapshot isolation. Maintaining a cleanup rate above or equal to the generation rate helps you avoid filling up tempdb. Note that long-running snapshot isolation transactions prevent cleanup of older entries in the tempdb version store, causing growth which can affect performance and cause tempdb to run out of space. When interpreting this gauge, note that version store cleanup occurs only once per minute while generation may be ongoing.
Available Alerts
The three specific alerts are Tempdb Contention Alert, Version Store Generation Ratio Alert, and Version Store Size Alert.
Refer also to the documentation Tempdb panel.
Tempdb Statistics Report
The Tempdb Statistics report lets you view space utilization and data throughput statistics over time for your tempdb database. You can focus on a single chart type, such as data file growth, and track its value increase over a specified period of time. Run this report to view different space utilization and data throughput statistics currently occurring on your monitored SQL Server instances. This report allows you to obtain statistics through different metrics or chart types. The nine possible metrics are Data File Size MB, Data File Growth, Reads Per Second, Writes Per Second, Transactions Per Second, User Objects MB, Internal Objects MB, Version Store MB, and Mixed Extents MB.
Refer also to the documentation Tempdb Statistics.
Refer also to the video "SQL Diagnostic Manager for Tempdb":
| www.youtube.com/watch |
Download a fully functioning 14-day trial of SQL Diagnostic Manager, request a one-on-one demonstration, or request a price quotation.