Professional Microsoft® SQL Server® 2008 Administration

13.1. The Goal of Monitoring

The goal of monitoring databases is to see what's going on inside SQL Server — namely, how effectively SQL Server is using the server resources (CPU, Memory, I/O). You want this information so that you can see how well the system is performing. Capturing this data over time enables you to build a profile of what the system normally looks like: How much of what resource do you use for each part of the system's working cycle? From the data collected over time you can start to build a baseline of "normal" activity. That baseline enables you to identify abnormal activities that might lead to issues if left unchecked.

Abnormal activity could be an increase in a specific table's growth rate, a change in replication throughput, or a query or job taking longer than usual or using more of a scarce server resource than you expected. Identifying these anomalies before they become an issue that causes your users to call and complain makes for a much easier life. Using this data, you can identify what might be about to break, or where changes need to be made to rectify the root cause before the problem becomes entrenched.

Sometimes this monitoring is related to performance issues such as slow-running queries or deadlocks, but in many cases the data will point to something that you can change to avoid problems in the future.

This philosophy is about the equivalent of "an apple a day keeps the doctor away" — preventative medicine for your SQL Server.

13.1.1. Determining ...