SQL Server memory architecture

  • Article

SQL Server 2005 dynamically acquires and frees memory as required. Administrator does not have to specify how much memory should be allocated to SQL Server, although following options exist:

  • 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.
  • 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.
  • If locked pages in memory is granted, it is recommended to set Max Server Memory.
  • 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.

For managing memory for large databases, refer to this article.