Ram Memory and SqlServer
In a 32- bit environment, the address space is limited to 4 GB. A 32-bit processor uses 32 bits to position each byte of memory . Each bit representing two possible values (O or 1 ), a long memory address of 32 bits can represent 2 ^ 32 positions.
In a 32- bit environment, the address space is limited to 4 GB. A 32-bit processor uses 32 bits to position each byte of memory . Each bit representing two possible values (O or 1 ), a long memory address of 32 bits can represent 2 ^ 32 positions. 2 ^ 32/1 024/1 024 = 4 096 MB = 4GB. This address space is divided into two equal parts. The first two gigabytes are available to applications, called the user memory , the following two are reserved for the operating system, and constitute the kernel memory . Even if your server does not have 4GB of physical RAM , Windows address the entire range by performing a mapping of virtual addresses to physical addresses using a process called VMM , Virtual Memory Manager.VMM is like a real crook who sells to different customers the same plot of land. through him, each process activity sees a space of 2 GB of RAM.
On a dedicated SQL Server machine, the activity of system operation himself
will be small, and does not require 2GB of kernel memory. You have the possibility of adjusting the distribution between user memory and memory operating system, with an option of loading the kernel. in the boot.ini file located in the root of your boot partition on the line corresponding to your operating system in the [operating systems] section, add the / 3GB option. example:
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /3GB
In doing so, you tell Windows to release an additional 1 GB for applications that support it (they must have enabled an IMAGE_FILE_LARGE_ADDRESS_AWARE bit in the header, which is of course the case of SQL Server), and dedicate 1GB to kernel memory. This is the option that we recommend on a dedicated machine, if you have up to 16 GB of physical memory. In addition, this option should be avoided. Indeed, in this case Windows needs extra memory for page tables (page table entries, or PTE) for this memory address. If you leave the / 3GB option on a machine that has more than 16 GB of RAM installed, additional memory will simply be ignored, which is probably not the intention.
You can tell if / 3GB is enabled in SQL Server by querying the view:sys.dm_os_sys_info, column virtual_memory_in_bytes:
SELECT
CASE
WHEN virtual_memory_in_bytes / 1024 / (2048*1024) <1
THEN 'Not activated'
ELSE '/ 3GB'
END
FROM sys.dm_os_sys_info;
Well, but what can we do with memory beyond the virtual address space of 4GB? Since the Pentium Pro, all Intel 32-bit processors (x86 family) include a memory mapping mode called PAE (Physical Address Extension), which provides access to 64 GB. In PAE mode, the memory management unit (MMU Memory Management Unit, responsible for an integrated access circuit memory and paging features) benefit additional address lines that allow it to handle 36 bit addresses. 2 ^ 36/1 024/1 024= 65536Mo = 64 GB.
To activate the PAE mode on Windows (from Windows 2000), you must add the /PAE option in the same boot.ini file:
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
This option enables the loading of a particular image of the Windows kernel (Ntkrnlpa.exe or Ntkrpamp.exe on a multiprocessor machine instead of
Ntoskrnl.exe).