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

You can confirm an application problem if you see the User Time line on the line graph in SystemMonitor
almost shadow Total Processor Time. This indicates that there is a user mode process consuming almost
all CPU cycles. Should this happen, you'll need to work through the processes to determine which is
consuming the CPU time. You should add instances of the % Processor Time counter in the Process
object for each process, for example, sqlservr.exe . Once you've identified the culprit, it will be a case of
trying to determine why demand is so high. There are a number of DMVs which expose this information
very readily. A lot of useful DMV information can be found in Chapters 4, 12, 13, and 14.

Using System Monitor Proactively
A baseline is invaluable. Understanding how your systems behave while under normal load will assist
when troubleshooting and while making decisions around capacity planning and resource utilization. It
can be really useful to authoritatively answer questions around typical CPU utilization, normal memory
consumption, and average disk performance. Using System Monitor proactively can help with each
of these.

Regularly running System Monitor to obtain system baseline data can be readily used for trend
analysis and capacity planning. Server overhead can be minimized by running SystemMonitor remotely,
and reducing sample frequency to between 15 and 30 minutes. Allowing the log to run continuously
over 24 hours (or longer) will provide a useful system overview which should be retained for future
reference.

Once you've done this, the next time you receive a problem report from a user that indicates a potential
performance issue, you'll be in a really strong position to run System Monitor on your production server
and alongside display your most recently saved baseline. A comparison will be easy, and while server
load won't necessarily be identical, you'll be able to compare averages and very quickly get a feel for
general server performance and any likely problem areas.

Running System Monitor on 64-bit Systems
If you're using x 64 Windows and x 64 SQL Server you shouldn't have any issues. System Monitor will
by default have the correct counters loaded and available for you to view and the same applies to
IA64 Windows and IA64 SQL Server deployments. However, if you're using SQL Server in a Windows-
On-Windows (WOW) mode on x 64 (which is to say that x 64 Windows is emulating a 32-bit environment
to facilitate you running an x 86 instance of SQL Server), then this section will definitely be relevant.

This situation ( x 86 SQL running on x 64 Windows) can be particularly useful for multi-instance test
environments or at times when applications specifically require 32-bit SQL Server. When you start System
Monitor on the x 64 host, you'll find that none of the counters relating to the 32-bit SQL Server instance are
available. This is because on an x 64 host you'll be running an x 64 version of System Monitor by default.
Monitoring the 32-bit SQL Server instance in the scenario will require running a 32-bit instance of System
Monitor, and this can be achieved by running the following from the Start ➪ Run box:

mmc /32 perfmon.msc