Setting maximum memory usage SQL 2008 Web Edition

We're using SQL 2008 Web Edition (which has no memory restrictions) and that's running on Windows 2008 64-bit.

We have 8GB of memory on our server but it's only using 2GB.

If we go into the properties and try up the maximum memory to say 5GB it won't let us go above 2GB and we aren't sure why.

Everything suggest we should be able to enter a figure above 2GB but it physically won't allow us too.

Any ideas?

Many thanks
LVL 9
Type25Asked:
Who is Participating?
 
Type25Author Commented:
Awe doesn't make any difference in a 64bit environment
0
 
gothamiteCommented:
Can you run sp_configure 'max server memory (MB)' and return the number you get back.

The number is in megabytes so are you sure you're not confusing the number with bytes as that would make it look like 2 Gb when in fact the number 2147483647 (the default) actually means unlimited.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Type25Author Commented:
Oops! Yes we were!

However, in task manager it says that we are only using 1.85GB of memory and the CPU is hitting 100%, but then dipping occassionally.

We have quite a lot of statements being run synchronously but with 8GB of memory and a dualcore machine it should cope with that fine.

We're kind of running out of ideas.
0
 
LMiller7Commented:
AWE can be used on a 64 bit OS, but only with 32 bit applications. Is your version of SQL 32 or 64 bits?

The Task Manager memory gauge doesn't mean what it seems to. It might show 1.85 GB used, but that doesn't mean that the remainder is free. Only that which is labeled "Free" will actually be free. The remainder will be on the standby list, memory that is in use but can be quickly reassigned for other uses if necessary.
0
 
Type25Author Commented:
Why would SQL use 100% CPU though?
0
 
gothamiteCommented:
That will be mostly down to the load you have running. You mentioned you have a lot of statements running - maybe they don't use much memory each but because you only have two cores, a lot of them are waiting on CPU.

You will need to investigate which queries are consuming the most CPU resources.
0
 
Type25Author Commented:
We kind of know which ones are intensive but we can't optimize them anymore. Are we stuck?
0
 
gothamiteCommented:
You could post the most intensive query along with the execution plan. I'm sure someone will have a look.
0
 
RiteshShahCommented:
there should be split between comment 31740990 and 31741440

 

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.