Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Management Pack for SCOM User Guide

SQL Server | Tempdb Drilldown

Tempdb is a system database. It is a temporary workspace for storing temporary tables that hold intermediate results during sorting or query processing. Tempdb is re-created anew from stored configuration each time the SQL Server service starts.

Use this drilldown to determine at a glance how much space is being consumed by tempdb. Spotlight also warns in advance problems surrounding disk space usage on Tempdb. These problems include: a fixed size Tempdb about to be filled up, a non-fixed size Tempdb trying to auto grow on a volume that does not have enough space for an auto grow.

To see the Tempdb drilldown

  1. Select a SQL Server from Monitored connections.
  2. Click Monitor | Tempdb.

Show all

To investigate...

Look at the...

Tempdb configuration.

Configuration Health page

Spotlight verifies the following best practices:

  • Tempdb file sizes are pre allocated to be of equal size.
  • Tempdb is split into a number of files appropriate to the number of processors in the system configuration.
  • Tempdb files are isolated on a drive if I/O rates are significant.
  • Tempdb auto growth is turned off.

Tempdb space usage.

Usage page

Identify the transactions consuming the most space.

Is the SQL Server experiencing Tempdb contention? If so, which user or application is the cause?

Contention page

When the Tempdb database is heavily used the SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve Tempdb to be unresponsive for short periods of time.

Note that Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

 

TIP: For more information on Spotlight charts and grids see Spotlight charts and Spotlight grids

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating