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

In order to determine if you're experiencing external physical memory pressure, take a look at the
following counters:

Process —Working Set: This shows the size of recently referenced pages in each process's vir-
tual address space. Looking for the top memory consumers from this output will give you a
good idea of any applications outside SQL Server which are consuming lots of memory! Take a
look at the following counters to determine whether SQL Server has access to enough memory:

❑

❑ SQLServer:Buffer Manager — Total Pages: This Buffer Manager/Total Pages counter will expose

the total number of pages acquired by SQL Server.

❑ SQLServer:Buffer Manager — Target Pages: This Buffer Manager/Target Pages counter records

the ideal number of pages required for the SQL Server Buffer Pool.

If the target and total pages values are the same, SQL Server has sufficient memory. If the target is greater
than the total, it's likely that some external memory pressure (usually another Windows process) is
preventing SQL Server acquiring as much memory as it would like to operate.

Internal Memory Pressure
Internal memory pressure is typically caused by shrinking the buffer pool. In this situation the buffer
pool has reduced in size — perhaps as a result of a reduction in Max server memory sp_configure
setting leaving SQL Server with a buffer pool that is too small.

A further cause of internal memory pressure could be tasks that consume memory within the SQL Server
process space but outside the buffer pool. Extended stored procedures, COM objects, SQLCLR, and
linked servers are all examples of components that may consume process space and could cause pressure
on the buffer pool.

If you suspect internal memory pressure, you'll need to look at the sys.dm_os_memory_clerks DMV or
take a look at the Buffer Counts section of DBCC MEMORYSTATUS output to confirm whether this is the
case. The most useful values provided by the buffer count output section are the Committed and Target
memory values. The committed memory is the amount of memory that SQL Server has been able to
acquire. Target memory is the amount of memory SQL Server would like in order to operate effectively.
On a warm server (a server which has SQL Server running for some time), with adequate virtual memory,
you should expect to observe committed memory and target memory values that are approximately the
same. If you're seeing a difference between committed and target memory on a warm server, you should
investigate memory consumption further to see if there's a problem.

ConfirmingMemory Bottlenecks
In order that you satisfy yourself that you really do have a performance problem, there are a
number of performance counters you should evaluate once you've checked the standard Available Bytes,
Pages/second, and Paging File utilization counters. These counters will allow you to positively identify
SQL Server as being short of memory or otherwise:

SQLServer:Buffer Manager — Page Life Expectancy: The Page Life Expectancy counter within
the Buffer Manager refers to the duration in seconds that a data page is likely to
reside in the buffer pool. The longer the page life expectancy, the healthier the server looks
from a memory perspective. A server suffering from memory pressure will typically see page
life expectancy values of 300 seconds or below. If you find this, you'll have reasonable
grounds to suspect a low memory condition. Similarly, if during monitoring you observe any

❑