August 18, 2010 at 6:49 pm
One of my customer, due to organizational virutalization demands, have moved SQL 2005 running on a Quad 8G physical machine to a Quad 8G virtual machine (VM ESXi). Since then we have seen a significant performance degradation. SQL has consistently been a bottleneck. In all the measurements, SQL Buffer seems to be leading to the bottleneck
SQL Instance is configured to grab min memory of 4G and a max of 6.4G but I have never seen SQLservr.exe take beyond 1.2G. As the server moves towards slow downs we observe the sqlservr.exe consumes about 100-200M only while rest of the memory on the VM instance is reported as utilizated but not by any specific process and cummulative utilization is well under. Almost like an instance leak
All the files are spread across multiple LUNs configured as VMDKs as opposed to RDKs. The original configuration was in RDK format but VM made them move to VMDK format.
What we are experiencing is a highly utiilized application that suddenly runs in LCK_XX wait on a certain SPID and at this time VM instance reports 7-7.8G utilization but the SQLServr.exe reports only 200M utilization. The PLE drop well below 300 secs. This queues up everything. As soon we stop the SQLServr.exe, the avaiable memory reduces to 5G and then a restart makes it go up to 1G; never seen it climb beyond 1.5G. Also, after the SQL instance reboot ; PLE increases slowly from 100 up to 1000 then slowly 20K but again never seen it go beyond 20K. It used to be in the order of 100K before the move
As next steps, I'm planning on running RamMap to see whether any mapped files are problematic and run MSE to see if any SQL threads are going bonkers.
But short of this, I am running out of wits since I have limited to no visibility to the VM Host end. I run into a catch 22 with the VMAdmins because they are sort of clueless as well.
Any suggestions/ ideas would be much appreciated.
August 25, 2010 at 7:48 am
I had a parallel post on MSDN social. Definitely recommend reading Jonathan Kehayias excellent write up with hotlinks
August 26, 2010 at 6:44 am
Hire a performance tuning professional to do a file IO and waitstats analysis to find out exactly what sql server is waiting on. Worst-performing-query analysis would be done too.
There are many ways moving to a VM can harm performance, especially when VM admins don't know what they are doing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply