May 7, 2013 at 4:35 am
We recently migrated our production server from a physical cluster environment to a VM environment, and ever since then the query performance is just bad.
We get more time outs, more waits and more deadlocks.
The resources assigned to this VM server is similar to what if was on the physical cluster: same CPU count, same disk speeds on the host drives, etc. The only thing that has changed is the memory allocation is less. We had 64 GB and now have 40 GB.
When I do Perfmon counters for Memory and CPU checks, the counters are all in the suggested ranges.
The CPU graph in task manager runs at an average of 50-70% and memory usage is 32 GB.
The DMVs also do not indicated any underlying issue.
Why then would we be experiencing such degradation in query performance - mostly on a time out, wait, level?
Any suggestions?
I have read that it could be that the virtual machines aren't configured correctly for optimal SQL server performance - how could I check this? What should it be?
May 7, 2013 at 8:12 am
Aah the fun of VM, where CPU are not really your CPU and RAM is not really you RAM...
Check with the VM admin to see if the host is not busy when you get your timeout.
Unless you are alone on the host, you have to also account for the other VM activities.
They can steal your CPU, your RAM and slow your IOs to a crawl.
Your VM admin should easily spot CPU and RAM bottleneck and you can check for disk latency on your own (Logical Disk -> Avg. Disk sec/Transfer in perfmon).
You should have your perfmon counters running all the time, when you have a problem and when you don't, that way you have some baseline to compare it to.
Ideally you should already have the same kind of data from your old physical machine so you can point some differences.
Default timeouts are usually around 10 or 15 seconds, so that can be hard to spot if your collection delay is too long.
If it's something you can reproduce at will then you can watch the perfmon counter live while it happen.
May 7, 2013 at 8:31 am
It's not a simple thing to debug and trace down.
SQL Server can run fine under virtualization, but you have to understand that configuring xx resources for a VM doesn't mean it gets them. Or it gets them when needed. I have friends running lots of SQL Servers under VMWare, but they also have fewer guests on host machines when SQL Server is involved, and dedicated IO pathing. They also have to watch to be sure that there aren't memory issues with the host not providing enough RAM.
I'd ask follow the advice from Oliii and check with the VM admin. Check your waits and latency for IO inside SQL Server, and have the Admin verify that enough resources are sent to the SQL Server. You might even try moving all other guests off that host and see if things improve.
May 7, 2013 at 8:39 am
I would recommend to read through this. As Jeff & Oliiii said there may be numerous factors which maybe affecting it.
May 7, 2013 at 2:26 pm
Are you using Reserved Memory for the VM? What type of VM disks are you using VMDK or RDM? Are your SCSI controllers ParaVirtual? Are the SCSI Controllers defined on a separate SCSI Adapter (1.0, 2.0 not 1.0, 1.1 etc)? Was this a newly built VM or a PtoV migration? Is VMWare Tools installed and up to date? Is the VM Host over committed? Is the new hardware faster than the old hardware? Is Hardware Assist turned on in the BIOS on the vm host? Do you have a vCPU/NUMA issue?
There are lots of things that could cause this.
May 8, 2013 at 6:53 am
Hi,
Thanks so much for your input - I have unfortunately not received much help from the VM admin - he has this attitude of "It must be SQL". So my hands are tied ...
I am doing as many checks on the virtual server as I can but like you said - I need to know what is happening at the host and he wont give me that info, or at the very least I get: "The host if fine - check your queries" uhg.....#$%#$%#
I should maybe also add that the time outs are all coming from the application but when you run the same queries in SSMS, they run really quickly. I know there are some ADO overhead when it comes the the app, but is it possible that a VM setup could have made this worse?
May 8, 2013 at 7:00 am
If queries are running fine in Query analyzer but not in application, I suspect two things.
1) Parameter sniffing
2) Network latency
Try to work on these 2 things and see what you find.
October 29, 2013 at 11:44 pm
Hope I can continue on this thread.. since its regarding the same issue..
SQL Server in one of our VM machine is giving timeout expired while logging in through SSMS on different machine. Upon checking task manager it said 90% of memory is occupied. But sql server uses little over 10% and no other processes is taking up that much memory.. please let me know which process could occupy the remaining memory..
Thanks for your valuable suggestions.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
October 30, 2013 at 5:07 am
I think you are seeing the resource ballooning issue.
See if this could help.
http://mssqlwiki.com/2013/03/31/sql-server-and-vmware-ballooning/
November 4, 2013 at 3:40 am
he has this attitude of "It must be SQL"
My advice is to escalate this to your management. Both you and the VM admin are working for a common customer - your business. There is no space in a professional IT shop for turf wars.
You and the VM admin need to work together to solve this problem. If they are not willing to respond to your requests then it is the job of your management to facilitate getting you both working together. And if the problem does lie with SQL Server, thank the VM admin for his help in fixing it...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply