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

Best Practice
Secondly, as a best practice you should always look to separate system databases from
user databases. This will minimize disk contention between these databases. There's
one specific database, tempdb, which is responsible for instance-wide temporary stor-
age. The demand on tempdb varies enormously depending on the type of activity on
the server and the features used within SQL Server. For example, certain DBCC com-
mands will use database snapshots, and online index rebuilds can also consume space
within tempdb in addition to the normal sorts and joins that might take place.
Identifying CPU Bottlenecks
CPU time is one of the most expensive and precious server resources, and overall system performance
is often very sensitive to CPU utilization. For example, a high CPU condition (even for a short period)
can often be felt by users. SQL Server itself can often create high CPU conditions through execution of
unqualified queries, hash or merge joins, or during query plan compilation/recompilation. Typically
these require SQL Profiler traces or use of DMVs to identify/resolve. SystemMonitor can be useful in the
initial identification.
It's worth clarifying that everything discussed in this section refers to logical processors, which are
processors as exposed to Windows. These may exist physically as Hyper Threaded (HT) or multi-core
processors, and both will expose themselves to Windows (and therefore to SQL Server) as logical
processors. In this way a four-CPU, dual-core system with hyper threading enabled will appear as
16 logical processors. It's not actually terribly easy for SQL Server or Windows to differentiate between
logical and physical processors.
There are two performance monitoring objects that are quite distinct but often confused: process and
processor. The process object refers to windows processes; there is an instance of this counter for every
Windows process, such as sqlservr.exe and notepad.exe . The processor object has an instance for each
logical processor — the actual CPUs themselves.
Similar to the counters you've seen in the disk section, you'll be able to get a fairly good understanding of
the state of health of the processors by adding the % Total Processor time from the Processor object. This
counter measures percent time the processor is busy servicing requests, which includes kernel mode and
application mode requests across all processors. If this is consistently above 80 percent, you know you're
seeing some CPU pressure, and you'll need to add some further counters to identify what the source is
and how the pressure is occurring.
The next step is to break down the Total Processor time to understand the composition of the CPU time.
(For example, is the CPU being heavily loaded by a kernel mode or a user mode process?) You should
examine the % Privileged Time and % User Time within the Processor object to determine where the CPU
demand originates. On a healthy server you should expect to see fairly minimal privilege time. One of
the side-effects of low memory conditions can be high kernel mode CPU, and the processors are involved
in handling paging.