Overview Of Memory Architecture In SQL Server

Overview Of Memory Architecture In SQL Server

SQL SERVER Memory Architecture.

Virtual memory = physical Ram + Page Filing 

Page Filing is nothing but a hard disk space.

Standard 32 bit addresses can map a maximum of 4 GB of memory. By default, on 32 bit operating system, 2 GB of this space is reserved for operating system and rest 2 GB is made available to the application. You can specify a /3gb parameter in boot.ini file of windows server so that operating system only reserves 1 GB for itself and 3GB is allocated to application.

Using WOW64 (Windows on Windows 64) upto 4 GB virtual address space can be used by SQL Server application. WOW64 is the mode in which 32 bit SQL Server runs on 64 bit operating system.

SQL Server support Address Windows Extensions (AWE) allowing the use of physical memory over 4 GB on 32 bit versions of MS Windows operating systems. Upto 64 GB is supported.

Windows Server 2000 enterprise Edition can see VM up to 32 GB

Windows Server 2003 data centre Edition can see VM up to 64 GB

Instance of SQL Server running on Windows Server 2000 use static AWE memory allocation. The instances of SQL Server that are running on Windows Server 2003 use dynamic AWE memory allocation.

Support for AWE only applies to SQL Server 2005 Enterprise, Standard and Developer editions and only applies to 32 bit operating systems. There is no need for this on 64 bit operating systems since the virtual address space limit rises upto 7 terabytes on IA64 architecture and 8 terabytes on X64 architecture.

To support more than 4 GB of physical memory on 32 bit operating system, you must add /pae (physical address extension) parameter to boot.ini file and reboot the computer.

If there is more than 16 GB of physical memory available on a computer, it requires 2 GB of virtual address space for system purposes and therefore /3gb parameter is not supported in boot.ini file. If the parameter exists then the computer will not use any memory available above 16 GB.

The Min Server Memory and Max Server Memory configuration options establish minimum and maximum limits to the amount of memory used by the buffer pool of SQL Server Database engine.

SQL Server memory divided into 2 parts, One is Buffer pool and 2nd is Non-Buffer pool

Buffer Pool again divided into 2 Parts one is Data Cache and 2nd one is Log Cache

Non-Buffer Pool can see only 4 GB of memory and Buffer pool can see 32 GB