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

receive a problem report by phone, e-mail, or a trouble-ticketing tool, the scope of the problem is
wide. The problemmay be client side, network-related, or server-related. It also could be hardware-
or software-related. Or it could have to do with the operating system, infrastructure (SQL Server,
IIS, and so on), or application.

Much like a doctor arriving at the scene of an accident, your first step should be an initial assessment of
the situation to get a feel for the scale of the problem, severity, and priorities. Some aspects will be implicit
in the problem statement; other aspects will need more data before a proper diagnosis can be made.
Essentially, in the early stages of fault-finding you should be looking to rule out software or hardware
components that could have caused or contributed to the performance problem.

System Monitor can equip you with some powerful information on which you can base decisions about
how and whether to proceed with troubleshooting. Accurate identification of the likely area of the
problem at this stage can drastically reduce the time to resolution for your users. Naturally, there are
many situations where System Monitor can't provide sufficient granularity of information or can't reveal
particulars of application behavior. However, there are many other tools (especially for SQL Server) that
do this job really well.

When Should I Use System Monitor?
Any time you deploy a new server or service or make a significant configuration or application change,
you should use SystemMonitor. Although this may sound time consuming, cumbersome, and unrealistic,
it need not be this way and having some form of relevant performance baseline to which you refer during
problem investigation could save hours, days, or weeks in terms of root cause analysis. Organizations in
possession of up-to-date performance benchmark data for their key systems typically experience a lower
meantime to resolution for performance-related faults in their environments.

Additionally, how do you know what sort of utilization your servers have? How can you tell whether
they have spare CPU cycles or if they may require additional processors next month? SystemMonitor can
be used to create a lightweight trace to provide powerful data for capacity planning, usage monitoring,
and trend analysis.

Performance Monitor Overview
Within System Monitor in Windows Server 2000 and 2003, one of the most useful components is known
as Performance Monitor , which exposes around 50 performance objects and 350 performance counters.
Each performance object provides access to a number of performance counters which give real time
access to system resources. The performance counters and objects are populated from the Windows
registry and using Performance Library DLLs. Additionally, each application installed on a server may
add its own performance objects. For example, a single instance of SQL Server will add more than 30 of
its own performance objects, each object with numerous counters.

Common tasks you can use Performance Monitor for include:

❑ Collect real-time performance data from your server

❑ Log performance data over an extended timeframe