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

❑ Collect and compare performance data from different servers

❑ View a graphic representation of server performance

❑ Save and export performance data

❑ Measure the performance impact of system and/or software changes

❑ Send performance alerts based on performance thresholds

In situations where you're experiencing performance or reliability issues, Performance Monitor can
reveal the source of the problem very quickly, if the problem is simple. On many occasions, however,
Performance Monitor will provide you with a better understanding of the scope and nature of a problem,
but you'll likely have to use other specific tools to proceed with troubleshooting. It's quite likely that
your initial troubleshooting Performance Monitor will lead you to require more detailed diagnosis and
frequently this will require tools such as SQL Server Profiler or a network monitoring application, such
as Windows Network Monitor or Ethereal.

Performance troubleshooting typically requires an iterative approach, starting by qualifying the initial
problem statement and continuing by performing a detailed fault diagnosis and analysis until you reach
aconclusion.Asafirststep,yourobjectiveshouldbe to determine whether a problem exists at all, and
you want to be sure that what you're monitoring is relevant to the symptoms expressed in the problem
statement. This qualification process will often begin with the error message and the attempt to identify
a corresponding error on the server at the time the user/service experienced the reported error. In the
case of web servers, this can be as straightforward as locating a matching GET request in a log file from
the client IP address. Other times, depending on the nature of the problem, it can be difficult to find
anything that matches on the server. It can be very useful to initially familiarize yourself with the
Windows Application and System event logs. Assuming these are adequately sized, they should
contain sufficient history to give you some level of confidence of the server's availability (uptime of
services and so on) and will provide you with a feel for the health of the server. When examining the
Application and System event logs, it's a good idea to isolate any entries flagged with Warning or
Error types and try to eliminate them as potential causes (unless they're clearly unrelated). It's always
surprising the number of server engineers who overlook clues to the root cause of their issues by
overlooking the Application and System logs or by dismissing entries that are subsequently proven
to be important.

Once any warning and errors in the Windows Application and System logs have been resolved or
dismissed as unrelated to the problem being investigated, it would be useful to begin performance
monitoring to determine the state and current workload of the server to identify any bottlenecks. When
considering likely candidates for performance issues, you should consider the following four areas for
initial monitoring:

❑ Memory

❑ Disk

❑ Processor

❑ Network

The objective of using Performance Monitor is to provide you with a feel for the health and workload of
the server. By selecting between 5 and 10 counters that cover the fundamental areas for initial monitoring
as listed above, you'll gain a quick and meaningful insight into the real-time state of the server.