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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.