Beginning, Microsoft® SQL Server® 2008 Administration

Chapter 10. Monitoring SQL Server

One of the primary responsibilities of the database administrator is the ongoing monitoring of SQL Server. Monitoring is done for a variety of reasons, including performance, storage, security, and standards compliance. Much of this monitoring can be automated, but for the most part, the monitoring results must be interpreted and acted on in a systematic approach by the DBA. The monitoring job never ends, and it can become quite complex. Knowing what to monitor, when to monitor, and what constitutes acceptable and unacceptable behavior can become a full-time job. Making things even more challenging is the fact that each SQL Server installation is different, making a global recommendation about what indicators identify unacceptable and acceptable performance very difficult.

This chapter explains the various tools used to monitor SQL Server and provides guidelines on how to use these tools to identify potential security problems and areas for optimization. Monitoring SQL Server can be a challenging process. SQL Server interacts heavily with every operating system subsystem. Some applications rely heavily on RAM, whereas others are CPU- or disk-intensive. SQL Server can be all three at the same time. SQL Server can also be very network-intensive, especially with distributed applications, replication, or database mirroring. Many database administrators find the whole process of monitoring and optimizing arcane and nebulous. However, it doesn't have to ...