maximizing sql server memory usage

Question

maximizing sql server memory usage

Nikita-395727

SSC Eights!

Points: 898
More actions
March 1, 2010 at 3:35 pm

#156728

Hello,
We currently have the following configuration for one of the virtualized sql server I inherited.
SQL server:
version: 2005
edition : enterprise
32 bit
Operating system:
version: windows sql server 2003
edition : standard
32 bit
Virtual host:
version: windows server 2003
edition: enterprise
64 bit
It is AWE enabled and Total server memory and target server memory counters show 3GB memory.
Is there any way fo sql server to see another GB of memory?
Thanks,
Nikki

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

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

Jambole Mr or Mrs. 500 Points: 540 More actions · Answer 1

With Windows Server 2003 Standard Edition you only can use 4GB of RAM. The AWE Option doesn´t change anything here. By using the /3GB Switch you will have 3GB Virtual Memory + 1GB Kernel Memory. That means a maximum of 3GB for SQL Server.

Sorry, you should change your Windows Edition to get more GB of RAM.

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

SQL Server 2005 Standard & Enterprise Edition supports Operating system Maximum RAM.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Nikita-395727 SSC Eights! Points: 898 More actions · Answer 3

Thanks everyone for the reply.

I knew that this is the best I can do with the current set up but wanted to confirm.

I just feel bad that all our Enterprise license are a total waste of money as they all have been installed on standard OS and really not taking advantage of any sql server enterprise version features especially memory.

Oh well ! Things for me to improve.. 🙂

Nikki

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Nikki-395727 (3/2/2010)
Thanks everyone for the reply.
I knew that this is the best I can do with the current set up but wanted to confirm.
I just feel bad that all our Enterprise license are a total waste of money as they all have been installed on standard OS and really not taking advantage of any sql server enterprise version features especially memory.
Oh well ! Things for me to improve.. 🙂
Nikki

It's not a total waste if you use things like partitioned tables, indexed views, or some of the more advanced features of SSAS or some of the advanced "transforms" in SSIS or "Online" reindexing, etc, etc, etc.

See the following for feature comparisons between editions. Don't cut the Enterprise edition short just because of the OS. 😉

http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx

I'm no expert at memory configuration on "short" OS's, but there are a number of articles and posts on GOOGLE that talk about using combination of /3GB, /AWE, and /PAE to get the Enterprise Edition to use more available memory. Might be worth a bit of deep diving on your part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I would check to see if the VM is limiting memory of the machine.

If you can't get more than 3GB allocated to the VM, turn off /3GB and/or AWE if enabled and restrict SQL Server to 2GB of ram max.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service