SQL Server: Memory Architecture

-   SQL Server dynamically acquires and frees memory as required.

-   Typically, an administrator does not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.

-   SQL Server supports Address Windowing Extensions (AWE) allowing use of physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems. Up to 64 GB of physical memory is supported. Instances of SQL Server that are running on Microsoft Windows 2000 use static AWE memory allocation, and instances that are running on Microsoft Windows Server 2003 use dynamic AWE memory allocation.

-   Support for AWE is available only in the SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems. 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.


-   SQL Server builds a buffer pool in memory to hold pages read from the database. Much of the code in SQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. SQL Server tries to reach a balance between two goals:

• Keep the buffer pool from becoming so big that the entire system is low on memory.
  
• Minimize physical I/O to the database files by maximizing the size of the buffer pool.