MEMORY
MANAGEMENT :
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 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