Professional Microsoft® SQL Server® 2008 Administration

13.4. Performance Monitor

Performance Monitor is an important tool because not only does it enable you to know how SQL Server is performing, it is also the tool that indicates how Windows is performing. Performance Monitor provides a huge set of counters. Probably no one understands all of them, so don't be daunted.

This section is not about how to use Performance Monitor (although later in this section you will learn about two very valuable tools, Logman, and Relog, that make using Performance Monitor a lot easier in a production environment). The focus in this chapter is on how you can use the capabilities of this tool to diagnose performance problems in your system. For general information about using Performance Monitor, look at the Vista or Windows Server 2008 documentation.

As mentioned, you need to monitor three server resources:

• CPU

• Memory

• I/O (primarily disk I/O, but in some cases network as well)

Monitor these key counters over a "typical" 24-hour period. Pick a typical business day, not a weekend or holiday, so you get an accurate picture of what's happening. You should also take into account any specific knowledge of your business and monitor for peaks of activity such as end of week, end of month, or other special activities.

13.4.1. CPU Resource Counters

Several counters show the state of the available CPU resources. Bottlenecks due to CPU resource shortages are frequently caused by problems such as more users than expected, one or more users running very expensive ...