SQL Monitor 3.5 – Baselines

The latest version of SQL Monitor has a new ‘baseline’ feature on the Analysis page. This is the third successive release this year aimed at helping to determine if a metric is ‘normal’ and follows the improvements to the time navigation controls and the ability to display multiple metrics on the same Analysis graph.

This represented a significant challenge for our team, figuring out how to surface something simple but effective from the complexities of the underlying feature, and we’re grateful again for the support and feedback of everyone that took part in our research sessions and helped us to understand what was required.

Take a look at our baselining page to get an overview of the new features, and see what Grant Fritchey has to say in the video below:

And if you’d like to see what we’re working on now, and influence the shape of the feature we’re developing, please email us and we’ll send further details.

Posted in Development update | Leave a comment

SQL Monitor 3.4 – Analysis Improvements

For the most recent release of SQL Monitor, version 3.4, we’ve focused on making further improvements to the Analysis feature. The previous release, version 3.3, introduced the ability to add multiple metrics for comparison, to make it easier to see correlations and aberrations. Having identified interesting peaks and troughs, we now want to help answer the question, “Is this normal?”, so we’ve made it easier to select customisable time ranges.

Comparing metrics

It can be useful to see how the metric looked at a previous time, the same time on a previous day or week for example, to establish if this is a pattern that repeats itself or is an anomaly. As well as adding more sensible defaults, we’ve made the comparison feature fully customisable (you can try this out on the live demo) and the time ranges more visible.

We encountered unimagined complexity with this feature and it seemed whenever we were satisfied with a solution, we’d run into another edge case that would break it. After much head scratching, sketching and coffee drinking, we think the new customisation is a significant improvement on the previous version, but we’d still like to know how you think we could improve it further.

Navigation

We’ve also made the analysis graph navigation more interactive, so it is now possible to quickly drag the graph and easily zoom in and out.

Panning – using a mouse, click and hold, then drag the graph, or use the panning buttons at each end of the graph.
Zooming – to zoom the graph, position the cursor and then use the mouse scroll wheel or double click or use two fingers vertically on the touchpad.

In parallel with this, we’ve continued to make improvements to the performance of the feature so that the graph is more responsive. However, the Analysis page may be slow if you’re using Internet Explorer 7 and 8, so we recommend using Chrome or other browsers, or updating Internet Explorer.

We’ve also fixed the persistance of the selections, so you can navigate away from Analysis and when you return, the settings will have remained. We know that troubleshooting involves a lot of back and forth, so this should make it a smoother experience.

What’s next…

In a word, baselines. We’re currently developing a feature that will show baseline(s) for a metric, as we continue to focus on helping answer the question “Is this normal?”

We’re intending to release this very soon, but if you’d like to take a sneak preview then email us and we’ll get back to you…

Read more about SQL Monitor
Suggest and vote for improvements you’d like to see

Posted in Development update, Metrics | Tagged | Leave a comment

SQL Monitor 3.3 – Email Improvements

For SQL Monitor 3.3, we’ve improved on one of our most popular features: alert emails.

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:

Email Improvement

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.

Posted in Development update | Leave a comment

SQL Monitor 3.3 – Analysis Improvements

We’ve just released a new version of SQL Monitor, version 3.3

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.

Analysis correlations

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:

Inherit

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:

Inherit

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

One metric across multiple servers

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…

Posted in Development update | Leave a comment

Release 3.2.1.231

We’ve recently pushed out a minor release – version number 3.2.1.231

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:
Updates Available Message

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…

Posted in Development update | Leave a comment

SQL Monitor’s data repository

As one of the developers of SQL Monitor, I often get requests passed on by our support people from customers who are looking to dip into SQL Monitor’s own data repository, in order to pull out bits of information that they’re interested in. Since there’s clearly interest out there in playing around directly with the data repository, I thought I’d write some blog posts to start to describe how it all works. The hardest part for me is knowing where to begin, since the schema of the data repository is pretty big. Hmmm… I guess it’s tricky for anyone to write anything but the most trivial of queries against the data repository without understanding the hierarchy of monitored objects, so perhaps my first post should start there.

Read more from the original post on Simple-Talk

Posted in Uncategorized | Leave a comment

SQL Monitor – development update

Here’s an update on what the SQL Monitor Development Team have been working on just recently for version 3.2 and beyond.

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.

SQL Monitor Metrics

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.

Install Metric and Alert

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.

SQL Monitor Hosted

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

Posted in Development update | 1 Comment

Dashboard Design – guest post by Jen Stirrup

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:

SQL Monitor dashboard

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:

Global overview

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:

Processor time

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!

Posted in Designs | Leave a comment

SQL Monitor 3.1 released

We’ve just released SQL Monitor 3.1 – here’s a look at the new features and how we developed them…

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.

Previous/Next

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.

Hotspot

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.

Newer Older

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.

Clear filter

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.

Time range

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…

Posted in Uncategorized | Leave a comment

SQL Monitor v3 Early Access Program update

Last week we sent out the third of our Early Access Program releases (you can still sign up for the SQL Monitor EAP and get the latest release). There is a new feature – user roles – and an update to the custom metrics and alerts feature.

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.

User roles

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:

Specify databases

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

Test metric collection

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…

Posted in Early Access Program, Metrics | Leave a comment