[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2008 recommended memory configuration

Posted on 2011-05-09
11
Medium Priority
?
371 Views
Last Modified: 2012-11-12
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.
0
Comment
Question by:polaris101
  • 6
  • 4
11 Comments
 
LVL 124

Accepted Solution

by:
Andrew Hancock (VMware vExpert / EE MVE^2) earned 2000 total points
ID: 35723783
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
 
LVL 124
ID: 35723788
What is the database, application, how many concurrent users?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:polaris101
ID: 35723808
@hanccocka - only 10 users, and it's an MS Access application on another server
0
 
LVL 124
ID: 35723832
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
 
LVL 16

Expert Comment

by:Danny McDaniel
ID: 35723953
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
 

Author Comment

by:polaris101
ID: 35725203
@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
 
LVL 124
ID: 35725218
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
 

Author Comment

by:polaris101
ID: 35725252
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
 
LVL 124
ID: 35725272
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
 

Author Comment

by:polaris101
ID: 35725294
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This Micro Tutorial steps you through the configuration steps to configure your ESXi host Management Network settings and test the management network, ensure the host is recognized by the DNS Server, configure a new password, and the troubleshooting…
This Micro Tutorial walks you through using a remote console to access a server and install ESXi 5.1. This example is showing remote access and installation using a Dell server. The hypervisor is the very first component of your virtual infrastructu…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question