Isolating Memory Used by SQL Server
By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources. If SQL Server needs more memory, it queries the operating system to determine whether 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, you can override the option to dynamically use memory by using the min server memory, and max server memory server configuration options. For more information, see Server Memory Options.
To monitor the amount of memory that SQL Server uses, examine the following performance counters:
-
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 is required.