When configured with an SMTP server, SQL Monitor can send email notifications whenever new alerts are raised or when their severity has escalated. It’s not surprising that some of our top feature requests on UserVoice relate to these alert emails, because they allow you to spot server problems before your customers do. In the latest version of SQL Monitor, we’ve improved the subject lines and message bodies of emails to help you get to the bottom of any server issues much more quickly.

Let’s have a look at the subject line first. An email from SQL Monitor 3.2.1 looks like this:

SQL Monitor 3–40632355 | server1 | Processor under-utilization (Raised – Low)

Much as we love to promote ourselves, we believe that any excitement for seeing “SQL Monitor 3″, followed by an arcane numeric ID, will wane after a few occurrences. The new subject line puts the most important information first, allowing you to quickly identify groups of similar alerts, for example:

Processor under-utilization | server1 (Raised: Low)

In the email body itself, we’ve added a new Details section to provide further information about why the alert was raised. For example, if the alert is about blocked processes, details for both the blocking and blocked process are displayed. For a “Long Running Query” alert, the email includes the T-SQL fragment causing the alert:

Who wrote that?!

And don’t forget, our integration with PagerDuty gives you a great deal of control over when and where email notifications are sent, allowing you to set up rules based on the alert severity, time of day, and more.

With our new alert emails, we’re trying to help you decide quickly which alerts require further investigation. SQL Monitor 3.3 is available for download here. What do you think of our alert emails? We’d welcome your comments.

This release features some improvements to the Analysis feature. We’ve had a number of requests, including a well-supported suggestion on UserVoice, that we allow the selection of multiple counters.

Multiple metrics

Viewing multiple metrics could be achieved by adding separate graphs for each metric, or by overlaying multiple metrics onto one graph. For this release we’ve chosen the latter implementation because the feedback we’ve had on the designs suggests this will make it easier to see correlations and anomalies.

The design should allow you to view multiple metrics for a machine, then quickly view the same metrics for another machine. By default, each metric that is added will inherit the object of the previous metric. For example, choose a machine and subsequent metrics will also default to that machine, displaying ‘As above’. In the image below, I chose to view Full scans/sec on the local instance of machine rg-sql01. When I added the Page splits/sec and Page splits/batch request metrics, they inherited the instance and machine:

To view the same set of metrics on a different machine or server, simply change the object on the first metric and the metrics below will inherit the change where possible:

Of course, you can also view the same metric across multiple servers if you wish:

Coming soon…

We are working on further improvements to the Analysis feature, but wanted to release this as soon as possible so that we could get more feedback from our users, to influence the development. Currently, our plans include adding the ability to:

• Drag and zoom on the graph, so that you can easily move back in time and zoom in on points of interest
• Compare a time range to another range more easily and with a better selection of default comparison choices
• Compare against a baseline so you can quickly tell if behaviour is ‘normal’

Please email us if you would like to share your feedback or preview our latest designs via a remote session. You can also share your suggestions on our UserVoice site.

Tomorrow we’ll post more about other improvements in this v3.3 release…

It contains the following two fixes:

1. IE 10 – SQL Monitor UI now works on IE 10
2. SQL Monitor will now be able to monitor SQL Server 2012 which has AlwaysOn availability group feature turned on

To take advantage of the Availability Group feature using SQL Monitor, you will have to add every node on the availability group as if it were a standalone machine. This way, SQL Monitor will monitor all instances on all nodes, be they active or passive. For easier handling, we’d recommend putting all of these machines into the same group (as in the “folders” that you can put monitored servers into in SQL Monitor). The instances will not look like part of a cluster in the UI, but they will behave accordingly (except instead of a Cluster Failover alert, SQL Monitor will report cluster failovers on Availability Groups by raising a “Machine/SQL Server Unreachable” alert on the failing entity.)

SQL Monitor will display a message above the product name banner when it next checks for updates:

A few links with good explanations about SQL Server 2012 Availability Groups:

A blog post from November 2010 when the feature was first announced:
http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/
The same site has a section on SQL Server 2012 Availability Groups:
http://www.brentozar.com/sql/sql-server-alwayson-availability-groups/
including a guide to installing it:
http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/

This blog post on Technet is also a good basic introduction to Availability Groups:
http://blogs.technet.com/b/andrew/archive/2011/11/15/sql-server-2012-always-on.aspx

This MSDN article goes in a bit more depth with some of the technical issues:
http://msdn.microsoft.com/en-us/library/ff877884.aspx

We’re currently working on making more improvements to SQL Monitor and will post an update shortly. Watch this space…

Read more from the original post on Simple-Talk

Custom metrics library

Since developing the custom metrics feature, we’ve seen it put to some interesting uses, ranging from simple but effective reality checks (Time since last restore) to complex scripts giving insights into query plan performance (Number of ascending statistics) . We decided it would be useful to gather some of these and put them somewhere for other people to use and hopefully add their own too.

We could have simply posted these scripts up on an existing resource, but we wanted to make it especially easy to use these metrics in SQL Monitor, so we felt it justified having its own site. This also gave us the freedom to implement the ‘Install metric’ functionality, allowing users of SQL Monitor 3.0 and later to export the T-SQL and associated settings directly into the app from the website.

We’re delighted to already have contributions from Jonathan Allen who gave the feature some early thrashing and was quick off the blocks providing his own custom metrics, Fabiano Amorim, TJay Belt, Stuart Ainsworth, Louis Davidson, Brad McGehee and Grant Fritchey who when he saw the prototype was moved to say ‘this is pretty cool’. We’re grateful for the feedback, support and contributions.

We’re hoping these custom metrics help people broaden their range of monitoring and that they’ll share their own metrics with us so we can put them on the site for others to use. There’ll be more news on this coming soon. Meanwhile, let us know if you think there’s anything we can do to improve the experience.

Fixes and enhancements

We are always looking at ways to improve SQL Monitor and we keep a close eye on the suggestions on our UserVoice site. Although the getting the metrics site live promptly required some intensive development, we did find some time for a few fixes and enhancements to the application. Most obvious of these tweaks is the ordering of databases by name rather than ID. It made sense to group the system databases at the top of the list, but below that the user DBs are ordered alphabetically (granted, the ability to sort would be even more useful and maybe that’ll make it into a future release).

Here’s how to upgrade to 3.2 or download a free trial.

Hosted Monitor

Next, we’re focusing on a hosted version of SQL Monitor where a service is installed close to your DBs and then we take care of the resources needed to power the monitoring. We’re hoping this will help those who find the main barriers to monitoring include the initial set up costs and time required, the on-going maintenance of the hardware and the long-term investment. By taking care of all the hardware, we’re aiming to make it quick and easy to start monitoring and provide it for a monthly subscription to make it less of an up front commitment.

If you’d like to find out more, try the forthcoming beta release or get involved in a feedback session, then sign up.

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.

References:
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!

Next we would like ‘Next’…

For version 3 of SQL Monitor we used UserVoice so people could tell us what they wanted to see in the next version of SQLMonitor. The most requested feature was to add ‘previous/next’ navigation to the Alert details so users could navigate between issues within the same screen. The root of this problem was that the filtering in the Alert inbox did not persist as it should. That is, when a filter had been set, navigating between alerts or changing other parameters would undo the filter.

Now it may seem like the simplest of things to ‘add previous/next’ to the Alert details page and indeed we could have done just that and released it. But we took some time to research how the Alert inbox and detail pages were being used and user-tested several designs and we think we’ve improved on it. Here’s how we arrived at the released feature.

Research

We often think of the Alerts as being like emails in an inbox and the most popular email clients – Outlook, Gmail, Yahoo and Hotmail – all place previous/next in the top right-hand corner of the emails and this felt like the most intuitive place to put them.

Test

We quickly created a prototype and showed the design to one of our ‘heavy users’ Jonathan (@fatherjack) who immediately thought they should be moved. He identified a ‘hotspot’ of activity where users focus when working through the alerts. Both in the inbox and on the detail pages, the important stuff is often over to the left and there’d be a lot of eye and mouse travel if the previous/next navigation was placed top right.

This made sense to us, but we tested this on a few other users and heard a few more comments on the position. So we moved them to the hotspot, ran a couple more tests and were satisfied with the position.

However, early in the round of tests we discovered that using up/down arrows or the words previous and next could be ambiguous. Did previous mean the next oldest or newest? Different users gave different answers. One user suggested ‘newer/older’. We iterated the design, continued testing and found no more ambiguity.

We also discovered that investigating an alert could lead the user away from the alerts altogether, to the Anlaysis or Overview pages for example, but users wanted to return to the filtered alerts. So we’ve now made the filtering persist until manually cleared.

And there’s more…

We’ve made a few tweaks elsewhere including adding more default time ranges to the Analysis page and fixing some bugs and known issues.

We hope we’ve improved SQL Monitor and we’re grateful for the support of our users, especially those who’ve taken the time to email, leave a suggestion on UserVoice or take part in a usability sesson – it’s invaluable getting feedback from people using the software – so please email us if you have any further feedback or if you’d be interested in our next round of usability sessions.

Right, now it’s on with the next version…

User roles:

The most requested feature via SQL Monitor Uservoice has been the ability to have multiple levels of user. However, during our research we heard several times that adding a new set of accounts to manage could potentially be a headache. So we designed with simplicity in mind.

There are now three levels of user:

Administrator – full control including control of user access to SQL Monitor
Standard user - limited access, but can for example manage alerts and configure alert settings
Read-only – can’t configure any settings but can view most areas except the Licensing page etc

The Administrator role is created on intial login. The Administrator can then optionally create the other two levels of access. Each role is activated by a single password that you share with relevant users, avoiding the ‘headache’ of multiple user accounts with specific roles and privelages allocated to them.

We’ve had many suggestions for additional functionality around this feature – for example, extending the control to instance level access or integration with Active Directory – and we’ll certainly consider these suggestions carefully when we’re deciding what goes into a future release. For now, we hope the additional levels of access will provide a useful way of restricting access to parts of SQL Monitor.

Custom metrics and alerts

This new feature, updated in the latest EAP, is one of those features an administrator might wish to restrict access to. It allows the user to make collections from their servers and then receive alerts when certain threshold values are passed.

We’ve made changes to this in the latest EAP. The feature now uses a three page wizard to allow users to:
1. define the metric
2. optionally create an alert based on the metric
3. see a summary before saving them.

We’ve added the ability to choose which databases to run the T-SQL against:

And to test the T-SQL to make sure it is returning a single numeric value:

It’s also possible to create the metrics and alerts in disabled states – for example, you might want to create a metric and an alert but save the alert in a disabled state so you can evaluate the metric before you decide it’s ready to fire alerts.

To support the feature we’ve made available several ready-to-use example metrics and alerts – such as a metric to collect rate of change of database file size in MB/hour with an accompanying alert when the database file size has increased beyond certain thresholds.

Please continue to let us know what you think of the new features and what you think can be improved and we’ll keep you updated on developments…

The first EAP contained a lean implementation of our new ‘Custom Metrics and Alerts’ feature. This gives users the ability to run custom T-SQL against their databases, monitor the data and receive alerts when the data breaches the defined thresholds. We wanted to get the bare bones of the feature out as soon as possible so we could begin getting feedback as we continued to work on the feature.

What’s in the next EAP?

We’ll have an improved version of the Custom metrics and alerts feature in the forthcoming EAP.

We’ve also asked for suggestions on what you’d like to see in v3 via SQL Monitor Uservoice. The most commonly requested feature has been the ability to have multiple levels of user, for example, a “’guest’ user who could review the data that was collected but not modify the settings”. So we’ve been working on this feature and it should also be in the next EAP. Again, we’re interested to hear your thoughts on it.

We’d like to take this opportunity to thank everyone that has given us feedback so far and we’re also grateful for everyone that enabled the automatic error and feature usage reporting which are particularly useful ways for us to receive technical feedback from the EAP without requiring effort from the user.

If you’d like to sign up to the next EAP, please email SQL Monitor and we’ll send you the details.

Who knows, next year we may even give you the chance to win a ticket back…

DBA in Space