Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Viewing VMware Counters in PerfMon for SQL Server

MSSQLTips author K. Brian Kelley By:   |   Read Comments (6)   |   Related Tips: More > Virtual Machines VM
Problem

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.

Solution

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.

VMware counters in PerfMon

Before you look at specific counters, make sure to check Show description so that you can see what each counter represents.

Check the Show description check box to learn more about the counter

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.

VM Memory and VM Processor counter collection

These work like any other PerfMon counters, meaning you can monitor them in real time or set them up to log for analysis later.

Monitor PerfMon counters 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.

Guest OS active memory and mapped memory
Next Steps


Last Update: 11/7/2012


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, November 07, 2012 - 2:13:49 PM - sqlfriend Read The Tip

Thanks, very useful tips when tools are limited.

 


Wednesday, November 07, 2012 - 2:23:41 PM - sqlfriend Read The Tip

I have two questions about this:

1. in one of our testing server, I see the memory active in MB sometimes is lower than Memory mapped in MB. Is that possible, it doesnot make sense to me. If so, what will happen?

2. The regular counter like memory and processor counter (not the vm counters), do they still give dba correct counters or not about the virtual guest server? is it still useful?

 

Thanks,


Wednesday, November 07, 2012 - 3:41:32 PM - K. Brian Kelley Read The Tip

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.


Monday, November 12, 2012 - 10:24:19 AM - sqlfriend2 Read The Tip

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. 


Monday, November 19, 2012 - 11:36:41 AM - AZJim Read The Tip

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 19, 2012 - 12:08:28 PM - K. Brian Kelley Read The Tip

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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.