Link to home
Start Free TrialLog in
Avatar of Type25
Type25

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Type25
Type25

ASKER

Awe doesn't make any difference in a 64bit environment
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Type25

ASKER

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.
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.
Avatar of Type25

ASKER

Why would SQL use 100% CPU though?
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.
Avatar of Type25

ASKER

We kind of know which ones are intensive but we can't optimize them anymore. Are we stuck?
You could post the most intensive query along with the execution plan. I'm sure someone will have a look.
there should be split between comment 31740990 and 31741440