I want to ensure that my SQL Server on a VMware Guest OS is getting the resources it should. This would go a long way towards helping isolate the performance problems we're experiencing. However, our system administrators won't give us access to VirtualCenter or any of the tools they use. Is there anything I can do? Check out this tip to learn more.
If you have the ability to use Performance Monitor on the OS and the VMware Tools have been installed, you should be able to do so (assuming you're on vSphere 4.x or higher). When the VMware Tools install, they provide extensions to PerfMon which allows you to see what VMware is doing with respect to memory and processor. If you open up PerfMon and scroll through your available counters, you should see two that start with VM.
Before you look at specific counters, make sure to check Show description so that you can see what each counter represents.
If you expand the VM Memory counter collection, you should see a whole list of counters to monitor. The same will be true for VM Processor. Add the counters you want to view. For instance, I've added the memory that is being physically backed by the host (Memory Mapped in MB) and the memory the Guest OS is actually using (Memory Active in MB) as well as the effective processor MHz vs. the actual processor MHz.
These work like any other PerfMon counters, meaning you can monitor them in real time or set them up to log for analysis later.
Measuring these values over time will allow you to determine if there are too many of too few resources being allocated to your Guest OS. Since SQL Server is like any other RDBMS and gains performance by caching data into memory, you should definitely scrutinize memory. If you suspect the host is reclaiming memory due to a physical memory resource shortage, track Memory Mapped in MB along with the Memory Ballooned in MB.
Often times, when a Guest OS is designed, we hang on to old numbers for both processor and memory. Sometimes what we thought we were using on an older system isn't what we need. For instance, if the below performance is true most of the time, we can probably adjust the memory allocated to the Guest OS because the active memory use isn't even close to it.
sqlfriend2, if you have your system administrators fired, who will monitor the servers while you rehire? Also, while that may be a fantasy solution, in the real world many have to deal with these sorts of limitations and DBAs are usually not in a position to get system administrators fired. So we work with what we have.
You're also making the assumption that the organization has purchased the VirtualCenter licenses. There are other options and those many not be as conducive to giving access. Why would an organizaiton do that? Cost. VirtualCenter is a premium cost. Others, not so much.
When I first checked, I didn't see the VM counters you mentioned in PerfMon on my VM guest. So I went to some notes I had for saving PerfMon counters under Windows 7 (or Windows Server 2008) and found a technique that allowed me to "expand" what was available. To do this, I went to Start/Run/MMC/File/Add Snapin and the VM PerfMon counters were available and savable. BTW, MMC is Microsoft Management Console. I don't know why I had to do it this way, but it works. For what it is worth.
Monday, November 12, 2012 - 10:24:19 AM - sqlfriend2
Have your system administrators fired. Their refusal to give you READ access to the VMs that run your SQL servers is unacceptable. I have RO to all of mine, and their refusal is just job security for them. Regardless, you NEED vCenter client access. I find it helpful when the WMI calls show 100% CPU utilization and the actual vCenter counters show only 20%. Time to really start troubleshooting. You need to know what is going on in the host side as well as the client. Only seeing half the picture is not helpful when you are dealing with mission critical apps and their databases.
Wednesday, November 07, 2012 - 3:41:32 PM - K. Brian Kelley
1. Yes. Memory Active is a running counter to show what the Guest OS is actually using. The memory mapped is the physical memory allocated. Consider that on a physical server this would be like looking at memory in use vs. total memory.
2. The regular counters don't give the DBA much useful information. For instance, if you have 80% processor utilization, 80% processor utilization of what percentage of the physical processor? Memory will tell you, for instance, what physical memory the Guest OS *thinks* it has, but of course this is being abstracted from it. That's why the VM counters are provided.
Wednesday, November 07, 2012 - 2:23:41 PM - sqlfriend