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

terms of resource utilization (that is, there isn't one resource that is completely exhausted while others
have spare capacity).
Bottlenecks are often grouped into three areas: Memory, Disk, and CPU. These are listed in order of likely
occurrence with SQL Server implementations. SQL Server is dependent on all of these components to
perform well. However, it is especially reliant on memory to hold data pages that have been read from or
are waiting to be written to disk. A lack of memory can severely impact performance as SQL Server may
be forced to read data pages from disk (instead of memory, which is much faster). In some low-memory
situations, SQL Server may be paged to disk by Windows. Again, this will cause performance to suffer as
disk access is much slower than memory access.
Almost as important as memory, a healthy disk subsystem is required to allow SQL Server to read
and write data pages as quickly as possible. Poor disk throughput can cause delays in data being read
or written and can have a direct impact on user experience.
There are times when SQL Server will be asked to calculate sums, re-order data, or join tables (plus many
other operations provided by SQL Server) that may require CPU cycles to action. In these situations,
availability and responsiveness of the CPUs will be important and may impact user experience if these
aren't readily available. For this reason making sure there are sufficiently free CPU resources is key to
ensuring good response times from SQL Server.
Memory Bottlenecks
In so many performance problem scenarios, it seems as though memory is an easy target, blamed by
system administrators the world over. There are a small handful of performance counters that will allow
you to correlate performance data to determine whether memory is a bottleneck in your environment.
SQL Server performance and stability are entirely dependent on sufficient available memory. A memory
shortage often results in Windows serving the virtual address space (VAS) from the paging file, which
will usually have an immediate and very apparent impact on performance. The first stage is to identify
that you are actually seeing a memory bottleneck. After you've established a memory problem, you can
then work to discover whether you have internal or external memory pressure.
Understanding the nature and characteristics of memory problems will help with early identification of
these conditions and help you make a decision about the best route to resolution.
Types ofMemoryBottlenecks
Memory problems experienced on SQL Server machines can typically be categorized in two ways: VAS
or physical. If memory pressure is caused by the process itself, it's usually considered internal memory
pressure. Physical memory pressure is usually caused by other Windows processes.
External Memory Pressure
External memory pressure can occur on servers running SQL Server alongside other applications that
may be competing for memory. From a SQL Server perspective, you'll see Windows signal to the
Resource Monitor within SQL Server Operating System (SQLOS) a request for SQL to reduce its
committed memory. This will cause SQL to recalculate its target commit level and if required reduce its
commit level (although this won't ever reduce to less than the sp_configure min server memory setting).