You should periodically monitor the computer running Microsoft® SQL Server™ to confirm that memory usage is within normal ranges and that no processes, including SQL Server, are being starved of or consuming too much memory.
To monitor for a low-memory condition, start with the following object counters:
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 paging.
A low rate of paging (and hence page faults) is normal, even if the computer has plenty of available memory. The Microsoft Windows NT® Virtual Memory Manager (VMM) steals pages from SQL Server and other processes as it trims the working-set sizes of those processes, causing page faults. To determine if SQL Server rather than another process is causing excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.
For more information about resolving excessive paging, see your Windows NT documentation.
By default, SQL Server changes its memory requirements dynamically, based on available system resources. If SQL Server needs more memory, it queries the operating system to determine if free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, dynamically using memory can be overridden using the min server memory, max server memory, and set working set size server configuration options. For more information, see Server Memory Options.
To monitor the amount memory being used by SQL Server, examine the following performance counters:
The Working Set counter shows the amount of memory used by a process. If this number is consistently below the amount of memory SQL Server is configured to use (set by the min server memory and max server memory server options), SQL Server is configured for more memory than it needs. Otherwise, fix the size of the working set using the set working set size server option. For more information, see set working set size Option.
The Buffer Cache Hit Ratio counter is application specific; however, a rate of 90 percent or higher is desirable. Add more memory until the value is consistently greater than 90 percent, indicating that more than 90 percent of all requests for data were satisfied from the data cache.
If the value for the Free Buffers counter is consistently low, it may indicate that more memory is required.
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 is required.