October 6, 2005 at 2:54 am
From reading this site it seems that getting SQL 2000 Enterprise Edition to recognise memory beyond the 2GB limit requires three things.
1. Grant the service account "lock pages in memory" privileges.
2. Use sp_configure to set the "AWE enabled" option.
3. Add /3GB /PAE to Boot.INI
I have been told that in our environment SQL Server automatically recognises the additional RAM without these 3 steps. How is this possible and is there any way to check that this is genuinely the case?
October 6, 2005 at 3:04 am
Are you using 64-bit? How much memory do you have and recognise?
October 6, 2005 at 3:21 am
No it is the 32Bit version and the SQL Server seems to recognise 3GB RAM.
The server is running in an Active/Passive cluster so I was wondering if the cluster service/manager is forcing SQL to recognise the extra RAM?
Beyond knowing that clustering if for resilience I don't know anything about clustering so I am stabbing in the dark with this one.
October 6, 2005 at 3:56 am
The /3GB switch alone would do this, but I guess you are saying that it is not set here?
October 6, 2005 at 4:01 am
Yes, it is not set.
Our infrastructure guys said that SQL 2K automatically recognised the memory out of the box from a standard install.
October 6, 2005 at 5:08 am
Is it possible to aquire the memory? Have you tried setting min and max server memory to 2.5GB (for instance) and set workingset size to 1?
October 7, 2005 at 2:24 am
Windows will recognise 4 gig out of the box and will be able to allocate a max of 2 gig to any one application. Depending on how much RAM the machine has you want to enable different settings. If you have over 4GB RAM you want to use the /PAE switch in the Boot.ini If you have 2-4GB RAM you want to use /3GB. This will allow applications to use 3GB of the 4GB and only leave 1GB for Windows. In a clustered environment this would probably be avoided as Windows required more memory to manage the cluster. And obviously if this is not a dedicated SQL box then again don't use this switch.
Have a read on the microsoft site about it, there are quite a few articles detailing what you should do.
October 7, 2005 at 2:51 am
Yes I have read the Microsoft literature. The situation is that I have just joined a company as their first DBA and they have a very knowledgable infrastructure team and development team who don't necessarily communicate as well as they should. I am in the latter.
The infrastructure team manage the live boxes to which the development team don't have access but they are saying that 32Bit SQL Server automatically recognises memory beyond the 2GB limit without any further modifications.
Absolutely everything I have read says that this is only true for 64Bit SQL Server and that the boot.ini switches, lock pages in memory policy and sp_configure AWE enabled are necessary for the 32Bit version.
Is there a situation where 32Bit SQL Server can recognise the extra memory with no configuration changes? What happens if it is running on Windows 2003?
October 7, 2005 at 3:23 am
It will if you are running Windows DataCentre Server, what are the details of your server, OS, SQL Version & amount of RAM, and is it a pure SQL box or are you running other things on it?
October 7, 2005 at 3:50 am
Its Windows 2000 Advanced Server cluster (Active/Passive) dedicated to SQL2000 Enterprise Edition 32 bit at SP3.
October 7, 2005 at 3:59 am
How much ram does the machine have?
October 7, 2005 at 5:30 am
My experience for 32-bit SQL Server is this:
1) The memory that SQL Server can address is dependant on Windows settings and SQL Server settings. The default is that any edition of SQL 2000 can only access 2GB memory. (I don't know if Windows Data Center Edition is different.)
2) If you have between 3GB and 4GB memory, you can give up to 1GB additional memory to any edition of SQL by using the /3GB switch in boot.ini. However, you must have the appropriate Windows edition for this to work - most editions will accept the parameter, but you need W2000 Enterprise Edition or W2003 Standard Edition or above for the /3GB switch to be effective. A reboot is also needed to activate the /3GB option.
3) If you have over 4GB memory, you should use both the /3GB and /PAE switches. If you have over 16GB memory you must remove the /3GB switch as Windows needs additional memory to manage the extra storage. You need the right Windows edition to access this memory.
4) If you want to use any storage above the 4GB line you also need to use SQL 2000 Enterprise Edition and activate AWE. Standard Edition cannot access storage above the 4GB line, regardless of any Windows or SQL settings.
5) If you have EE and have got AWE turned on, you must use a fixed memory size, and give SQL the 'fix pages in memory' right. You must use a fixed memory size of at least 3GB, or storage above the 4GB line will not be used. Also, if the amount of storage you request is not available when SQL starts, SQL will only use storage below the 4GB line until SQL is restarted, regardless of if the additional memory becomes available.
6) The only information that is stored above the 4GB line by SQL is the buffer cache. Enough storage must remain below the 4GB line to satisfy requirements for the lock, connection, optimiser, etc buffers and for all other storage used by SQL.
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
October 13, 2005 at 3:02 pm
From task manager, check how much memory the SQL Server process is using. Unless the /3GB switch is in your boot.ini, you will see that SQL Server is using about 1.7G of memory.
Confusing SQL's allowed Min and Max memory configuration settings with how much RAM SQL Server recognises may be the issue.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply