Thursday, September 12, 2013

SQL Server Memory Management Part I

Understanding Physical and Virtual memory helps to understand SQL manages memory

Windows has both physical and virtual memory. Memory is managed in the form of pages, ideally processes demands memory as needed. Memory pages are in 4kb size in both virtual and physical memory.

On Win32 bit architecture total addressable memory is 4GB out of which 2GB is accessible from user mode and 2 GB from kernel mode, and kernel mode memory shares between all processes. Using /3GB switch we can change the process address space of kernel made to 1GB instead of 2GB and user address space will be expanded by 3GB instead of 2GB. We need to add /3GB switch to boot.ini file and need to reboot the computer. 

On Win64 bit architecture total addressable memory is 16 EB but due to architectural reasons it supports only 16 TB on which the user address space is 8 TB and system address space is 8 TB.

Physical Memory -- It is actual memory (RAM) installed on the system. It works very fast but it is limited. 

Virtual Memory -- As the name suggests it is not real memory, it is one classification of memory which was created by using hard disk for simulating additional RAM, and the addressable space available to the user (Pagefile.Sys). It is divided into three categories 

a) Private virtual memory -- The address space for each process is private and which is not shared by other process until it is shared.

b) Shared virtual memory -- Two or more process can share regions of their virtual address space.

c) Free memory -- As the name indicates memory is not defined use and it is available to be allocated to your process.

 Private and shared memory is further categorized in two ways a) Reserved & b) Committed

Reserved -- The page has been reserved for future use, and the range of addresses cannot be used by other allocation functions. This page is not accessible and has no physical storage attached to it and it is available to be committed.

Committed -- This is the space that is currently used, physical storage is allocated for the page. In this case operating system will decide either physical memory or space in the paging file is sufficient for this allocation.

No comments: