Memory Architecture

Microsoft SQL Server 2005 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.

Note

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

One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. 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.

By default, the SQL Server 2005 editions dynamically manage memory for each instance. There are differences in the way SQL Server manages AWE-mapped memory on Windows 2000 and later versions of the operating systems.

Note

In a heavily loaded system, some large queries that require a large amount of memory to run cannot get the minimum amount of requested memory and receive a time-out error while waiting for memory resources. To resolve this, increase the query wait Option. For a parallel query, consider reducing the max degree of parallelism Option.

Note

In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. To resolve this problem add physical memory or tune the queries to use a different and faster query plan. For tuning information, see Optimizing tempdb Performance and How to: Tune a Database.

Providing the Maximum Amount of Memory to SQL Server

By using AWE and the Locked Pages in Memory privilege, you can provide the following amounts of memory to the SQL Server Database Engine.

32-bit

64-bit

Conventional memory

All SQL Server editions: Up to process virtual address space limit:

• 2 GB
• 3 GB with /3gb boot parameter1
• 4 GB on WOW642

All SQL Server editions: Up to process virtual address space limit:

• 7 terabytes on IA64 architecture
• 8 terabytes on x64 architecture

Note:

On Windows Server 2003, the limitation is 512 GB; and on Windows Server 2003 Service Pack 1, the limitation is 1 terabyte. When Windows supports additional memory, SQL Server can reach the limits listed.

AWE mechanism (Allows SQL Server to go beyond the process virtual address space limit on 32-bit platform.)

SQL Server Standard, Enterprise, and Developer editions: Buffer pool is capable of accessing up to 64 GB of memory.

Not applicable3

Locked pages in memory operating system (OS) privilege (Allows locking physical memory, preventing OS paging of the locked memory.)4

SQL Server Standard, Enterprise, and Developer editions: Required for SQL Server process to use AWE mechanism. Memory allocated through AWE mechanism cannot be paged out.

Granting this privilege without enabling AWE has no effect on the server.

SQL Server Enterprise and Developer editions: Recommended, to avoid operating system paging. Might provide a performance benefit depending on the workload. The amount of memory accessible is similar to conventional memory case.

1**/3gb** is an operating-system boot parameter. For more information, visit the MSDN Library.

2 WOW64 (Windows on Windows 64) is a mode in which 32-bit SQL Server runs on a 64-bit operating system. For more information, visit the MSDN Library.

3 Note that the sp_configureawe enabled option is present on 64-bit SQL Server, but it is ignored. It is subject to removal in future releases or service packs of 64-bit SQL Server.

4 If locked pages in memory privilege is granted (either on 32-bit for AWE support or on 64-bit by itself), we recommend also setting max server memory. For more information, see Server Memory Options.

See Also

Tasks

How to: Enable the Lock Pages in Memory Option (Windows)

Concepts

Process Address Space
Dynamic Memory Management
Effects of min and max server memory
Using AWE

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: Added the table regarding maximum memory.