Tag: monitoring

Monitoring a database server is something that many of us know is important, but we often take the process for granted. Whether we’ve purchased a tool, like SQL Monitor, or we’ve built our own system, we often set up a watcher for our systems and rarely view the details unless something goes wrong. I’m not sure that’s the wrong approach as part of the reason monitoring is set up is to allow data capture in the background and remove one more task from our daily workload.

Monitoring isn’t necessarily simple, however, and while I still debate the best way to do this in many organizations, I realize that monitoring isn’t necessarily something I want to build in house. There is enough work to just work with the data that other systems might output that I really want some other software in place that is built to perform monitoring for specific technologies. In reading about the complexity for the Stack Overflow monitoring systems, I realize that this can become very complex for a “set it up and let it run in the background” configuration.

The team at Stack Overflow built their own system for monitoring various systems, including SQL Server, but I think part of the mission of Stack Overflow was to build a system from scratch, which isn’t the job for most of us. Plenty of us have other tasks to deal with as a part of our job, and software development for monitoring or alerting or some other administrative task isn’t one of those jobs. I know I wouldn’t want to stop and think about data management and gathering, and more as a software process. If I’m a DBA, I want to just get the data and use it to ensure systems are running well.

Monitoring can be a way for us to proactively look for developing issues and mitigate them before clients know there is a problem. It’s important that a system is in place and handling data. It’s even more important that there is some alerting application in place as well to ensure that when something does start to go wrong, the DBAs are alerted early enough to prevent widespread problems.

If you read about all the thought and details of the Stack Overflow system, you quickly realize that there is a lot to consider when setting up the monitoring for your systems. I’d encourage you to think about what is important and ensure that you’ve got some way to gather and analyze that data. When something goes wrong, and something will go wrong, you’ll appreciate the time spent on the details of the monitoring system.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

I’m a believer in instrumentation and metrics being regularly gathered from your systems. Across the years I’ve built and bought monitoring software to use in various jobs, sometimes in layers where one piece of software uses data from another to provide insight about the way in which applications are performing. By gathering and analyzing this data, I can determine if more or less resources ought to be allocated to meet the demand.

No matter how you choose to gather this information, there are costs associated with the effort. Early in the lifecycle of an application, many developers and DBAs might just check to determine if the a system is up. As time passes and dependency increases on the system, there are additional needs to understand more about the system, such as storage, processor usage, and more. SQL Server has certainly matured in this respect as well, with more and more DMVs being added over time to allow more instrumentation of the platform.

One of the interesting things that happens when you monitor your systems is that there are various costs to your organization, some of them hidden.  Certainly purchasing or building software has a cost, but what about the other costs involved? As I work for vendor that sells this software, I’m always amazed how customers view costs. Most understand there are hardware costs, though surprisingly, a few don’t expect that.

There are other costs, some of which are outlined in the article linked above. There are plenty of time costs, especially associated with learning and configuring the software. In fact, this can be a significant cost. Most customers fail to account for time, and regularly become frustrated as the instrumentation never seems to meet their needs. Either it hasn’t been configured to meet your needs, or you haven’t learned to use the system well. There are also some people that find the opposite, with monitoring visualizations becoming a time sink, where administrators get lost in the analysis of data and the variety of ways to examine the information available.

It’s not just administrators, as not shortage of executives have wanted real time visualizations of metrics on systems, sometimes just standing there watching the metrics rise and fall to gain confidence in how well their systems are functioning.

There is another cost that many people often forget about as well. Any system that monitors another will impose a load to gather that data. That could be significant, especially if you haven’t tuned your software. There will always be some load, and I think it’s necessary to have information, but you ought to be sure the load isn’t too significant.

It’s important we monitor our systems. I would never build that system again, but rather buy some piece of software because the process is cumbersome and there are lots of choices for software that someone has spent time building. I’d rather learn to use their application well and let it help me do my job.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

I visited a doctor recently, and he told me a measurement he’d made. I asked if it was good or bad, and he said he had no idea. The value varied too much from person to person, and without values from the past, he couldn’t really evaluate the significance of it. He will be able to in the future, now that he has a value, but there’s nothing that can be done now. At this point, he has a baseline (of sorts) and can now start to judge how things change over time.

After that visit, I started thinking about Page Life Expectancy (PLE). PLE is one of those counters that so many DBAs look at early in their career. Often they’ve read guidance that they should worry once this is below 300, which isn’t true. There are calculations for this, but they are based on your system, and really, they’re a rough rule of thumb. Really you need to measure this for your system, so that you know what a the value often is and then worry when it dips.

To do that you need a baseline. You need to measure various metrics about your system over time so that you understand what’s a normal value. Plenty of experts, like Erin Stellato and Tim Radney have written about baselines, why they’re important, and what you might want to capture. In fact, we have quite a few articles on baselines at SQLServerCentral.

If that sounds like a lot of work to you, I agree. I’ve built systems in the past that captured metrics on my instances and stored the data. I wrote reports to view data, alerts to let me know when something is breaking (or broken), and maintenance that kept data storage under control. I essentially had to be both the software developer and operations staff for my systems. That works, but I’d try to avoid repeating that effort from now on. As Tim mentions in his piece, there are better ways to do this. There are products, such as SQL Monitor and SQL Sentry, that capture this data for you, that won’t have typos, mistakes, or holes in their operation.  Some will even show you the baseline visually to see if things are withing expected ranges.

The monitoring software does lots for you, though at a price. It’s tested, and it does all the gathering, storage, basic analysis and alerting in a way that allows you to spend time on actually fixing issues, tuning queries, and providing value for your organization. I think it’s worth the cost, since I know that my time is better spent on solving problems, not writing monitoring software. You may feel the same way or youj may not. You may prefer to write your own system, or you may not have a budget and be forced to build your own. Whichever route you go, make sure you set up a baseline. You’ll appreciate having one the next time your phone rings with a call that the server is slow.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.