3GB PAE and AWE - Taking away (some) confusion...
It's a long time since I wanted to post some info concerning memory on this blog, but I always been confused on where to start from.
To take away any doubt, let's start from scratch. Even amongst the most seasoned DBAs there is some confusion about memory , so we should try first to clarify these.
Starting with what is already available over the internet, so far the best blog I found you should consider starting with if you are a newbie in the "memory world", is the following:
PAE and /3GB and AWE oh my...
https://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx
For any further detailed information on SQL server memory internals, Slava Oks's WebLog (https://blogs.msdn.com/slavao/default.aspx) is what you need.
To summarize (from the SQL Server point of view)
1) With default settings:
Each process will be assigned memory split between:
Physical Memory - user-mode - therefore max 2GB (max depending on phy mem or other apps using the same space) + Disk (Paged)
2) Same (nearly) happens with \3GB.
Each process will be assigned memory split between:
Physical Memory - user-mode - therefore max 3GB (max depending on phy mem or other apps using the same space) + Disk (Paged)
3) With \PAE and AWE
PAE\AWE-aware processes will be assigned up to 64GB of memory (depends on Windows Edition) split between:
Physical Memory - user-mode - therefore max 2GB or 3GB depending on \3GB option (see rule of thumb below) + Physical Memory (Additional) + Disk (Paged)
Rule of Thumb
If your system has < 4 GB - use only /3GB
If your system has > 4 GB and < 16 GB - use /3GB + /PAE + AWE
If your system has > 16 GB - use /PAE + AWE
Now then, so why considering x64 Architecture if we have AWE?
The use of SQL Server (32-bit) with AWE has several important limitations. The additional memory addressability is available only to the relational database engine’s management of database page buffers. It is not available to other memory consuming database operations such as caching query plans, sorting, indexing, joins, or for storing user connection information. It is also not available on other engines such as Analysis Services.
For more detailed information, refer to:
Advantages of a 64-bit Environment
https://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
What about max_server_memory and min_server_memory?
By default, SQL Server can change its memory requirements dynamically based on available system resources. Which means, it can use the amount of memory specified between min_server_memory and max_server_memory.
Use min server memory to guarantee a minimum amount of memory available to the buffer pool of an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced.
Use max server memory to prevent the SQL Server buffer pool from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly.
Usually (underlined, yes it is underlined) you should leave these settings at the default value, BUT the following considerations\exceptions apply:
- As there is a short delay between the start of a new application and the time SQL Server releases memory, using max server memory prevents this delay and may give better performance to the other application.
- With SQL Server 2000 AWE or SQL 2005\2008 AWE on Windows 2000, memory is not managed dynamically. This means, min server memory is ignored and max server memory is never released so must be set (underlined again).
Min server memory and max server memory are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately (without a server stop and restart).
For more info:
Enabling AWE Memory for SQL Server