SQL Diagnostic Manager allows you to monitor your tempdb database with a number of charts and alerts to help you avoid costly performance issues caused by a full tempdb. 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 large 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. SQL Diagnostic Manager seeks to resolve some of these issues by providing a monitoring solution that features a series of tempdb-specific charts, views, and alerts.
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 re-creates 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. Certain workloads may cause excessive space usage or create contention in tempdb, which can affect performance on the entire server.
Tempdb monitoring is supported on SQL Server 2005 and above servers only.
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 (PFS)
- Global allocation map (GAM)
- Shared global allocation map (SGAM)
Latch contention of this sort is usually an indication that you should create more tempdb data files. In some situations, using Trace Flag 1118 may also alleviate tempdb contention.
Available alerts
Tempdb includes the following 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
- Version Store Size (MB)