Exploring your SQL Server's Performance History with Spotlight on SQL Server

Spotlight's Playback, Alarms by Time and Alarm Log views

DBAs can’t be there to investigate every issue that affects their SQL Servers as it happens.  They might be working on an issue in another system or at home getting some well-deserved rest.  So Spotlight on SQL Server, in addition to providing email alerts and rapid diagnostics for SQL Server problems in real-time (covered in this post), also lets you see a history of alarms.  Even when a problem has come and gone, you can see what happened, possibly enabling you to prevent it next time.

We’ll be focusing on the part of the navigation ribbon shown above.

First a little background. The data we will be looking at is stored in the Spotlight Playback Database.  You can see the setup of this database at Configure > Diagnostic Server > Playback database:

What we’re seeing here is the number of days that alarm history is saved - defaulting to 7 days.  (If you’d like to retain alarm history for longer, just make sure that you allocate to the playback database about 1Gb per monitored instance per week of history).  The rest of what’s on this screen is just the name and location of the database, and the credentials that Spotlight will use to write to and read from it.

 

This most basic way to view alarm history is with the Playback drop-down. It shows your most recent alarm at the top, and allows you to scroll down to look into the past.  You can click one of the alarm entries to investigate the conditions of your SQL Server at that time.

 

The blue bar that appears across the top of the dashboard is there to emphasize that you are now looking at the appearance of the dashboard as of sometime in the past.

Once you’re viewing a dashboard in historical mode, you can investigate the issues that occurred at that time just the same way you do in real-time:  just click on the components that are in an alarmed state:

In this example, we’ve clicked on a blocking alarm, and just as happens with alarms in real-time, we get a popup with information about the alarm type, and a Diagnose button that will take us to the details of the issue.

 

The Diagnose button has taken us to the blocking drilldown, which shows us which sessions were blocking and being blocked, along with the statements, applications, users and machines involved.

You can use the Rewind, Skip and Play buttons to move around the time period you’re looking at, giving you an understanding of how the blocking situation emerged, progressed and resolved.

Clicking on the Real-Time button in the navigation ribbon returns the dashboard to showing the current condition of your SQL Server.

 

Now we’ll look another way of viewing alarm history, called Alarms by time, which is found in the navigation ribbon just to the left of the Playback button.

 

Once you’ve expanded out the server, looking along the left of the timeline you can see the alarms that Spotlight has recorded.  Following along the row of an alarm, you can see not only when an alarm began, but how long it lasted (based on the length of its bar), and how severe it got (based on its color).  For example, above, the lower arrow is indicating the same Locks – Blocked Processes alarm that we viewed earlier via the Playback dropdown.

This view is especially useful in understanding I/O issues.  For example, above you can see a number of database files that have experienced I/O response alarms recently.  Seeing the alarms arranged in this way can help reveal that files may be experiencing contention because of their shared disk location, as could be the case above with the stacked pattern along the right edge.

 

Above, we’ll look at our last example for this post.  It’s the Alarm Log, and it’s simply a list of all the alarms saved in the Playback repository, but this time in a table that can be filtered and sorted.

We’ve explored Spotlight’s history of alarms, but Spotlight also maintains a history of statements running through SQL Server.  This post will give you a look at that.

About the Author
Trent Mera
My SQL Server experience started with 7 years as a development and administrative DBA in the real estate, finance and defense industries. For the past 10 years I've been a Solutions Consultant for...