SQL Sentry Performance Advisor and Event Manager provide a rich and growing library of SSRS-based reports that provide easily digestible views of important data collected in the SQL Sentry database. These reports can be accessed from the SQL Sentry client, deployed to a SQL Server Reporting Services server, and new to Version 8, from cloud.neveropen.co.za.
Why Reports?
Like everything else in our products, reports have a well thought out role in our approach to monitoring and assessing SQL Server performance. Some uses for reports include:
- sharing information with people lacking direct access to SQL Sentry;
- providing a durable snapshot of a SQL Server’s state at a desired granularity and time, perhaps for comparison at a later date;
- addenda to executive level summaries; and,
- administrative support – the General reports in SQL Sentry fit in this category, providing detailed information on your deployed actions, notifications, and settings.
There is another reason, however, that I think many people miss. Reports provide a different or enhanced view of data compared to what you can see in the client interface. This is particularly important while troubleshooting performance issues.
For example, suppose I’m looking at my SQL Sentry dashboard in Sample mode and the Waits graph looks like this:
I can derive a certain amount of information about what SQL Server is waiting on just by looking at this chart – clearly the predominant wait class is Transaction Log. And if I were to hover over the red portion of that column, I could see the actual wait(s) involved (in this case, resource waits are 37ms under WRITELOG):
However, if I right-click in the chart and select Quick Report…
…I’m presented with a lot more detail (click to enlarge):
By the way, Quick Reports are available from other areas of Performance Advisor, including:
- the Disk Activity and Disk Space tabs (right-click on file icon or a row in the grid);
- any of the Dashboard Sample Mode gauges (Network: Queue Length, CPU Usage: Context Switches, CPU % of Total Waits, Page Life Expectancy, etc.); and,
- any of the performance metrics in the Performance Advisor Overview that also display a historical graph on the bottom of the screen.
Thinking Outside the (Reporting) Box
Event Manager and Performance Advisor provide another form of report – data exports from grids. Let’s look at two examples, one for each product.
Event Manager Calendar
When we open the Event Manager Calendar in List View (did you know about List View?), we see a grid showing event history for the chosen connection, start time, interval selection, and any other event view filters in place. The list is grouped by Event Source and Object. Here’s my example (click to enlarge):
Now go to the menu bar and select File > Export Data…, or just press Ctrl+E. You will be presented with a Save As dialog box where you can specify a file name and save the exported data in Excel, csv, pdf, or html format. See these results in Excel format.
Performance Advisor
PA for SQL Server uses data grids in every tab except the Dashboard, including AlwaysOn Management. All of these grids can be saved in one of the formats noted previously. For analysis and troubleshooting purposes, Excel is often the best format choice. You may find the PDF format more suitable in other situations, such as when included as part of a management report.
Note: On tabs that include graphics of some sort (Disk Activity, Disk Space, Indexes, Deadlocks), the Export Data functionality will not include the graphics.
Something to keep in mind is to ensure your grid includes the right data set before performing the export. Set the time frame using the Start and End date/time controls in the toolbar. Add and remove columns using Column Chooser. Group By, Sort and Filter using the techniques I outlined in my previous post to include only the data you need.
For this example, I am using the grid from the Deadlocks tab. The process is identical to the steps followed in the previous example: Capture the right data, using the techniques noted previously, and press Ctrl+E and save to a file.
Here is a crop of my Deadlocks tab grid (click to enlarge):
You can also see the results in this Excel file.
** Deadlocks bonus – right-click in the deadlock graph and select “Export Deadlock to XML.” You’ll be able to open the resulting .xdl file in the SQL Sentry Client (or Plan Explorer PRO) for additional troubleshooting at a later date.
Last, but not least
I’ll end this post with a final, but ubiquitous report. Clicking on the toolbar’s Report button while in any of the Performance Advisor tabs produces a Windows + SQL Server Performance Report for that SQL Server instance, within the Start and End times currently selected. It’s a quick and easy way to create a report version of the dashboard metrics.
