August 14, 2006 at 4:38 pm
We have a two-node SQL cluster, and each node is running a SQL instance with databases that require ~3GB of memory to perform adequately. These nodes are Windows 2003, and we're running SQL2K EE. Each node has 8GB of memory.
What is the correct configuration for memory, assuming that in the event of a failure we would want both databases to run on the same node?
I find the documentation out there on the /3GB switch to be very confusing - if I enable the /3GB switch on the nodes, would each SQL Server instance use 3GB of memory, leaving the OS with 2GB for other tasks? Or do I need to configure /PAE and AWE and configure each server to use 3GB of memory with the "max server memory"?
Reading the documentation out there, I'm not sure whether enabling 3GB would mean both instances each get 3GB of memory or whether they would share 3GB of memory and leave the 3GB of memory outside the addressable 4GB limit unused.
Insights, especially ones that reference Microsoft documentation, would be appreciated.
Thanks,
Mark
August 15, 2006 at 4:38 am
The /3GB switch is in BOOT.INI and therefore refers to the operating system, not SQL Server.
With 32-bit addressing, you can access 4GB of memory. This is normally divided by the OS so that Windows has 2GB reserved to itself and 2GB available for each application. The /3GB switch restricts Windows to 1GB and allows each application to use 3GB. Restricting Windows to 1GB is normally OK, but depending on the mix of applications running can cause memory shortage in some pools. If all you run on your box is SQL Server plus the normal corporate anti-virus, etc, security stuff you should be OK.
The use of /3GB is only valid if you have between 3GB and 16GB memory on the box, and is only needed for 32-bit servers. Below 3GB memory nothing happens. With 16GB or more, Windows need more than 1GB to manage the additional memory so /3GB has to be removed.
You will also need the /PAE switch if you want SQL to use AWE memory. /PAE uses smoke and mirrors to allow AWE aware applications (such as SQL) to use memory outside the 4GB address range that 32-bit provides. The memory above 4GB can be considered as backing storage, and has to be copied within the normal 4GB range when it is used, but as this is done at memory speed you get good performance.
SQL uses AWE for database bufferpool storage only. All storage used for user connections, internal sorts and hashes, etc, continues to live under the 4GB line (true for all 32-bit versions of SQL) so a system with many 1,000s of users could run out of sub 4GB memory regardless of what is on the box.
You need to activate AWE configuration option in SQL to use AWE, and allocate a minimum of 3GB memory on SQL startup. If you ask for less than 3GB then AWE will not be used. This memory has to be contiguous and must be available at SQL startup time. If you have a number of SQL instances on a large box with different AWE memory requirements and start and stop them a lot you may end up with fragmented memory above 4GB and the AWE allocation could be rejected.
I have read advice saying if you use AWE you should use a fixed memory value, or set min and max to the same. I had a recent case with Microsoft where they advised against doing this - SQL can allocate more memory than the max value you specify, and if you have a fixed allocation the excess memory will never be released. The MS advice for AWE use was to always set min lower than max so that any excess memory allocations could be released.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 15, 2006 at 10:10 am
Ed-
Thanks, this is consistent with what I understood. Is it true to say that enabling /PAE in the BOOT.INI is pointless unless you enable AWE in SQL?
Also, I was under the impression that once AWE memory was allocated it could not be released. Has this changed in Windows 2003?
Thanks again,
Mark
August 15, 2006 at 10:24 am
Yes, you need to do both. Use /PAE in boot.ini to enable extended adressing support in Windows, then enable AWE in SQL to allow SQL to use the enabled support.
My understanding is still that AWE memory cannot be released after it has been allocated until SQL is closed. A variable memory size, even if you use AWE, allows SQL to release any over-allocations of memory made below the 4GB line.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 15, 2006 at 10:34 am
I've always set min and max memory to be the same with awe and also checked the reserve physical memory.
I've never found awe using more memory than allocated, I can see no reason to allow freeing memory, what else could possibly use the awe memory on a sql box unless you're running other awe aware apps.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 16, 2006 at 7:21 am
The issue is not with the AWE memory allocation, it is to do with the normal memory allocations SQL makes below the 4GB line. The argument I was given by PSS is that if min and max memory are the same, the mechanism for releasing memory is disabled.
If SQL has memory allocated below the 4GB line that could be released because it is no longer needed then it cannot release it if the memory values are fixed. If there is some leeway between min and max memory then the unwanted sub-4GB memory can be released.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 16, 2006 at 7:58 am
Hmm .. I sometimes wonder at the messages coming out from microsoft. Again what would you be releasing memory for? SQL will only take the 2gb from below the 4Gb line anyway .. or maybe it doesn't .. there's a little known fact ( I've usually found ) that you can enable awe on a server with 4Gb of ram ( or you could last time I tried this !! ) forgetting the /3gb switch enabling /pae on a 4gb ram box will allow you to allocate 3gb of ram to sql server. I guess this increases the data cache 'cos allegedly awe only gives you more data cache .. I must dig out Ken Henderson's internals book for another read.
Last time I discussed this with ms it was agreed that taking the memory management away form the o/s was good and fixing the memory was essential if you did so. I suppose to quote Kimberley Tripp " It just depends" ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply