Can Spotlight Monitor Tempdb Database?

Absolutely! Spotlight performance monitoring can do it all.  It can monitor the health of your Tempdb, so you can have the peace of mind of knowing that Spotlight’s got you covered.  It not only ensures best practices by being on the lookout for configuration related issues.  It also alerts you of any possible Tempdb contention and excessive database usage.  Spotlight monitors tempdb and reports issues by through the use of eight alerts and a dedicated diagnostics drilldown display.

Undetected tempdb contention can cause execution bottlenecks for queries that require Tempdb involvement in turn leading to system unresponsiveness.

In the example below, Spotlight is reporting that my server is experiencing some PAGELATCH contention reported on an allocation page, also referred to as a PFS page, with a recommendation on how to alleviate the issue. Not only that, it shows exactly what user and application is causing this contention.

In the example below, Spotlight is reporting Tempdb usage spikes in user and internal objects against my server. Spotlight also has a dedicated data collection and graphical display version store objects. In addition to that, it shows me the top 25 most tasking SQL during that time.

Beyond contention and usage of Tempdb, Spotlight can provide you a recommendation of best practices for Tempdb configuration in the Health page display. These recommendations can be any one or a combination of the following steps:

  • 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 table below displays the eight Tempdb monitoring alerts of Spotlight that check configuration setup recommendations such as auto-growth, location of Tempdb, number of files, and their sizes; along with version store activity and contention wait times.

Name

Description

Threshold Level Values

Tempdb Auto Growth

Tempdb file is set to auto grow. It’s recommended that tempdb files will not be set to auto-grow to avoid the extra waits associated with growing the file, or allocate enough tempdb space to cover the typical peak, or configure auto-grow for special situations only.

 

Tempdb Contention

Tempdb is experiencing contention on PFS, GAM or SGAM pages. Total wait time for all sessions is measured and displayed in milliseconds.

0-200 for Normal,
200-500 for Low,
500-1k for Medium,
>1000 for High

Tempdb File Location

Tempdb files in the same location as other database files. Consider moving Tempdb to a disk that has no other databases on it.

 

Tempdb File Size

Tempdb files are different physical sizes.  It’s recommended that all files in Tempdb should have identical sizes.

 

Tempdb Growth Settings

Tempdb files settings are misconfigured. All files in Tempdb should have identical growth settings.

 

Tempdb Number of Files

Tempdb number of files are misconfigured. Consider increasing number of files.

 

Tempdb Version Store Generation Rate

The Tempdb version store creation rate is high compared to the cleanup rate. Rate measured in 10kb per second.

 >10 for Low

Tempdb Version Store Size:

The Tempdb version store is too large. Measured in Kilobytes.

>2048 for Low

So give Spotlight a try today. You can download a Spotlight monitoring free trial and find us on Twitter.

About the Author
Gita Sharifi
Gita Sharifi has been with Quest since 2006 working as a Senior Technical Support Engineer supporting database performance monitoring tools. Prior to joining Quest Gita has held positions in Oracle PL...
View all posts by Gita Sharifi