Monday, April 14, 2014

Memory management in SQL Server


3G Switch
We can place the 3G switch in boot.ini file and useful only for 32 bit OS system.

Windows 32-bit Operating Systems implement a virtual memory system based on a flat 32-bit address space. This address space translates into 4GB of virtual memory - no more, and no less. By default, the address space is divided into two equal chunks. 2GB for the Kernel and 2GB for User-mode processes
/3GB boot-time option to allow the user mode address space to grow to 3GB. The /3GB option was intended as a short term solution to allow applications such as database servers to maintain more data in memory than a 2GB address space allowed. However, using the /3GB method to increase the user-mode memory space comes at a cost.
Using 3G has adverse impact on OS as kernal now have only 1GB available to fulfill kernel and other OS programs.

PAE Switch (Physical Address Extension)

PAE switch enable windows 32 bit server to support RAM memory upto 64 GB (for all windows 2003 edition) and 128GB (for windows 2003 datacenter edition).
NB: by default all 32 bit OS can support only 4GB of RAM.

To boot the system and utilize PAE memory, the /PAE switch must be added to the corresponding entry in the Boot.ini file. If a problem should arise, Safe Mode may be used, which causes the system to boot using the normal kernel (support for only 4 GB of RAM) even if the /PAE switch is part of the Boot.ini file.
This reduces the need to swap the memory of the page file and results in increased performance. The program itself is not aware of the actual memory size so its need to use its own memory manager to use increased space. (eg : AWE for sql server). Without PAE, AWE cannot reserve memory in excess of 4 GB.

AWE does not require PAE  but is often used together with PAE to allocate more than 4 GB of physical memory from a single 32-bit process
(AWE is a set of APIs that allows a process to allocate nonpaged physical memory and then dynamically map portions of this memory into the virtual address space of the process.)

when the /3GB switch is used in conjunction with the /PAE switch. In this case, the operating system does not use any memory in excess of 16 GB. This behavior is caused by kernel virtual memory space considerations. Thus, if the system restarts with the /3GB entry in the Boot.ini file, and the system has more than 16 GB of physical memory, the additional physical random access memory (RAM) is not used by the operating system. Restarting the computer without the /3GB switch enables the use of all the physical memory.

AWE (Address Windowing Extensions)
AWE is used by SQL Server when it has to support very large amounts of physical memory. AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version

Support for AWE is available only in the SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit versions of SQL Server. Analysis Services cannot take advantage of AWE mapped memory. If the available physical memory is less than the user mode virtual address space, AWE cannot be enabled.
AWE lets applications acquire physical memory, and then dynamically map views of the nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged memory can be much larger. This enables memory-intensive applications, such as large database systems, to address more memory than can be supported in a 32-bit address space.

·         To support more than 4 GB of physical memory on 32-bit operating systems, you must add the /pae parameter to the Boot.ini file and reboot the computer

·         If there is more than 16 GB of physical memory available on a computer, the operating system requires 2 GB of virtual address space for system purposes and therefore can support only a 2 GB user mode virtual address space. For the operating system to use the memory range above 16 GB, be sure that the /3gb parameter is not in the Boot.ini file. If it is, the operating system cannot use any physical memory above 16 GB.

·         The "Locked Pages in Memory" privilege must be enabled before AWE can be enabled

 Lock Pages in Memory :
Lock Pages in Memory is a Windows security setting policy that determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. This policy must be enabled to configure Address Windowing Extensions (AWE) and it is disable by default.

The operating system works with memory and paging file. The paging file is located on the disk, and when there is not enough physical memory left, the operating system can use the paging file and move data from the physical memory to it. When it will need that data, it will have to read it from the disk. The problem is that disk is a lot slower then the physical memory. If SQL Server's cache will be stored on the disk instead of the on the physical memory, you'll have a huge performance problem. When you grant SQL Server's service the right to lock pages in memory, the operating system won't be able to move memory pages that are used by SQL Server to the paging file and your are guaranteed that SQL Server will not use the paging file.

Disadvantage: Operating system will starve for memory when there is system wide memory pressure. OS has to completely rely on SQL Server to respond to low memory notification and scale down its memory usage . SQL Server may not respond fast enough to low memory condition at system level because OS is already starving for memory.

As sql server doesn’t release memory once acquire but OS can claim memory in emergency from sql server not using Lock pages in memory option.
It is advisable to set max server memory while using Lock pages in memory with leaving sufficient memory for OS and other programs.

To enable the lock pages in memory option

  • On the Start menu, click Run. In the Open box, type gpedit.msc.
  • On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
  • Expand Security Settings, and then expand Local Policies.
  • Select the User Rights Assignment folder.
  • In the pane, double-click Lock pages in memory.
  • In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
  • In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.
  • Log out and then log back in for this change to take effect.

Max and Min server memory:

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory confi gured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.

Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.

No comments:

Post a Comment