Requires Spotlight Cloud Professional

How to open the TempDB Usage drilldown for a SQL Server connection

  1. Direct your web browser to http://www.spotlightcloud.io. Sign in an click the Monitoring tab. See also Welcome to Spotlight Cloud Monitoring.
  2. Select a SQL Server connection. Select the TempDB Usage drilldown.

Open the TempDB Usage drilldown for a SQL Server connection

TempDB panel

TempDB Summary Panel

Usage

Shows in proportion the usage of TempDB by Internal Objects, User Objects and Free Space.

Auto Growth

Is Auto Growth on or off for this SQL Server instance?

Outstanding Alarms

Show the number of outstanding alarms of each severity related to the TempDB drilldown. Click to open the Alarms panel to drilldown on these alarms.

Usage / Contention panel

TempDB Usage Panel

Usage

The Space Usage chart represents “points in time” usage.

Legend Description
User objects System tables and indexes, user defined tables and indexes, global temporary tables, local temporary tables and indexes, table variables and tables returned in table-valued functions.
Internal objects Work tables (for cursors, spool operations, temporary large objects LOBs), work files (for hash join and hash aggregate operations), intermediate sort results (for rebuilding indexes, group by, order by and union)
Version store There are two version stores in Tempdb. The online index build version store is used for online index builds in all databases. The common version store is used for all other data modification operations in all databases.
Free space Unused space

Contention

Click the Contention tab to see the Wait Time Chart. The Wait Time chart plots wait contention over time for the resource types PFS (page free space), GAM (global allocation map) and SGAM (shared global allocation map).

Files panel

TempDB Files Panel

Files

Show statistics for TempDB files.

Sessions

Click the Sessions tab to show TempDB usage by sessions.

Tables & Indexes

Click the Tables & Indexes tab to show statistics for TempDB objects.

Version Store Analysis

Compare at a glance the rate of growth to the rate of cleanup.

Create Objects Rate

Shows the rate at which temp tables are created.

Performance Indicators

A display of key performance indicators such as:

  • Oldest Transaction Running Time
  • Total Version Transactions
  • Average Version Transaction Rate
  • Active temp tables
  • Temp Tables for Destruction

Analytics panel

Activity Highlights

Spotlight Cloud summarizes the current situation. Is the version store currently growing? What is the current size of the version store? Is there significant TempDB contention on the SQL Server instance?

Advisories

Click the Advisories tab on the Analytics panel. Spotlight Cloud provides advice based on the latest configuration.

Show Hide Click to show / hide the Analytics panel.