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:
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.
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 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.
Informative post! Very useful for Tempdb monitoring.