Memory usage SQL server EE on Windows 2003

Question

Memory usage SQL server EE on Windows 2003

blom0344

SSCrazy

Points: 2073
More actions
August 2, 2006 at 8:14 am

#61699

We basically have 2 identical servers (W2003) that run SQL server 2000 EE.
Memory settings for server:
Min = 768, Max = 6144
The servers themselves have 8gig RAM
The production server has working set size set at 1, the other server has default 0.
I know that min and max settings should be the same if working set size is set at 1.
However, is this out-of the box setup going to use more than 4gig when running on W2003?
There is an excellent article of activating AWE memory when running under W2000, but I suspect W2003 is a different story (cannot a boot.ini for instance)
How can I optimize the production machine and be sure it is using all of the allocated memory. We run into memory problems once in a while on production...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

As far as I know, the same rules apply to Windows 2003 as to Windows 2000, if you're not using the 64-bit edition. The memory restrictions are not due to the operating system itself, but to the 4GB address space of a 32-bit processor. So, set the /PAE switch in boot.ini (I think you'll find it does exist), and then set the min and max memory as high as you dare, leaving just 1GB, say, for the operating system. This is assuming your server is dedicated to SQL Server. If it isn't, set the memory lower and let the other application(s) have the rest. Set the awe_enabled option, and don't forget to apply the AWE hotfix if you are on Service Pack 4.

John

humbleDBA Hall of Fame Points: 3540 More actions · Answer 2

With 8GB of RAM, you'll also need to use the /3GB switch with the /PAE switch, if you want to make use of greater than 6GB for SQL Server (otherwise 2GB will be reserved for the OS, instead of 1GB).

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

John Mitchell-245523

SSC Guru

Points: 148809

August 3, 2006 at 9:03 am

#653133

Yes, you're right.

John

blom0344 SSCrazy Points: 2073 More actions · Answer 4

I found this:

http://support.microsoft.com/default.aspx/kb/811891/en-us

and:

http://support.microsoft.com/default.aspx/kb/822164/en-us

which only adds to the confusion. Can anyone of shed some light on this?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The first one is saying that you need to grant your service account the Lock Pages in Memory right.

With regard to the second article, it seems to me to follow that, since you can't have dynamic memory allocation with AWE, the set working size option isn't going to have any effect with AWE.

John