Summary for Accidental DBA Book / Chapter 4: Memory Management

Chapter 4: Memory Management

• SQLOS is a self-tuning features in SQL Server, which is a dedicated thread that monitors memory notifications issued by the Windows operating system, to inform other applications of the status of memory usage in the operating system. Two such memory notifications are set by the Windows OS: 
• memory high lets SQL Server know that it can grow its working set and use additional memory 
• memory low lets SQL Server know that the operating system is under memory pressure and that SQL should try to reduce its working set to return memory to the operating system … P129.

32-bit Virtual Address Space limitations

• The Windows OS runs every process, including the SQL Server process, in its own dedicated area of virtual memory, known as the Virtual Address Space (VAS). The VAS is divided into two regions; kernel mode (or system) space and user mode (or application) space. The kernel mode VAS is used by the OS, for mapping various system data structures such as the file cache, Paged and Non-Page pools (discussed briefly later). The user mode VAS is used to map memory for the currently-executing application process (in our case, SQL Server) … P132.
• VAS tuning: In Windows Server 2000, two switches can be added to the boot.ini file to change the default 50:50 split of the VAS. The /3GB switch in the boot.ini changes the allocation so that 3 GB is allocated to the user mode VAS and 1 GB is allocated to kernel mode. There is trade-off with VAS tuning is that you end up with up to 1 GB less space for kernel mode VAS, and this can have significant consequences, so care must be taken when using VAS tuning … P136.
• On 32-bit servers on which more than 4 GB of RAM is installed, SQL Server can utilize the memory over 4 GB through the use of Address Windowing Extensions (AWE). In order for SQL Server to be able to use AWE to allocate memory, Physical Address Extensions (PAE) must first be enabled on the server. Next, the 'awe enabled' sp_configure option must be set inside of SQL Server, and the SQL Server service account must have the Lock Pages in Memory user right … P138.

Memory configuration options with 64-bit SQL Server

• One of the benefits of 64-bit systems is that the VAS limits are substantially higher, means that 64-bit SQL Server instances do not require any additional configuration in order to allocate memory over 4 GB; the AWE enabled option has no application for these instances … P141.
• SQL Server offers two instance-level settings that can be used to control how memory is allocated to, and removed from, the buffer pool:
• The min server memory option specifies the minimum size to which SQL Server can shrink the buffer pool when under memory pressure; it does not specify the minimum amount of memory that SQL Server will initially allocate.
• The max server memory option specifies the maximum amount of memory that SQL Server can use for the buffer pool, which is primarily used for caching data pages in memory … P143.
• It is recommended that the min server memory configuration be set lower than the max server memory configuration by a couple of gigabytes of memory, to allow SQL Server to resize the buffer pool as needed to respond to OS low memory notifications … P144.
• As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. Then monitor the Memory\Available Mbytes performance counter on the server (along, possibly, with 145 counters like Total Server Memory and Target Server Memory, covered later) to determine the value for max server memory that leaves at least 150–300 MB of memory available at all times for the memory requirements of non-buffer pool and non-SQL memory allocations, for Windows and other applications … P145.
• If SQL Server experiences a working set trim or memory gets paged out by the OS because of memory pressure, SQL will write a message in the error log as follows: 
• A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 16484, committed (KB): 6239692, memory utilization: 0%.
• If this message is frequently seen, it indicates there is a problem with SQL getting paged out, and to prevent this, the Lock Pages in Memory privilege can be assigned to the SQL Server service account. A problem appears after running SQL Server using Lock Pages in Memory is that the information returned by Task Manager no longer reflects the total memory use by the sqlservr.exe process, and to know that the SQL SQL Server:Memory Manager\Total Server Memory performance counter should be used … P146-147.

Diagnosing Memory Pressure

• Buffer pool churn: is the repetitive cycle of flushing data pages from the buffer pool, to make room for other pages, then read it back into the buffer pool. This happens when SQL Server is forced to operate with an insufficient memory, as a result, higher physical I/O is needed to bring data pages from disk into the buffer pool as they are requested, and performance will degrade … P149.
• To tell if SQL Server is under memory pressure and needs more memory, review the following counters:
• The SQL Server:Buffer Manager\Buffer Cache Hit Ratio counter shows how often SQL Server gets data from Memory (buffer cache) as opposed to disk. Generally this counter should be greater than 95% for OLTP systems and greater than 90% for OLAP systems, to say that SQL Server is working normally, but this counter alone doesn’t tell whether or not SQL Server is under memory pressure … P150.
• The SQL Server:Buffer Manager\Page Life Expectancy (PLE) counter provides the time in seconds that a page exists in cache before being aged out to allow reuse of the cache space. Generally the value of this counter should be > (total memory in GB/4) *300 … P151.
• The SQL Server:Buffer Manager\Free Pages counter reflects the total number of free pages that exist for the SQL Server buffer pool, allowing for immediate allocations by an executing request without having to release additional pages from cache to satisfy the request. If the PLE has a value that considered low or Free Pages has a value of zero, and the system is also experiencing Free List Stalls at the same time, then that is a sure sign the instance is under memory pressure and could benefit from additional memory … P152.
• SQL Server:Buffer Manager\Free List Stalls occur whenever a request has to wait for a free page in the buffer pool. If the number of stalls exceeds zero frequently or consistently over a period of time, this is a sign of memory pressure … P152.
• The SQL Server:Buffer Manager\Lazy Writes/sec counter reflects the number of buffer pages that have been flushed by the Lazy Writer process, outside of a normal checkpoint operation, allowing the buffer to be reused for other pages. If you observe Lazy Writes occurring in conjunction with a low PLE, a low number of free pages, and the occurrence of Free List Stalls, this is a sign that the workload is exceeding the amount of memory that is available to the buffer pool, and additional memory needs to be added to the server … P152.
• SQL Server:Memory Manager\Target Server Memory (KB): total amount of memory that has been allocated by SQL Server. SQL Server:Memory Manager\Total Server Memory (KB): the amount of memory that SQL Server wants to commit. If Target Server Memory < Total Server Memory then the SQL Server process wants to commit more memory than is available on the server, which can be a sign of memory pressure …P153.
• SQL Server:Memory Manager\Memory Grants Outstanding : This counter measures the total number of processes that have successfully acquired a workspace memory grant. Low values for this counter, under periods of high user activity or heavy workload, may be a sign of memory pressure, especially if there are a high number of Memory Grants Pending … P153.
• SQL Server:Memory Manager\Memory Grants Pending: this counter measures the total number of processes that are waiting for a workspace memory grant. If this value is non-zero, it is a sign that additional memory needs to be added to the server … P154.
• There is also some information regarding memory-related waits and non-buffer pool memory allocations and so on, that can be extracted from the DMVs, such as the sys.dm_os_memory_* objects in the Operating System-related DMVs, or the sys.dm_exec_query_memory_grants DMV. A list of those counters with brief description in p154. However the writer prefers to use the counters in the previous points, in diagnosing memory pressure rather than those DMVs, as he finds the information in those DMVs header to analyze.

Common Memory-Related Problems

• Paging problems (discussed in point P146-147). The common causes are the following:
• Incorrect settings for the max server memory sp_configure option, when Lock Pages in Memory is not being used
• A large system cache in Windows caused by caching of non-buffered I/O operations such as file copy operations
• Hardware driver issues that result in memory leaks or excessive memory allocations by the driver … P156.
• OS instability due to using Lock Pages in Memory and leaving the default configuration for the max server memory sp_configure option, which means all of the memory available on the server. This leads that when OS is badly under memory pressure it can't page out or trim the SQL Server working set, this leaves the OS at the mercy of SQL Server to respond to the memory pressure fast enough to prevent the Windows OS from crashing. The same case if an inappropriately high value has been set for max server memory for the instance. For this reason, it is critical that when Lock Pages in Memory is set for a SQL Server instance, the max server memory configuration option should be set low enough to ensure that the Windows OS never gets into memory pressure … P157.
• Error 701 and FAILED_VIRTUAL_RESERVE, Inefficient memory utilization by the SQLCLR assembly, or limitations of the VAS that is available for allocation by the SQLCLR assembly. When this error is encountered on 32-bit installations of SQL Server upgrade the server to a 64-bit. However, if the SQLCLR code excessively utilizes memory by using objects like a DataSet, even the upgrade will not resolve the problem, in this case we need to run the SQLCLR assembly outside the SQL Server (in WinForms for example), and profile its memory usage. If upgrade is not possible one potential remediation is to use the –g startup parameter, to increase the size of the MemToLeave VAS Reservation, providing additional VAS to SQLCLR for memory allocations …P157-158.
• Over-provisioned virtual machines: In SQL Server VM, setting the correct min server memory configuration inside of SQL Server can allow for partial ballooning to occur, while ensuring that SQL Server continues to allocate the buffer pool memory needed … P160.

• It is recommended that min server memory be configured for each instance, to guarantee a minimum amount of memory to the buffer pool if the Windows OS sets the low memory notification, and the SQL Server instances reduce their memory allocations in response. If the min server memory configuration option is not set, a single instance of SQL Server may voluntarily reduce its memory usage to the point that it experiences performance degradation. Setting min server memory for all of the instances will prevent this from occurring, and instead cause the other instances to reduce memory usage appropriately, in response to the OS low memory notification …P161.

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786