Your Performance Data, Your Way With Custom Charts
Our product and engineering teams have spent a significant amount of time over the past year working on a new dashboard experience in SQL Sentry Portal to give you the upper hand in monitoring your servers and diagnosing performance issues. Providing control over the way data is displayed is one of our most requested features, and we’re excited to satisfy this request with custom charts.
This journey began with our original take on customizable dashboards when we released SQL Sentry Portal in 2020. The first version allowed DBAs and application developers to organize preset charts in different configurations while mixing multiple targets in a single view. Now, with the next iteration, we’re taking a big leap forward and allowing you to choose the data you want to see on a dashboard. There are nearly 200 metrics to choose from, including data from Windows, SQL Server, Azure SQL Database, and Amazon RDS. Let’s take a look at custom charts in SQL Sentry Portal.
Custom Charts in 60 Seconds
One of the first things you’ll notice after upgrading SQL Sentry Portal to version 2021.1 is the new Dashboards item in the navigation menu, shown in the screenshot below. Selecting Dashboards will display the new Dashboard Digest page.
The Dashboards menu item in SQL Sentry Portal
The Dashboard Digest displays all dashboards in your environment and provides management functions to create, update, and delete custom dashboards. For large environments with many dashboards, there is also a search feature.
The Dashboard Digest in SQL Sentry Portal
After opening a dashboard, the configured charts will display with data for their respective time ranges and targets, as you can see in the example below.
Dashboard showing all configured charts
Clicking the Edit button will open the edit view, where you can design a custom dashboard. This view gives you the ability to drag-and-drop to reposition charts, add charts from a predefined template, or create a new chart.
The dashboard edit view
Clicking the Add button on the Custom Chart sidebar will open the Customize Chart dialog box, shown in the screenshot below. This is where the fun begins. You can customize the name, chart type, target, and metrics to display. The live preview lets you see what the chart will look like once it’s saved to the dashboard. The Sample Period defaults to one hour but can be extended to the past day or past week to get a better sense of how your data trends. The Sample Period also extends the range of the Instance dropdowns to include values occurring less frequently.
The Customize Chart dialog box
In the Performance Metrics section, you can configure the data to display in the chart. The Metric dropdown options can vary by target, most commonly by target type and version. The Instance dropdown is enabled only for particular metrics providing partitioned data, such as NUMA nodes or SQL Server instances.
The Metric and Instance dropdowns are also searchable. You can type a phrase such as “cpu” to see all the CPU-related metrics rather than scanning the list. There are currently more than 200 metrics to choose from and more on the way.
Beneath the Performance Metrics section is an option to enable the custom time range. This option disconnects the chart from the time range selection in the navigation bar. You can use this option to set particular charts that always observe a larger time range to assist in spotting anomalies. Note, enabling Custom Time Range won’t affect the chart preview but will take affect once the chart is on the dashboard.
The Custom Time Range option in the Custom Chart dialog box
As metrics are added to the chart, you might see the y-axes shift. Under the hood, every metric has an assigned unit of measure. Metrics with the same unit of measure will share the same y-axis, while a metric with a separate unit of measure will split into its own y-axis. This allows you to correlate different types of data without skewing the visualization. The unit of measure is also present in the formatting of values, such as in the y-axis ticks and tooltips.
Custom Chart with separate y-axes
With the new custom dashboard experience, the Dashboards tab in the target view has been replaced with Performance. This view will show the same information as the Performance Analysis dashboard from the previous version.
Trends, Correlations, and Creativity
Now that you know how to customize charts, let’s have some fun and look at examples of the charts you can build.
CPU Consumption
One common thing we like to observe and monitor is CPU consumption, but some workloads have different demands. For example, I once saw a machine with some bad code that exhausted one CPU core while the other cores were unaffected. The average CPU consumption was acceptable, but customers were reporting the application was sluggish or unresponsive.
With custom charts, you can quickly identify this workload issue by selecting individual CPU cores overlaying the average CPU consumption, as shown in the screenshot below.
CPU chart exposing individual core consumption
Another time, I observed a machine with periodically degraded performance. Without proper monitoring, it would typically recover on its own before I could diagnose the problem. It turned out to be the backup strategy in use that replicated backups across regions.
You can build a custom chart to quickly identify and monitor this behavior by charting SQL Server Backup/Restore with Network Out Throughput. This could be combined with a Network In Throughput chart on the receiving server to better distinguish the traffic from your backups from other traffic.
SQL Server Backup/Restore overlaying Network Out Throughput
Metrics Separated by Process Name
Sometimes there are specific processes you want to monitor. Custom charts include a set of metrics separated by process name to help you do this. You can monitor agents, web servers, or even SQL Server services.
Process CPU consumption
Top SQL Metrics
The Top SQL metrics are also available to chart. With Top SQL metrics, you can view and compare CPU, duration, execution count, logical reads, and physical writes by application, database, login, or host. These metrics can be mixed in various ways for different workloads, based on whether you want to focus on an individual application, as shown in the screenshot below, or to compare multiple at a time to diagnose the source of load on the host machine.
Top SQL applications
Conclusion
We can’t wait to see the incredible things you do with the new custom chart functionality in SQL Sentry Portal. As always, we would love to hear your feedback on the latest updates and what you’d like to see next in SQL Sentry Portal.