Understand TempDB health with Spotlight on SQL Server Enterprise

The Tempdb is a system database that serves as temporary workspace for storing temporary tables that hold intermediate results during query processing or sorting to help maintain peak performance of SQLServer. The Tempdb is re-created each time the SQL Server service starts providing a clean copy of the database and reset to its last configured size. It is regularly used by user-generated requests and system-level processes. A healthy Tempdb is critical for efficient SQL Server performance.

Spotlight on SQL Server Enterprise keeps users informed of Tempdb configuration settings, space usage, and contention. Upon selecting a monitored SQL Server instance, click on the ‘Tempdb’ icon to access the tempdb drilldown page.

In the Tempdb drilldown page, you’ll see three tabs.

In the Configuration tab, Spotlight highlights the following best practice and provides suggestions (per the Help file):

  • 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.

The page concludes with sharing those configurations you have set appropriately. It’s always great to end on a positive note!

In the Usage tab, the usage page displays those transactions consuming the most space.

Users can leverage the Space Used chart. Populated from the sys.dm_db_file_space_usage, the data represents point-in-time usage. The chart plots User objects, Internal objects, Version Store, and Free/Unused Space.

There are Version Store Size/Change charts. These charts can be alternately viewed with the chart tile dropdown menu. Populated via SQLTempDBPerfCounters, users can look at these to determine the growth rate versus the cleanup rate.

The bottom portion of the drilldown highlights the Top 25 sessions in a grid.

(Available for SQLServer 2012 Service Pack 2 and above)

In the Contention tab, Spotlight users can see if the SQLServer is suffering from Tempdb contention. Not to be confused with I/O, Tempdb contention is the bottleneck for threads attempting to access in-memory allocation pages.

The Wait Time chart plots the wait over time for the Page Free Space (PFS), Global Allocation Map (GAM), and Shared Global Allocation Map (SGAM) resource types.

Understanding the sessions involved can help determine the application or user causing the contention.

Spotlight provides the following Alarms out of the box:

Spotlight users will be visually alerted on screen when a threshold is crossed. Optionally, users can configure various Actions to take place as a result like email notification, script execution, or an application launch.

Let Spotlight on SQLServer Enterprise keep you informed about the performance of your Tempdb, essential to overall SQLServer health.

About the Author
David Orlandi
David Orlandi brings over 17 years of experience in software consulting to his position as Sales Engineer. Dave is responsible for supporting the sales division by communicating and demonstrating the value...