Defualt max memory used by SQL server when AWE/3GB is not enabled in 32 bit.

Question

Defualt max memory used by SQL server when AWE/3GB is not enabled in 32 bit.

Joy Smith San

SSC-Insane

Points: 24877
More actions
May 24, 2011 at 11:54 pm

#145459

Hi All,
One of our client is having a server with the following,
Windows 2003 (32 bit).
SQL Server 2005 with SP3 (32 bit)
Memory is 8 GB.
Neither 3Gb nor AWE is enabled.
1. In this case what will be the maximum memory used by SQL Server .?
2. Shall I enable AWE so that it will be able to use 10 - 12 GB of memory ?
Thanks,
San.

Viewing 10 posts - 1 through 10 (of 10 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Joy Smith San (5/24/2011)
Hi All,
One of our client is having a server with the following,
Windows 2003 (32 bit).
SQL Server 2005 with SP3 (32 bit)
Memory is 8 GB.
Neither 3Gb nor AWE is enabled.
1. In this case what will be the maximum memory used by SQL Server .?
2. Shall I enable AWE so that it will be able to use 10 - 12 GB of memory ?
Thanks,
San.

1. In this case what will be the maximum memory used by SQL Server .?

Is it dedicated server. If its dedicated server leave 2 GB for OS. set the max memory 6 GB.

2. Shall I enable AWE so that it will be able to use 10 - 12 GB of memory ?

How its possible you told you have 8 GB only.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

Smith,

Windows 2003 (32 bit).

What is OS edition ?

windows 2003 standard edition only supports maximum 4 GB memory only.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

1. In this case what will be the maximum memory used by SQL Server .?
Is it dedicated server. If its dedicated server leave 2 GB for OS. set the max memory 6 GB.
2. Shall I enable AWE so that it will be able to use 10 - 12 GB of memory ?
How its possible you told you have 8 GB only.

Thanks for your quick response.

It's a dedicated server. Max memory is set to a large value...

Yes, only 8 GB memory (...by mistake I mentioned 10-12...sorry).

Windows is Enterprise edition with SP2.

I am still not clear on this...

If I simply set the MAX memory to 6GB, will SQL server use that much memory..?

If so, then why should we enable AWE at first place...?

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

No you need to restart the SQL server.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

Check this link. you may get an idea.

http://www.sqlservercentral.com/Forums/Topic702953-360-1.aspx

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Mr. Holio Hall of Fame Points: 3328 More actions · Answer 6

You didn't mention your SQL Server edition in one of your previous posts ...

1. In this case what will be the maximum memory used by SQL Server .?

I believe 2GB. (3GB if you use the /3GB switch)

I wouldn't bother setting a maximum memory value if it is a dedicated SQL Server as the kernel will have its own 2GB (or 1GB when using /3GB)

2. Shall I enable AWE so that it will be able to use 10 - 12 GB of memory ?

SQL could benefit from the extra memory..

- First make sure you have the /PAE switch in boot.ini (and /3GB, up to you)

- Maximize SQL Server memory

- Enable AWE, give rights for your service account for lock pages in memory.

- restart server

If it is Standard edition, you're in trouble.

__________________________
Allzu viel ist ungesund...

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

If you have AWE enabled or are running 64-bit, you need to set a sensible max memory. Otherwise it's easily possible for SQL to starve the OS of memory.

If SQL is 32 bit, the PAE flag needs to be in boot.ini and AWE needs to be enabled, otherwise SQL can use no more than 2GB of physical memory. If you have 8GB of memory and this box is dedicated to SQL, set max memory for around 6GB. I would not recommend the use of /3GB for a server with more than 4 GB of memory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 8

Based on this recent case study, you should reserve 1 GB per CPU on a 64bit OS. You can likely halve or third this on a 32 bit OS.

http://sqlcat.com/technicalnotes/archive/2011/04/26/lessons-learned-from-benchmarking-a-tier-1-core-banking-isv-solution-temenos-t24.aspx

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 9

If SQL is 32 bit, the PAE flag needs to be in boot.ini and AWE needs to be enabled, otherwise SQL can use no more than 2GB of physical memory.

Thanks Gila. My doubt is cleared. I noticed in task manager that SQL never takes morethan 1.7GB.

I enable AWE and allocate 5 - 6 GB to SQL, after checking other applciations.

Thanks to all again.