SQL Server 2008 recommended memory configuration

Hi All, I have SQL 2008 R2 installed pretty much with the default install settings.  This server is a VM on VMWware ESXi.  I have 6GB of RAM allocated to this SQL VM.  SQL is the only application installed.

After a few days of SQL running, SQL uses a little less than 5GB of memory.... from what I understand this is normal...leaving hardly anything for the OS. Occassionally, the server hangs and freezes up...and I'm thinking it's because SQL is hogging all the memory and not leaving any for other processes.

I know that I can set the max memory - but what is the recommended way of handling this?  Is it best practice to set the max memory? or to just leave the defaults?

I'll find out shortly if setting max mem solved this particular issue.
polaris101Asked:
Who is Participating?
 
Andrew Hancock (VMware vExpert / EE MVE^2)Connect With a Mentor VMware and Virtualization ConsultantCommented:
Just leave the defaults, when SQL starts up it will use all the memory in the server, releasing memory back to the Operating system, when the Operating System or applications request it.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
What is the database, application, how many concurrent users?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
polaris101Author Commented:
@hanccocka - only 10 users, and it's an MS Access application on another server
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
The server freezing or not responding is a little worrying?

could you ping the server?

could you RDP (terminal services to the server)

check the server in vSphere GUI Console?

What was the CPU usage?

It's not performing reports or querys, causing the server to go 100%?

what is the performance for the server? (looking at the history for the VM)

What datastore is the server using, RAID 5, RAID 10, RAID 6, what speed of disks?

what spec of host VMware Server?
0
 
Danny McDanielClinical Systems AnalystCommented:
I drop the memory so I don't have to see the guest memory usage alarm and I figure if that causes performance to suffer, then I need to give the VM more memory as it would probably be hitting the cap sometime soon in the future.
0
 
polaris101Author Commented:
@hancocka-
hanccocka:
 


could you ping the server?
HAVE NOT TRIED THIS WHILE THE SERVER IS SLUGGISH AND NOT RESPONSIVE.  

could you RDP (terminal services to the server)?
WHEN THE FREEZING IN THE APP OCCURS, THE RDP TO THE SQL IS FROZEN AS WELL. OR EXTREMELY SLOW.

check the server in vSphere GUI Console?
YES, APPEARS NORMAL.

What was the CPU usage?
NORMAL

It's not performing reports or querys, causing the server to go 100%?
NOT THAT I HAVE PINPOINTED

what is the performance for the server? (looking at the history for the VM)
NORMAL

What datastore is the server using, RAID 5, RAID 10, RAID 6, what speed of disks?
RAID1

what spec of host VMware Server?
DUAL XEON'S, 16GB MEMORY, 10K DISKS, RAID 1
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
have you got any other vms on the host?

did you log into the sql server via console?

check task manager or resource monitor?
0
 
polaris101Author Commented:
have you got any other vms on the host?
YES, HOWEVER, I GAVE THE SQL VM HIGH SHARES UNDER RESOURCE ALLOCATION

did you log into the sql server via console?
RDP'D INTO CONSOLE

check task manager or resource monitor?
YES, HOWEVER, SESSION HANGS/FREEZES SO IT'S TOUGH TO GET ANYTHING FROM IT WHEN THE FREEZING IS OCCURING.

I'm fairly confident that it's the "Max memory" setting in SQL.  But, I'll find out for sure tomorrow and wednesday. SQL was using about 5GB, which leaves only 1 GB for the OS.  So, I'm hoping that forcing SQL to only be able to use 4GB will solve my issues here. Thoughts?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
it shouldnt freeze the server. but cpu at 100% will, everything look good on performance charts?

i was wondering if you had loggedninto console via the vsphere client?

is this problem reproduceable?

sql installations do use all the memory, but release it back to the os, applications on request.

so shouldnt freeze.
0
 
polaris101Author Commented:
Nothing looks out of the ordinary in the performance charts in vsphere client.   I have not logged in to the vsphere client while it has occured.

No, unfortuntely, I cannot reproduce it.

I have read articles that say you can leave the default memory settings, and some that urge you to set a max.  So, I'm a little confused at this point.

Personally, it makes sense to me to set a max.  Until I can rule it out, I'm going to keep the max setting of 4GB that I set earlier.
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.