Monitoring Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 18

Why are these areas so important? They're the major hardware components of a server, and each
component is involved in servicing user requests. The timely performance of these components is
directly related to overall perceived application performance. Therefore, a problem with one or more
of these four areas is likely to result in user complaints. SQL Server relies heavily on CPU performance,
available memory, and disk throughput, whereas the client performance depends heavily on network
performance. Any processor which is consistently busy for 90 percent of the time or more will result in
the buildup of a queue of work requests, and performance will likely suffer. Additionally, SQL Server
can be very demanding of memory and performance can really suffer if physical memory becomes
exhausted, when typically Windows is forced to use the page file. Disk is almost certainly the slowest
component, inherently because it's mechanical. SQL Server's necessity to retrieve data from disk often
means any delays at the disk Input/Output (I/O) will impact overall performance. Finally, your database
could be performing perfectly well, but if there's latency in the network or if packet loss is high, forcing
retransmissions, all the good work done until this point is negated by poor performance when talking to
the requestor.

Getting Started with System Monitor
System Monitor is provided as part of the operating system to allow system administrators to get
visibility of server workload and resource utilization. The tool can be useful for providing data helpful in
troubleshooting problems, trend analysis, and system capacity planning.

This section is intended to help familiarize you with the tool, the information it can provide, and how
you can present this information natively within System Monitor. The Performance Console Microsoft
Management Console (MMC) snap-in includes two key tools for monitoring server performance:

❑ System Monitor

❑ Performance Logs and Alerts

The first component, System Monitor (commonly known by the name of the executable perfmon.exe ,
and generally what is known as Perfmon), is the realtime monitoring tool. Performance Logs and Alerts
contains three sub-components: Counter Logs, Trace Logs, and Alerts.

Throughout this chapter Performance Monitor and System Monitor are used interchangeably as
terms referring to System Monitor (rather than Performance Console), since these names are more
commonly used.

Begin by launching the Performance Console from Administrative Tools (the same tool is known as
Reliability and Performance Monitor in Windows Vista), or add the Performance snap-in to MMC. The
figures shown in this chapter were created using Reliability and Performance Monitor in Windows Vista,
so the interface may differ slightly in appearance, although functionally they're the same. Note that
some counters are present by default; you'll see a graph beginning to form as values are sampled for the
default counters (see Figure 2-1).