Magical Database Monitoring

This weeks photo is from Jersey City,NJ looking across the Hudson River

(Scene One: Office of the DBA)

Enter: Friendly SQL Adventurer ready to help you be more pro-active in your management of your enormous SQL Server landscape.

Has your company invested in a database monitoring tool? Something that will give you "live" performance information and alert you when specified thresholds have been exceeded? Something that is watching all of your SQL Agent jobs and will let you know if one is running too long or has failed? Something that allows you to configure all kinds of different profiles and classes of servers and levels of service?

You have one of these - wonderful!

Have you configured it for your environment?

Yes? Good, let's take a look…

Umm..

Okay, I see you have adjusted the performance alert thresholds….

So, you really only want to be paged when the CPU usage gets over 98%?

Oh, you got tired of getting an alert for every little spike.

Did you know that you can configure this to only alert you when the threshold is exceeded for a specified duration?

No? Okay, well you can configure this to alert you when CPU usage gets over 80% for 5 min.

Yes, that would be better.

So, I see you have divided your servers up into Dev, Test and Production. That's great.

I see they are all using the same performance profile.

You didn’t know you could create different profiles? Okay here let me show you….

You say you got this great tool and set it all up and added all your SQL Servers, but it alerted and paged you all the time so you adjusted some of the settings.

Kind of looks like you adjusted them so that they would never go off unless the building was on fire.

Ah, yes. I don't like to be paged unnecessarily either.

Let's sit down and do a little analysis of your environment ...

Three Part Harmony

Before you even start to look at monitoring tools you need to know what you want to know and when you want to know it.

The first step to monitoring your environment is creating a detailed list of what you need to know.  For which servers this information is important?  What is the priority when everything is going wrong at once? Other environmental factors will need to be identified such as the lead time to allocate more disk space.

For example, remaining physical disk space for the production backup storage location is very important or your backups will start to fail. You'll want to be notified when it is getting low with enough lead time that you can do something about it.  If it takes six months to get more disk space then you need to know six months before your backup jobs start to fail that you need more space.

Generally, performance problems are more important to production environments then to development environments. Adjust your thresholds accordingly.

You get the idea.

I believe that you should monitor all of your environments, even development.  The dev SQL Servers are important for the developers to be able to do their work - they are production to the developers. If they are not performing well you will need to handle it.  It is important to monitor test servers too.  You will be able to catch poorly performing components or badly configured SQL Servers before they get to production.  If your test environment is supposed to be just like production that should include the monitoring.  This will allow you to take the performance you saw in test and compare it to what is being observed in production.

Take the time to identify each SQL Server - server, instance and database - and detail how it should be monitored, what should be monitored, the urgency, and who will be responsible for responding to issues.

Now, choose a tool. And here is the important bit…

Learn how to use it.

All of the available tools do a lot of things, but not by magic. You need to make the investment in learning how to fully utilize the tools you have chosen.  I know we all want to find a monitoring tool that will just be magical and solve all of our monitoring and alerting problems with little or no effort or configuration.

In Tune

Any monitoring tool will come with a set of default monitoring parameters. Most tools you can just install, let them discover your SQL Servers and let 'er rip!  Your inbox will be full in no time. 

Go to that work you did before you purchased the tool and configure it to work for you and proactively alert to your specifications. 

Monitoring tools are VERY worth the investment. Realize that the investment is not just in dollars spent to acquire the tool but in analyzing your environment to identify HOW and WHAT to monitor and investing in developing the skills to make full use of the tools you have chosen.

By thoughtfully designing a monitoring plan, becoming skilled with your chosen tool set, and configuring for your environment you will have created a monitoring environment that has solved all of your alerting and monitoring problems - just like magic.