How to Monitor Memory Usage in Microsoft SQL server
If you have a low-memory condition, you may want to monitor
• Memory: Available Bytes
• Memory: Pages/sec
Note: The Available Bytes counter indicates how many bytes of memory are
currently available for use by processes. The Pages/sec counter indicates
the number of pages that either were retrieved from disk due to hard page
faults or written to disk to free space in the working set due to page
faults. Low values for the Available Bytes counter can indicate that there
is an overall shortage of memory on the computer or that an application is
not releasing memory. A high rate for the Pages/sec counter could indicate
excessive paging. Monitor the Memory:
Page Faults/sec counter to make sure that the disk activity is not caused by
A low rate of paging (and hence page faults) is typical, even if the
computer has plenty of available memory. The Microsoft Windows Virtual
Memory Manager (VMM) takes pages from SQL Server and other processes as it
trims the working-set sizes of those processes. This VMM activity tends to
cause page faults. To determine whether SQL Server or another process is the
cause of excessive paging, monitor the Process: Page Faults/sec counter for
the SQL Server process instance.
To monitor Memory Used by SQL Server, check the following performance
• Process: Working Set
• SQL Server: Buffer Manager: Buffer Cache Hit Ratio
• SQL Server: Buffer Manager: Total Pages
• SQL Server: Memory Manager: Total Server Memory (KB)
The Working Set counter shows the amount of memory that is used by a
process. If this number is consistently below the amount of memory that is
set by the min server memory and max server memory server options, SQL
Server is configured to use too much memory.
The Buffer Cache Hit Ratio counter is specific to an application. However, a
rate of 90 percent or higher is desirable. Add more memory until the value
is consistently greater than 90 percent. A value greater than 90 percent
indicates that more than 90 percent of all requests for data were satisfied
from the data cache.
If the Total Server Memory (KB) counter is consistently high compared to the
amount of physical memory in the computer, it may indicate that more memory
Note: By default, SQL Server changes its memory requirements dynamically.
Whenever SQL Server needs more memory, it takes available physical memory.
If SQL Server does not need the memory currently allocated to it, it
releases the memory to the operating system. You may adjust how many the
memory SQL will use by enable AWE.
To monitor above accounts, run perfmon and add those accounts as shown.
Post your questions, comments, feedbacks and suggestions
Contact a consultant