I Didn't Know It Could Do That!
A comment I frequently hear from customers while troubleshooting performance issues with SQL Sentry Performance Advisor is:
“I didn’t know it could do that!”
The reasons for that reaction seem to vary from user to user. In some cases they haven’t worked with the software enough to become familiar with its features; for other folks, prior experiences with other tools seemingly create a frame of reference with which SQL Sentry doesn’t quite conform – which is not surprising!
Quite frankly, most every feature that elicits this reaction is well documented in our User Guide, but we all understand most technical folks “don’t have time” to read manuals (uh huh). With that in mind this post is the first in a series intended to bridge the gap. So, let’s get started.
The first stop on our tour is…
Jump To… Soldier!
No, we haven’t embedded a drill sergeant inside SQL Sentry. Jump To is a context menu command that allows you to navigate from one element of the client to another while maintaining the time range of the jump off point. In other words, while investigating a performance issue from one Performance Advisor tab, say the Dashboard (in History Mode), I can look for corroborating information on another tab, such as Top SQL. Let’s look at an example:
That looks like an interesting spike! Where shall I Jump To for more info?
In the Windows Disk IO chart above, I spot a spike in disk write latency, and I’d like to see what additional information SQL Sentry has gathered that will help me determine the cause. To start my investigation I select a time range in the chart surrounding the spike, then right-click within that range to bring up the context menu. Hovering over Jump To in the context menu reveals a host of options to explore. For example, I can select Disk Activity to see which disk volumes and database files were experiencing heavy write loads during the same time period as my selection.
Alternately, I could Jump To Top SQL to see what queries ran during that time and examine those with the highest number of writes.
(Of course from here I can view the captured query plans for further diagnosis in Plan Explorer, which is integrated into Performance Advisor.)
OR if I am monitoring this server with SQL Sentry Event Manager, I can Jump To the Calendar View to get an overall picture of event data (SQL Agent jobs, Top SQL, blocks, deadlocks, etc.) that occurred during that same time frame.
But wait, there’s more! Jump To exists as a context menu option throughout Performance Advisor, not just on the Dashboard. For instance, from the Calendar View, right click on an event and Jump To appears again, this time with even more options:
One of my personal favorites is using Jump To from an instance of a SQL Agent job to access its history going back as far as 180 days!
Here’s the resulting partial history for SQL Server Agent job called Validate Measures, including details of a Job Step failure:
I’m going to show you one more use of Jump To that few new users even suspect exists, but is extremely powerful. I urge you to try this in your own environment.
- Back on the Top SQL tab, switch to Totals Mode by clicking on the Show Totals button on the toolbar
- Right-click on a normalized query row in the Completed Queries grid (I chose one with 3 executions during the current time period)
- Select Jump To… Dashboard
- The Dashboard appears with a set of Event Overlays (the purple markers at the bottom of each chart), showing the full duration of each Top SQL execution. Great visual correlation!
Here’s my result:
I have a theory that SQL Sentry’s pervasive and powerful Jump To function is overlooked by some users because the web has taught us to find greater detail by drilling down, rather than switching horizontally between tabs. In my experience, SQL Sentry’s approach makes it much easier to maintain context, switch between views, and correlate the large amount of data collected. Jump To can be utilized from every chart on the Dashboard, and almost every grid throughout Performance Advisor. Right-click on the grids in Deadlocks, Blocking SQL and other tabs to discover all the possibilities.
I hope you found this installment of I didn’t know it could do that! instructive, and that you’ll return for more expert tips in the future.