SQL Server 2005 And Alarms

[vanberg]

Hello,

I have a re-occuring problem on 3 VM's. All 3 are running MS SQL Server, and all 3 are constantly triggering the "Virtual Machine Memory Usage" alarm. Most have 4 GB of RAM, and 2 CPU's. On one of the VM's I even set a memory reservation of the RAM. This helped some, but after running for more than 1 day it starts to alert again. This server is running our test SharePoint Database only, it runs about half the speed as our production SharePoint server does. This even happens if it is the only guest on the host.

Has anyone had any luck getting SQL Server to work without generating alarms? Or, even run close to the same speed as a physical server?

Stats:

• Memory Configured: 4800MB

• Memory Reservation: 4096

• Host Memmory: 128GB

• Host CPU 16x 2.693GHz

• VM CPU: 2

• VM C: drive 100GB (Has some file storage on it...no databases)

• VM D: Drive 100GB (Has all DB's and log files)

• Memory Ballon Average: 0

• Memory Usage average: 94.61%

• Memory Active AVG: 4601174

• Memory Granted AVG: 4655104

• SQL Server Memory in VM used: 3.7GB

• Time for production page served (Physical): 1 Second

• Time for VM Page serverd: 5 Seconds

Thanks,

Eric

[Lightbulb]

Don't have much experience with SQL 2005 in a virtual environment, but as a rule SQL will eat all available memory over time. This is how it is designed, it will only release memory when asked by some other process, otherwise over time it will take as much memory is available on the system.

This looks bad but is perfectly ok and monitoring should be adjusted accordingly. You can limit the amount of memory the SQL instance uses in the settings of the SQL server you can set min max memory settings. You might be able to back the max setting to a level just below what will normally generate alerts.

[vanberg]

I will try that and see if that will fix the alerts.

Do you have any idea on the perfomance side? A 5 second page load is painful, even for a test environment.

[Lightbulb]

You have 2 vcpu VMs are you sure you need dual CPU? sometimes multiple vCPUs actually inject latency due to scheduling issues.

As to SQL performance the most common bottleneck is I/O. Are your Data/Transaction/TEMPDB files on seperate vmdks?

Is the LUN used for creation of the Data/Transaction/TEMPDB vmdks highr performance (RAID 10)

Just a thought.

[LucasAlbers]

new sharepoint deploy doc.

Running Microsoft Office SharePoint Server 2007 on VMware Infrastructure

http://www.vmware.com/resources/techresources/10030

http://www.vmware.com/files/pdf/MOSS_2007_Virtualization_with_VMware_Infrastructure.pdf

[AndreTheGiant]

See also:

Andre

**if you found this or any other answer useful please consider allocating points for helpful or correct answers

[mnasir]

Eric,

I am running production SharePoint 2003 ON A VIRTUAL sql 2000 server and many other production databases on virtual SQL 2005 server. So far I have no issues. First thing, did you split the app server and database server on separate VM? This is recommended even in physical environment. Also, for the production sql server I have my storage on SAN, are you using SAN or local drives on esx host. I have several virtual disks seating on different LUNs, c: OS, D SQL, E: systems DBs, F: prod DBs just mdf files and g: Transactions logs. I am using 3GB memory with no reservation, So far absolutely no issue with performance

Also, how big is your user community? I have about 700 users who access SharePoint on a busy day. Also, some time you get better though pout if keep the app server and DB server on same ESX host, this way network traffic doesn't go out the production switch.

Please let me know if it is working for you. I have absolutely no issues.

• If you find this post helpful, please consider awarding points.