How to optimize SQL 2008 memory utilization?

We run SQL 2008 on a VM of Win 2008 with 80G memory and in the Control Panel \ System it does show memory : 80 GB. But in the Task Manager \ Performance tab it shows the "Physical Memory" total = 32G. Does it mean the Win 2008 OS doesn't use more than 32G and we have been wasting the big amount of unused memory? What could be wrong?

Also, in SQL server properties / Memory pane, it shows the Minimum Server Memory = 2,048 MB and the Maximum Server Memory = 12,288 MB (please see the attached screen shot). Can you tell me if we should change the two numbers in order to optimize the SQL performance?
CastlewoodAsked:
Who is Participating?
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.

Phil DavidsonCommented:
I don't see a screenshot.  Is the SQL Server 2008 instance 32 bit or 64 bit?  Is your Windows server 32 bit or 64 bit?  I wonder if the SQL Server 2008 resource governor has been configured to use no more than 12,288 MB of RAM.  SQL will perform better if you allocate more memory to it.

Are you sure the 80 GB isn't the size of the hard drive (and thus storage capacity)?  80 GB is a great deal of RAM.
0
arnoldCommented:
What is the sum total of all your databases on this instance?
If they are adding up to no more than 32GB SQL server/ and this total VM memory usage has no need to use more than 32GB.

Check the OS configuration within the VM (propertirs of computer\advanced settings\advanced\pergormance settings\advanced),

Are you having performance issues?
0
CastlewoodAuthor Commented:
"Check the OS configuration within the VM (propertirs of computer\advanced settings\advanced\pergormance settings\advanced), "

I checked the above and found it is for setting up paging file size in hd not for RAM. Correct?

Both OS and SQL are x64.
80 GB is the memory assigned by the VMWare host.  
Please see the attached screen shot for the Minimum / Maximum Server Memory setting in SQL server properties / Memory pane. Should I increase the Maximum? And how many?

We just moved the SQL/ERP using VMware Converter (P2V) and we want to give more memory in the hope of seeing the better performance as we have plenty of RAM. Can you help how to?
SQL-memory.png
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.

CastlewoodAuthor Commented:
Ok, I found the maximum RAM for a 2008 Standard server is only 32 G. That's clear now. But should I increase the SQL Maximum Server Memory setting in SQL server properties / Memory pane. Currently it is 12,288 MB. Please help.
0
arnoldCommented:
Your limited by the server not SQL.
In taskmgr what does the sqlserver reflect as memory used?

Yes you can increase the memory allocation to be higher, you might save some resources by starting the minimum allocation closer to where you want it to start with the max 30GB allowing 2GB or so for the OS .....
The settings reflect the priority for processor and memory to be programs, correct?
0

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
CastlewoodAuthor Commented:
In Task Manager, sqlserver process uses 214M of ram. Is it you asked for?

"The settings reflect the priority for processor and memory to be programs, correct? "
The "Adjust for best performance of" currently is with "Background services" checked. Should I change it to "Programs" for this SQL server?

There are about 5 databases in this instance and each db is about 10 G.
For memory allocation in SQL, should I give
the Minimum Server Memory = 10G and the Maximum Server Memory = 30G ?
0
arnoldCommented:
Yes, adjusting the prioritization for programs for process and memory should improve the performance/VM internal resource allocation, the host to guest is a .....

While SQL is using 214M, how much memory is being used in total on the system?

Was this configured for awe?

It could be that the server is not being used enough to need a higher resource.

Yes, you can adjust the minimum to 10GB with a maximum of 29-30Gb to see what happens.


Since this is a VM what is the load on the host and how many other VMs are running on the host?
0
CastlewoodAuthor Commented:
It said 7% Used Physical Memory.
AWE is checked.
This host is empty only for the SQL vm.
0
arnoldCommented:
See sp_configure
To check whether awe is checked and what memory allocation it is set to.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3191c3f6-fba1-4aeb-91b6-290daf3b4eef/sql-server-2008-awe?forum=sqlsetupandupgrade

On a 64bit it should not be using awe as you have a larger access to resources.
The SQL server is running under what credential. Local system, or a user account?
Awe was needed on ENT 32bit server OS to have SQL access much more memory that 2GB.

You could try to boost CPU priority option under the SQL server properties. Deals with running SQL with higher priority.
0
Scott PletcherSenior DBACommented:
If the server has 80GB of RAM, give SQL Server its full 32GB, unless the load on SQL is extremely small.
0
CastlewoodAuthor Commented:
After we changed the Minimum memory to 10G, now in Task Manager / Processes, the sqlservr.exe now use about 10G. And it is running under a AD user account with Domain Admin rights. But does it matter? Just curious. Can you tell me under which login account will matter?
0
arnoldCommented:
Often it is not advisable to run the service under an administrative account, but the account being used needs to have appropriate rights such as logon as service, run as service, lock pages in memory, etc. but most of the time, the easier route is to create a service account and grant it administrative rights.
......

Do you see a performance change?

You may want to setup perfmon to monitor the system performance to baseline its operations.


Cacti.net with SNMP installed on systems can also be used to monitor system resource use
0
Scott PletcherSenior DBACommented:
You wouldn't normally ever give a SQL Server box full AD admin rights, because that's extraordinarily dangerous from a security standpoint.  You'd be better off creating a new AD account that only has access to what that SQL instance needs to get to.  You can give the account local server admin if you really need to.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Castlewood, do you still need help with this question?
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 2008

From novice to tech pro — start learning today.

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.