Blog | Jun 20, 2014
Memory Management and Troubleshooting Related Bottlenecks on the SQL Server
Memory management is an important aspect of fine-tuning the SQL Server performance. Various options are provided for one’s review and consideration. The SQL server dynamically acquires and frees memory as necessary. Typically, an administrator does not have to specify how much memory should be allocated to the SQL Server, although the option exists and is required in some environments.
As there are several types of memory resources, it is important to understand and differentiate each type of a memory resource:
- Virtual Memory
- Memory Pool
- 4GT Tuning
Virtual Memory Manager: Enables windows to provide virtually unlimited memory to the system. It is constructed on a page-based memory scheme which divides all of memory in equal chunks.
Memory Pools: They are of the following two types:-
Paged Pool: Is that region of the virtual memory in a system space that can be paged in and out of the system. It is accessible from any process context. The device drives that do not need to access the memory from the dispatch level or above can use a paged pool.
Non-Paged Pool: Includes a range of system virtual addresses that are guaranteed to reside on a physical memory at all times. It can be accessed at any time from a process context by incurring a page fault.
4GT Tuning: The operating systems based on Windows NT technology provide applications with a 4 GB virtual address space.
/3GB Switch: Is used within the Boot.ini to increase the size of a user process address space from 2 GB to 3 GB and to curb the Kernel space from 2 GB to 1 GB. The application.exe must be flagged as a 3-GB aware application.
/USERVA Switch: Initiated with Windows 2003 servers and Windows XP SP1 onwards, a new switch /USERVA was incorporated to work with a /3GB switch. The /USERVA switch is used with /3GB switch in Boot.ini file to tune the user-mode space into a value between 2 GB and 3 GB.
/3GB Switch is not supported on 64 Bit systems.
PAE (Physical Address Extension): Allows up to 64 GB of physical memory. The PAE maps up to a 64 GB of memory into a 32-bit (4 GB) virtual address space.
AWE (Address Windowing Extensions): Can be used if the account under which SQL Service is running (the "Service Account") has the "Lock Pages in Memory" privilege granted to it in a gpedit.msc.
Let’s understand what Memory Pressure is:
Memory pressure indicates missing indexes, badly designed tables, an actual lack of memory on the server for the workload it’s been tasked with, other applications running on the system and users logging onto the server
External Physical Memory Pressure:
If the physical memory (RAM) is running low, it can cause the system to trim working sets of currently running processes, which may result in an overall slowdown.
• \Memory\Available Bytes: Indicates the free physical memory on the host server. If the value stays consistently low, it is usually a sign of memory pressure or stress.
• \Memory\Pages Input/sec: The rate of reading pages from the disk. We need to compare the average value with the baseline for further analysis.
• \Memory\Pages Output/sec: The rate of dirty pages that need to be written to disk. This is not a desirable condition. Usually this indicates memory pressure.
• \Memory\Pages/sec: The number of pages read from or written to disk for the hard page faults. The recommended average value should be less than 50.
• Find major system memory consumers and attempt to eliminate (if possible).
Check for adequate system RAM and consider adding more.
External Virtual Memory Pressure:-
Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bringing it to a halt.
• Paging File: %Usage: This counter indicates the percentage of the paging file that is currently being used by the operating system. If %Usage is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”.
• Increase the swap file size and check for the major physical memory consumers and follow steps of the external physical memory pressure.
Internal Physical Memory pressure:
The SQL Server detects high memory consumption internally, causing redistribution of memory between the internal components.
• \SQLServer:Memory Manager\Memory Grants Pending: This counter represents the number of processes waiting for memory grant within the SQL Server memory. The recommended average is 0.
• \SQLServer:Memory Manager\Target Server Memory (KB): Shows the amount of memory that the SQL Server wishes to consume.
• \SQLServer:Memory Manager\Total Server Memory (KB): This counter shows the SQL Server estimate of how much RAM it would need to operate effectively. The total memory counter shows what was actually used. This should be close to the Target Server Memory (KB).
Internal Virtual Memory Pressure:
The SQL Server detects this condition and may release reserved regions of VAS, reduce buffer pool commit target, and start shrinking caches.
• \SQL Server:Buffer Manager\ Page Life Expectancy: This counter indicates the duration, a data page can stay in the buffer pool without being referenced. The higher the value the better. An average recommended value should be 300 (5 minutes).
• \SQLServer:Buffer Manager\Buffer cache hit ratio: This counter measures the percentage of pages that are read from the buffer cache without having to read from a disk. This value should stay consistently high or as close to 100% as possible.
• \SQLServer:Buffer Manager\ Lazy writes/sec: This counter measures the dirty buffers, data pages that have been modified, that need to be written to a disk to free up the memory. The recommended average value should be less than 20.
• Identify major memory consumers inside the SQL Server and verify the server configuration. Further actions depend on the investigation like: check for workload; possible design issues; other resource bottlenecks.
The following tools and sources of information could be used for troubleshooting.
• Memory related DMVs (some of DMV’s are sys.dm_os_memory_clerks, sys.dm_os_memory_objects , sys.dm_os_memory_nodes ,sys.dm_os_memory_pools)
• DBCC MEMORYSTATUS command
• Performance counters: performance monitor or DMV for SQL Server specific object
• Task Manager and Event viewer: application log, system log
About TriCore Solutions
TriCore Solutions, the application management experts, provides a full suite of scalable and reliable managed application, cloud, infrastructure hosting, and consulting services to enterprise organizations. The company delivers its services and the TriCore Trusted Promise to more than 250 companies worldwide to reduce costs, raise service levels, improve customer experience, increase business agility, and accelerate innovation, unlocking the business value from their IT investments. TriCore Solutions is headquartered in Boston, MA, with offices in India and throughout North America.