SQL VMWARE performance

We run an application, AMS 360, as our in-house agency management application.  The company who programs it is very vocal about how the product does NOT run well in a virtualized environment.  Still, the benefits to virtualized consolidated backups are a no-brainer and we will not be traveling as advised...

True to their word, I installed the application (Two Parts: IIS server and DB back-end) and it is, for lack of better words, sludgy.  Hopefully one of the experts has experience troubleshooting web front end/sql DB applications contention.  I've googled and haven't found any ground breaking information.

I don't think it's hardware contention by any means.  The guest can r/w 800MB/s, It has 16GB memory (I've tested with 24), 8 cores (which remain bored throughout the day, even though affinity is set corretly) and the DB is around 120GB.  The users access the application through an identical 2008R2 TS.

There's got to be some VMWARE setting I've overlooked or not read about that is causing the application to stick.  Is there some IIS compression or caching I could enable?  Some caveat I don't know about as far as best practices with IIS or SQL that is commonly overlooked?  I'm at your mercy.  Please help.

Thanks!
LVL 16
Chris HInfrastructure ManagerAsked:
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.

jhyieslaCommented:
I can't speak to that particular application, but I know that we run MS SQL, several servers in fact, in a virtual server environment and we have no issues with it.  As long as you're running a regular server install, mostly the app shouldn't know that it's virtual.

One thing though - is the application very disk intensive?  We have done two things that did improve our overall SQL access.  One was to make the data drive RAID 10 instead of 5.  The other thing was to move from spinning disks to a Pure Storage SSD system. The IOPS on the thing are amazing.  But, these things only help if the app itself is very disk intensive.
0
Harper McDonaldCommented:
By chance are you running McAfee IDS on any of these VM's?  (HIPS)
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
It's true, we've had many issues with SQL (in a virtual environment!). That we've had to migrate it to a physical environment, the hypervisor just takes too many "ticks" away from the process.

Allocate more vCPUs, how many have you allocated at present ?

What is the datastore ?
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.

Harper McDonaldCommented:
SQL is very memory intensive, are you using Idrea to monitor the memory consumption?  Are you getting any Event ID 701 memory errors in the Windows or SQL logs?
0
Harper McDonaldCommented:
How much memory are you allocating to SQL vs. the OS on the SQL VM?  How many VM's do you have on the host?  Are you overcommiting the RAM too much from the host?
0
Chris HInfrastructure ManagerAuthor Commented:
@jhyiesla Not disk intensive.  The server appears bored.  

@Harper McDonald Not running and AV

@Andrew Hancock vCPU is at it's free maximum, 8.  Datastore is local RAID-0 Samsung BEVO (maybe revo) SSD's.

@Harper McDonald Not using Idrea.  Never even heard of it.  16GB is dedicated to the guest.  SQL eats it all.  No 701 errors found.  None at all, actually.  That in itself is weird...  Not overcommitting...  No balooning.... Just a quiet, sludgy SQL installation.
0
Chris HInfrastructure ManagerAuthor Commented:
I will add, when the conversion engineer remoted into the box and ran a few test queries, he did say "'wow', this thing is really fast.  Faster than ours..."  Which, from a cost and sanity standpoint, there is no way they're not virtualizing their data centers for the online version of the product.

And @Harper

How much memory are you allocating to SQL vs. the OS on the SQL VM?  

How can I check that?  The working private memory set is 14GB in the taskmgr.
0
Harper McDonaldCommented:
Should leave at least 4 GB for the OS - SQL is a hog (Pin 12GB for SQL and leave 4GB for the OS).  Try looking at Perfmon as well to monitor the memory..The reason I asked about the McAfee IDS application is that there is a memory leak bug that they are aware of - update 5 will be out sometime Q2.  It's not AV but IDS (Intrusion Detection Software...kinda dumb too me.  We use it but in the past I've always used hardware IDS's)
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
When you say sludgy SQL installation are you comparing with a physical SQL installation ?

Have you always had 8 vCPU assigned, you've not over commited, and have too many vCPUs ?

e.g. have you tried 2, 4 ?
0
Harper McDonaldCommented:
Are you fully patched as well?
0
Chris HInfrastructure ManagerAuthor Commented:
@Andrew It's much more responsive on a physical installation.  I've tested on half that much hardware and it isn't sludgy.  Literally staring at the resource monitor the entire time I've been on this question and nothing, other than max'd memory, would indicate a bottleneck.  But, even with max memory, the disk r/w is absurdly fast.  Just tested the data disks in production and they write at 500-600MB/s, read at 800MB/s.

I have always had 8.  Should I drop down to 2?
0
Chris HInfrastructure ManagerAuthor Commented:
@harper Do you have instruction or  alink on how to pin memory?  I have not done that yet.  And yes, fully patched.
0
Harper McDonaldCommented:
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
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I think you are finding, what we discovered here, many years ago, and we've been moving SQL from virtual to physical installations since....

this is a video I made, of a Web Front End and Back End SQL Server. The requirement was the client wanted it to be virtualised, it runs a system with approx 80 concurrent users using the Web front End, and Submitting Queries to the Database.

This was a virtual machine solely running on a HP DL 360 G6, 32GB RAM, 15k SAS Disks, under ESXi.

It was allocated 32GB RAM, and 8 vCPUs, and the issue, when some submitted a query it would flat line the VM for 13 minutes, running the query......hopeless case...

We moved the SQL instance to the same hardware, DL 360 G6, 32G RAM, 15k SAS Disks, under Windows 2008 R2, with no hypervisor, and performance was PERFECT, no issues!

Client decided physical installation was the answer.

Video - Poor Microsoft SQL Performance caused by Virtualisation - Not all servers are equal!

I've had to obscure some of the text because it's confidential, but you can see the performance graph.....is max out, until query stops....

I hope you find your answer, we know what the solution is...
0
Chris HInfrastructure ManagerAuthor Commented:
I will pin the memory and hopefully that alleviates some of the bloat.  

Other than that... I guess we live with the performance hit.  I'm never going back to physical.  Not ever... Never...  

As always, thanks for the swift responses.
0
Chris HInfrastructure ManagerAuthor Commented:
A quick follow-up for @Andrew
http://www.vmware.com/files/pdf/solutions/VMware-SQL-Server-vSphere6-Performance.pdf

The new tests show large SQL Server database instances continue to run extremely efficiently, achieving great performance in a variety of virtual machine configurations on vSphere 6. Only minor tunings to the SQL Server application and the vSphere 6 ESXi hypervisor were needed to fully optimize these large virtual machines.

Have you had any benefit to upgrading from 5.5 to 6?  

I'm thinking the benefits they're mentioning are based solely on being able to allocate more vHardware and not a more efficient use of the current pHardware?

Thanks!
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
that is correct, because you can now allocate 128 vCPU/4TB to make a monster VM.

see here

https://blogs.vmware.com/performance/2015/03/vmware-vsphere-6-oracle-12c-scalability-study-scaling-monster-virtual-machines.html

but there are still some contradictions! because in the article above, which talks about creating a monster VM, with 120 vCPU on a server that only has four processors and 15 cores, 60 cores total, VMware recommends, that you do not exceed the number of physical cores, e.g. 60 vCPU (because of hyperthreading the Hypervisor would see this as 120 vCPU)

the following article states not recommended, because it's likely that all vCPU will be heavily utilised, so, technically, once you exceed 60 vCPUs, performance is not linear, so therefore, it's not going to double in performance, which they do highlight in the above article.

http://blogs.vmware.com/vsphere/2014/02/overcommit-vcpupcpu-monster-vms.html

I think this is marketing, ESXi versus Hyper-V, and my "Dad is bigger than your Dad!"

As for physical servers, I've not seen many past 80 cores yet in a single server......(excluding hyperthreading!)
1
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

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.