2005 Memory question

[StitoJunior]

Hi All,

Quick question about memory allocation. I've read a few other older posts about this but wanted to double check.
Spec -
Windows Server 2003 Standard Edition SP2
SQL SERVER 2005 Standard Edition

There's 4GB RAM on the server.

The memory properties for SQL Server seem to be the default i.e. min of 0 max 2GB.

Will the database take more than the 2GB dynamically as it needs it or am I sitting with unused memory?

I haven't touched boot.ini or the AWE settings as mentioned in the other posts.

thanks in advance.

 

[Alan0568]

Unless you go 64bit OS & sql you won't use anymore. You can set the /3GB switch in boot.ini which will allow a 32bit app to use of portition of it, but thats all.

A

 

[SQLWilts]

You can use AWE/PAE settings, but on a 4GB machine, it isn't really practical.

AWE/PAE allows you to use a lot more of the memory, even on a 32 bit machine. Memory constraints are no longer an issue in SQL 2005 - check out here:

http://sql-server-performance.com/Community/forums/p/23532/133162.aspx

As suggested by Alan, the /3GB is probably the best way to go - that would leave 1GB for the op system.

 

[StitoJunior]

Thanks a lot folks. Appreciate it.

 

[Alan0568]

AWE .... Actually thats a point I would like to clarify as I have heard conflicting opinions.

If the original posters OS is "Windows Server 2003 Standard Edition SP2" **32bit** then is the max memory for that OS 4gb? If so surely AWE can't achieve anything in this scanario.

I guess I've hijacked the thread but is this true?

A

 

[mrdenny]

Correct, you are always limited by the amount of memory that the OS can deal with. Windows 2003 Standard 32bit would cap you at 4 Gigs no matter how much you have installed, or what edition of SQL you have installed.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Alan0568]

Thanks for that. I often see AWE discussed as a magic answer, without mention of the details of the OS i.e x64/enterprise/datacenter.

A