RAM memory management

[disturbedone]

I'm no SQL expert so I'm looking for advice/thoughts. The server was built by a contractor pretty much as you see below. I have increased RAM on the instances but that's about it.

I have an SQL2K8R2-64bit VM on W2K8R2 on ESXi5.0 with RAM configured as follows:
VM total - 14GB
Instance1 - 3GB (hosts vSphere, VMware View, payroll DBs
Instance2 - 8GB (hosts finance & staff/student management DB)
OS - 3GB (14-8-3 = 3)

The VM has 1x virtual socket with 2 cores per socket. The CPUs on the ESXi5.0 host are Intel Xeon CPU E5-26400 2.5GHz.
The VM has C:\ (40GB for OS) and D:\ (300GB for data).

The memory for each instance in server properties has been configured as follows:
Use AWE to allocate memory - NO
Minimum server memory (in MB) - 0
Minimum server memory (in MB) - 3072 (or 8192)
Index creation memory - 0
Minimum memory per query (in KB) - 1024

Virtual memory (page file) is configured to 'Automatically manage paging file size for all drives'.

Users of the payroll system (on Instance1) report slowness when performing actions intermittently. Users of the finance & staff/student management system (on Instance2) also report slowness when running reports intermittently - RAM has been increased from 4-8GB in an attempt to improve this although it doesn't seem to have done much so it has been put back to the vendor. The Instance1 is barely used but when payroll complain it is suspected, but without any real evidence, that the high use on Instance2 might be causing the server to respond slowly. My limited understanding was that separate instances kept memory separate and they would not affect each other.

Any advice or thoughts would be appreciated.

 

[gmmastros]

My guess is that your problem has more to do with the drives instead of ram. Drives are usually 100 to 1000 times slower than ram, so you may be running in to contention there. I oils recommend that you add several physical hard drives to the server so that your data file is on a separate drive from your log file. Do this for each instance.

It's also possible that you are running in to a locking/blocking condition, which is usually corrected in code.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[disturbedone]

This is a virtual machine. No possibility of physical disks. All servers here are virtual.

In years gone by the practice has been to put logs on a different disk than the data but these days, at least as far as Exchange goes, there's no need to (from what I've read anyway). Not sure if the same applies to SQL - thoughts?

Would locking/blocking on Instance1 cause slowness on Instance2???

 

[gmmastros]

My experience with hyper v allows you to add separate disks per virtual machine. I don't know about esxi. I know that putting data and logs on separate disks will speed up performance.

Blocking one one instance would not affect another instance.

Have you run any traces on the instances to see what's going on? The will likely help you determine if its ram, disk, CPU, or something else.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

No possibility of physical disks

So what are the virtual disks stored on?? I am guessing there ARE physical disks.

If your disks are your bottle neck you need to look at how your virtual disks are spread across the physical disks (assuming there are more then one).

This includes what config they are in (type of RAID), the speed of the disks, and how the data\logs are apportioned across physical disks.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[fredericofonseca]

There is always a possibility of adding new physical disks to the datastore, or to use san/iscsi disks.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[PghMikeH]

Look at your memory paging. I have a hunch you're paging to disk. If that's the case, 3GB for the OS may not be sufficient. Ultimately, it depends on what you're doing. The other thing you want to look at is whether or not your VM has memory over-allocated. For many types of applications, this is fine. But for database servers, it's typically a bad idea. In VMWare, you can set reservations at the VM level.

Mike Hillwig
mike@hypermike.com

http://www.hypermike.com

I've done more R5 deployments than I care to think about.