Beginning SQL Server™ 2005 Administration

Chapter 10. Monitoring SQL Server for Performance

One of the primary responsibilities of the database administrator is the ongoing monitoring of SQL Server performance. Much of this monitoring can be automated, but for the most part, the monitoring results must be interpreted and acted upon 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 worse 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 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 be all that mysterious. A good understanding of the tools, as well as a familiarity with the different objects requiring monitoring, ...