Monitoring SQL Server: An Interview with David Bick

Simple-Talk sat down with David Bick, a Red Gate Product Manager, to discuss his work on SQL Monitor and why passively monitoring SQL Server just doesn't cut it anymore.

1971-David%20Bick.jpg David Bick works for Red Gate software as a Product Manager. He’s been wrestling with databases for 20 years, and is genuinely surprised that animated gifs are still a thing.

Following his time as a developer and a technical architect, he’s spent the past couple of years working on SQL Monitor. After a long day in front of a computer he likes nothing more than to relax in front of a computer playing games, reading contemporary fiction, and getting involved with his local church.

Simple-Talk decided to take a look at the motivation behind creating a SQL Server monitoring tool from the man in charge, and found the need for active monitoring is something very close to home for Red Gate after experiencing some monitoring pains of their own.

So David, you’ve been the Product Manager for SQL Monitor for the past couple of years, why create a product to monitor SQL Server when there are so many ways to diagnose a SQL Server problem?
SQL Monitor isn’t the sort of tool you’d suddenly scrabble for when the server is melting, it proactively monitors your servers so you have time to fix things before there’s a disaster. The hardest time to introduce monitoring is when everything is going haywire and you don’t have that baseline to compare with, so that you can’t be certain about what’s changed from yesterday.

Most of the people responsible for databases know that monitoring is a good thing, but when they do monitor, they do it as a response to a problem. There’s a group of DBAs who are using custom scripts that are cobbled together one way and another to do the job. These custom scripts are fine because they tell you some things well, but they often tend to be really narrow in what they look at, so you put them in place in response to a particular disaster and then the next disaster is a different flavour.

The problem with reacting to an existing problem is that you don’t always know how much CPU usage or memory there should be at this particular period of time; if it’s the day of the week, month or whatever.
Absolutely. SQL Monitor will overlay yesterday’s or last week’s data on todays, so it’s really easy to see what’s changed. You can also start to see some of the longer-time trends as well: you might want to see how your data files are slowly growing and will one day exceed the capacity of the disks they’re on. We capture lots of metrics, most of which are just Windows performance counters or things that SQL Server has in its DMVs. We’ve even introduced advice in the tool as to what sort of values you should expect, but as usual the answer to that is ‘Well, it depends’. Unless you can look back at yesterday, last week, or last month, knowing what’s normal is not something you can do in that moment of panic. If you have that data collected all the time and kept for you in a data repository, then you’re much better placed to spot what’s different from last week. We’ve made this easy in the tool –
And get alerted for it?
Sure. The user can configure the alerts to set the type and quantity of the threshold for the alert. You could, for example, get an alert if there’s 10% disk space left, or maybe if there’s three gig left. You’ve set the thresholds to give you time to rectify that before you run out of space.


A view of the baselines in SQL Monitor

If you’re rolling your own system, I suppose you’d have to set up maybe several different alerting systems.
Exactly. We combine those alerts that are just based on the metrics with others that concern things like job failures or even whether the correct services are turned on or off. SQL Monitor looks at CPU, disk I/O, transactions per second, page life expectancy; all those kind of things from the host machine or from SQL Server. You can, of course, add in other metrics as well, because we’ve got a feature that allows you to write your own custom metrics which aren’t included in the core product and have those collected at the same time.
With custom metrics, could you have your application metrics in place so that you can detect that whenever a large number of business events such as shopping transactions happen in your application, then you’re going to get high CPU in the database in consequence?
Absolutely, and that’s really important for us at Red Gate. As a DBA, you may see your responsibility as just keeping the server up and running. That’s okay as far as it goes but your server is there to run applications for people to serve a business in different ways.

I’ll give you an example: With the majority of our products, you can download a trial before you choose whether you want to buy it or not. When you do that we ask you for your e-mail address and we record that in a database. That e-mail address is only recorded if some JavaScript on our website works. Like everyone else, we make mistakes, and we published a broken piece of JavaScript to the website without realising it. The customers could still download the tools, but all the e-mail addresses just got dropped on the floor. The database looked fine, but the end-to-end system was broken. Nobody noticed for a day or two because we didn’t have a metric in place to count whether we had the right number of email addresses to downloads today until one of the sales guys finally noticed, then we jumped to react. Now we have a custom metric that alerts us to the fact pretty quickly. In terms of the industry-wide valuation of business ‘leads’, you’ll have paid for the product by reacting instantly to a mistake like that.

So why have you chosen to develop a hosted solution?
With SQL Monitor Hosted, there’s no big upfront cost, particularly if you’re not completely convinced that this is going to be a valuable thing for you. We can allow you to do it as a subscription service that you can try for free for a month to see if it suits you. If you want to continue, then you only pay a small amount per month per server. Generally, there’s a big upfront cost to putting a monitoring system in place, especially if you’ve got a large number of servers that you’re responsible for so we decided to make it easier to get started.

The on-premise SQL Monitor product is easy to get going with, but you need to have a web server set up and have a SQL Server database free, which may mean that you’ve got to reach for another server, set up another VM or provision another database somewhere. By having a hosted solution, you now don’t need a VPN link to your organisation to access SQL Monitor. You can just access it directly like any other Cloud application.

So it’s very little commitment. Whereas if you’re installing a monitoring server and a IIS that’s going to be a significant cost to the organisation, and there will be far more delay before alerting and monitoring are operational. Just commissioning the extra hardware would take a month’s wait in the average shop.
Absolutely. With a hosted service they could just get started in a few minutes and then if they find it a valuable service they can keep going with it. We can also roll out a new version which will be instantly available to everybody. We have feature usage reporting which, if people choose to opt in, allows us to see which areas of the product we need to fix or improve and then roll out those fixes without having to disrupt the customers because they don’t have to install anything else. They probably won’t notice anything other than maybe noticing that the software is better than it was yesterday.
So why would someone choose the on-premise SQL Monitor instead of the hosted version?
Some people work in organisations or industries where even allowing monitoring data out of the building isn’t an option. For them, the on-premise version is still the right way to do monitoring. There are other people as well who want to know that they have complete ownership of everything they rely on. With any hosted solution, you need to trust that the provider can operate that reliably for you. Some DBAs don’t want to take that risk and again I can sympathise with that. They can use the on-premise version, look after the database server and the web server themselves and have that security of knowing that everything is in their hands.
Who is your average SQL Monitor user?
SQL Monitor appeals to people for different reasons. The accidental DBA will find that that the product is monitoring the essentials and it’s explaining them to you as well. Expert DBAs like SQL Monitor as well because it’s doing the essentials across a whole bunch of servers. You can spread that monitoring goodness across all the servers in your care, including those that are generally well-behaved.

I think the other group is the devops user. These guys may seem like the ‘accidental DBA’ but they tend to have a very clear business focus on what their application is meant to be doing for their users. They really appreciate being able to monitor the application and also know when the database is causing problems for their users.

I’m wondering if SQL Monitor is a developer tool as well? It can be used to get a much better feel for scalability and how your application runs under stress if you monitor your test servers while you’re giving them various different application-loadings.
It won’t surprise you to know that we use SQL Monitor to monitor SQL Monitor while it’s in development to make sure that we’re not introducing big performance regressions with each version of the product. I think it’s interesting as a developer now having to have that mixed mentality between seeing the big picture yet profiling down at the level of an individual web request maybe and seeing the bits of SQL that were executed as a result of that, and where the time was spent. The detail is required but you still need that ‘zoomed out’ view of “‘Okay, well here’s my new version of the software and here’s a typical load profile and now let’s just see what’s different from last time, you know what have I screwed up?”
Yes. Because, as a database developer, the big problem I have is getting the helicopter view of how the application is working in its relationship to the database server, particularly as you increase the stress on the system. I can’t think of any other tool that gives you that very simple view of how the database is reacting under load.
Absolutely. It’s not just that either. You often need to know how the application behaves over a long period of time, like when doing soak testing. There are plenty of performance load tools out there that will do load testing and collect some metrics as well, but you can only use them under a load profile that you set up artificially.

While we’re developing SQL Monitor, we have copies of different versions of the software constantly running on some test machines under a simulated load, with SQL Monitor then monitoring those instances. At any point in time, when we’re releasing new versions of the software or as we’re going through just internal builds, we can see if they’re all behaving in a similar fashion. We can check very quickly whether CPU is still around the same sort of level despite the fact that we’ve added some new features. Have we suddenly done something to mess with disk I/O or bloat the memory consumption? This test system tells us very quickly.

That sound like it’d have enormous value. It could save weeks, maybe months, of development time.
That’s the goal.
Tags: , , , , , , , , ,


  • Rate
    [Total: 9    Average: 3.7/5]
  • Dave

    Sales Pitch
    This article is just a giant sales pitch. -1

  • rickvidallon

    SQL Monitor
    Hello David,

    My name is Rick Vidallon. I run a small web firm in Virginia Beach since 2000 and hoping you might point me in the right direction.

    I will admit I glanced at your article needing to move on to another emergency so please forgive if your article already answers my questions.

    I would like to ask you the following:

    – Does SQL Monitor run as a 3rd party service?

    – I have many client .Net sites running on ‘shared’ Windows hosting environments. I am looking for a service that will tell me any or all of the following;

    a. Site is not loading
    b. SQL is stalled or has some issue relating to the DB or Server.
    For example, 9 times out of 10 the application pool is the cause but have to call support to determine this.

    I know that I could have this service on a managed VPS or Dedicated plan, but most small business clients cannot afford the cost.

    Rick Vidallon