Getting to Know Your Servers

It goes without saying, and any good DBA will back me up, that to maintain service level and keep servers and databases available, you need to become familiar with each server, the applications and services running on that server, and the resources they need and use. I am not saying that you have to strike up an intimate relationship with a database server, but it is insufficient to just maintain a subjective feel for how a server is “supposed” to operate. All the monitoring and diagnostics we have covered in this chapter will enable you to quickly reverse a bad situation-the shutting down of mission-critical applications, server attacks, system failures, and so on. System monitoring is thus a critical job that you need to be continuously engaged in.

The first task at hand is to generate data, because otherwise you basically have nothing to compare against. Only after a considerable amount of data has been collected will you have the data with which to make comparisons. Data collection should take place over several weeks, possibly even months, and then, after assessing that data, you will be able to establish baselines against which to base future observations and decision making. This is an important step for any new project involving the operation of SQL Server, especially SQL Server machines engaged in replication in widely distributed data centers.

A new database underpinning a Web application is a good example of a system that needs to be gathering data from the moment it is launched. Unless you have the data for your baseline, how can you say that the system is running “normally?” You first have to determine the baseline before you can make judgment calls. What’s considered normal for some machines may be aggressive for others. Other machines may sit idle for most of their lives, and that could be because they are not getting traffic or are endowed with resources that would drive a battleship.

Note 

If a server is starting to become unstable, collecting data and setting alerts like crazy might exacerbate the problem. Your first option will be to try and stabilize the system; only then should you set up monitoring and alerts to determine the cause of the instability in the future. In the event of an unresponsive system, you might have to run Task Manager and attempt to end the tasks, applications, and processes that are causing the problems. Fortunately, such events are unlikely to be associated with SQL Server.

With a baseline in hand, you will quickly be alerted to performance that is out of the ordinary. For example, if you notice that at night the database server’s responsiveness begins to diminish suddenly, you may find that the server has received a query that is consuming considerable processing bandwidth…the result of a bug or a trigger that has 2,000 lines of cursor code in it. A memory counter would be the object that alerts you when this is taking place.

Your information should also reflect various points in system performance. You should note what constitutes “normal” on a server. For example, at a client we noticed that an Analysis Server was low on RAM and had begun to page out to disk excessively When we asked the MIS about this, he advised it was “normal” for this time of the day because of replication services that fire. So we moved the drill-downs to a later part of the day and later moved the services to a new server.

It is thus important to note periods of low use, average use, and high or peak use. Systems that provide real-time communications are a good example of servers that should be monitored continuously for this type of information.

Tip 

Make baseline performance information available within an arm’s reach of your server. This can be in the form of clipboards or journals into which you can paste data. This will allow other system operators to look up a server and determine what might be considered normal.

Do not expect to obtain any instant insight into system performance either, because the baselines you develop will establish typical values to expect when your system is not experiencing problems.