question

Hemant avatar image
Hemant asked

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
sql-server-2005memorywindows-2003
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bonskijr avatar image
bonskijr answered
Feature comparison says "Memory limited to maximum supported by operating system." [ http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx][1] [1]: http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 ??
0 Likes 0 ·
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.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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 :)
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Use AWE and give the service account the "Lock pages in memory" privilege under Local Security Policy.
2 Likes 2 ·
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 ?
0 Likes 0 ·
Yes I believe you do need to configure AWE correctly.
0 Likes 0 ·
Does SQL Server Standard Edition Support AWE feature ?
0 Likes 0 ·
2005 as of SP2
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.