source: http://www.MSSQLTips.com/tip.asp?id=2806 -- printed: 11/28/2015 6:46:16 PM
Viewing VMware Counters in PerfMon for SQL ServerWritten By: K. Brian Kelley -- 11/7/2012
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.