x

Max Memory limit of SQL Server 2005 Standard Edition 32 bit

Hi experts,

I have Windows Server 2003 R2 Standard Edition 32 bit with SQL Server 2005 Standard Edition SP2 installed on my production database server.There we have 4 GB RAM in total which is the maximum memory limit of underlying operating system.Recently I figured out there is a memory bottleneck so I am going to upgrade operating system to Windows Server 2003 R2 Enterprise edition 32 bit which can support up to 64 GB RAM enabling PAE (Physical Address Extension)setting on the server as I found on MSDN site.

If I have 12 GB RAM in total on the server and there is no application other than SQL Server installed on the machine, what is the maximum memory that can be used by SQL Server 2005 STANDARD EDITION sp2 32 bit ?

Thanks, Hemant
more ▼

asked Nov 26, 2010 at 04:33 AM in Default

Hemant gravatar image

Hemant
277 12 12 14

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
more ▼

answered Nov 26, 2010 at 04:37 AM

bonskijr gravatar image

bonskijr
204 5 5 7

So can it be confirmed , SQL Server 2005 Standard Edition (32 bit) on Windows Server 2003 R2 Enterprise Edition (32 bit) can use up to 64 GB RAM which is the maximum memory limit on the given OS ??
Nov 26, 2010 at 04:51 AM Hemant
It should, though I havent had experience of memory beyond 8GB for our servers. Of course like Kev Riley said, you should leave some for the OS.
Nov 26, 2010 at 05:01 AM bonskijr
(comments are locked)
10|1200 characters needed characters left

It is OS maximum.

So assuming you get 12GB, you can, in theory, use 12GB although you'd be wise to leave some for the OS :)
more ▼

answered Nov 26, 2010 at 04:40 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Do I need to do enable AWE (Advanced Windowing Extension) on SQL Server to make it use that much RAM or can it use the available memory on its own without enabling AWE ?
Nov 26, 2010 at 04:58 AM Hemant
Use AWE and give the service account the "Lock pages in memory" privilege under Local Security Policy.
Nov 26, 2010 at 05:12 AM Blackhawk-17
Yes I believe you do need to configure AWE correctly.
Nov 26, 2010 at 05:19 AM Kev Riley ♦♦
Does SQL Server Standard Edition Support AWE feature ?
Nov 26, 2010 at 05:27 AM Hemant
2005 as of SP2
Nov 26, 2010 at 08:40 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

To add to @bonskijr's answer - here is a list of memory limits for windows:

[2]: http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx#physical_memory_limits_windows_server_2003
more ▼

answered Nov 26, 2010 at 04:49 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

If I would, I will set the server memory to (keep 2 GB of memory for operating system)-

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 10240;
GO
RECONFIGURE;
GO

Also you can get better performance by setting the AWE option on. [Enabling AWE Memory in 32 bit Server][1]

[1]: http://msdn.microsoft.com/en-us/library/ms190673.aspx
more ▼

answered Nov 26, 2010 at 04:59 AM

Leo gravatar image

Leo
1.6k 54 56 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x57
x12

asked: Nov 26, 2010 at 04:33 AM

Seen: 8882 times

Last Updated: Nov 26, 2010 at 04:42 AM