SQL Monitor: Dashboard Design
The Red Gate SQL Monitor tool is a dashboard tool, which helps DBAs and other critical-operation team members to monitor and assess the performance of SQL Server. SQL Monitor is interesting from the data visualisation perspective, because it aims to present information in a way that is intuitive to users, even though this information can be rapidly changing. In this article, we will look at those Data Visualisation principles that underpin the design of the dashboard. Before we proceed, let’s have a brief overview of the SQL Monitor dashboard.
The purpose of the dashboard is to provide the information that the DBAs need to know right now, in order to act upon it. SQL Monitor offers a number of different displays to support this purpose, including:
- Exploration: Reliably reporting critical issues in SQL Server in real time
- Resolution: Historical performance data is stored, to make it easier to find the ‘troublespot’ at which the critical error occurred
- Interpretative: Performance data is presented in an easy-to-understand manner
- Call to Action: Provisioning information so that DBAs and operational team members can devise a relevant action-plan in order to deal with the issue, and isolate it before it develops into a further problem
An example of the Global Overview can be found below:
Dashboards can take a number of forms. One example is Stephen Few’s ‘Faceted Analytical Displays’, which he describes in his intriguingly entitled ‘Three Blind Men and an Elephant (PDF)’. He defines analytical dashboards in terms of ‘interactive data explorations and analysis’. These Faceted Analytical Displays are designed to allow data consumers to ‘surf’ their way through data visualisations in order to answer a business mystery.
This Analytical type of dashboard is different from the Monitoring type of dashboard, where the data has to be understood quickly in a crisis. Essentially, the purpose of the dashboard is to support joined-up thinking about troubleshooting performance problems, primarily to reduce decision-making time. This type of dashboard answers a business question, in the case of SQL Monitor ,‘What is it that the DBAs need to know right now, and what is it that they need to act upon?’
Dashboards like this are only useful if they contain relevant data, and are designed to provide the relevant insights to the question that is being asked. Once the business question of the dashboard has been determined, the most relevant pieces of data must be chosen. There needs to be a balance between providing the most important pieces of data, and providing too much data. If the dashboard is too ‘busy’, then the message of the data will be lost. Data Visualisation expert, Edward Tufte, coined the phrase ‘chartjunk’ to denote overcomplicating data with unnecessary graphic decorations, and it has even been used to denote ‘statistical stupidity’ (Tufte, 2002). This issue is particularly important when the purpose is to deliver real-time, mission critical information; it is essential that the data consumer isn’t distracted by information that misdirects attention away from urgent information. Thus, there is a good reason that the SQL Monitor dashboard isn’t completely overloaded with fancy visualisations.
The dashboard should never distract away from the message of the data. Dynamic visualizations must support resolution techniques, provide an overview of the data, and allow the DBAs to zoom to a specific subset of it. Therefore, DBAs can see a Global Overview, and then filter down to the cluster, and then down to the individual server. A sample structure can be found below, where the user can drill down using links on the left hand side:
The Summary -> Filter -> Drill to Detail structure of visual guided analysis is supported by psychological literature by data visualisation experts such as Ben Schneiderman, who devised this as a ‘Visual Information-Seeking Mantra’. If you’re interested to know more, then Ben’s Eight Golden Rules of Interface Design is an interesting read and I’d particularly recommend one of Schneiderman’s essays, ‘The Eyes have it (PDF).’
Dynamic visualizations are most valuable in environments that require monitoring or manipulation of large quantities of data, in real time, and under tight time constraints.
SQL Monitor follows a simple paradigm which allows DBAs to quickly learn the basic functionality, with only a limited need for error messages. It also means that users can immediately see if their actions are supporting their investigations, and they feel in control of the dashboard.
The data visualisations themselves are simple, but I’d like to point out one in particular: the sparkline. Sparklines are defined as small, intense, datawords by their creator Edward Tufte (2004), who designed these small lines of data so that they could be displayed anywhere; in a paragraph, spreadsheet, sentence or even inside a table cell. The key to the sparkline is that it is simple, elegant, and tells a story simply. Sparklines can be found as part of the Red Gate SQL Monitor in order to represent Processor Time (%). Here is a sample illustration, taken from the Red Gate SQL Monitor tool:
By providing data-intensive displays on a small scale, the sparkline can assist the DBA to identify patterns in the processor time that are abstracted away from the detail. This can assist the user to see if the processor time is spiking, for example, or whether the processor time is following a regular pattern.
In contrast, there is room for improvement – the ‘Status’ arrow and tick icons could be simplified so that they aren’t distracting the main scene, but are complementary to the message of the data instead. It would be good if these issues could be neatened for vNext.
To summarise, SQL Monitor adheres some interesting data visualisation principles. The SQL Monitor tool dashboard is interesting since it needs to be able to display the information simply, whilst ensuring that the key functionality is required.
In truth, the best designs are the ones which the user does not notice; instead, they carry right on, and carry out their tasks. It is important to be able to value to a dashboard, without increasing complexity or impacting user adoption. The SQL Monitor dashboard is a tool which new DBAs will feel at home in, after installing and configuring it, whilst also providing more experienced DBAs with the information that they need. You can download SQL Monitor from Red Gate.
Beautiful Evidence (2006) E. Tufte. Graphics Press.
Cancer Survival Rates: tables, graphics and PP (2002) E. Tufte. www.edwardtufte.com
Three Blind Men and an Elephant (PDF) (2007) S. Few. www.perceptualedge.com
Thanks to Jen Stirrup for this guest blog post!