In a previous post we explained some of the challenges associated with monitoring CPU utilization on a VM.  One of the most significant new features in Spotlight on SQL Server Enterprise version 7.0 is the ability to provide further insight into VMWare’s impact on your SQL Server’s.  I’d like to explain the feature in greater detail below:

SoSSE is not designed to be a full blown VMWare monitoring package.  Quest has one of those (it’s called vFoglight) if the you’re interested.  SoSSE is hoping to provide you with enough information about your virtual environment so that you can rule in, or out, VMWare as the cause of your performance issue.  By showing a you the amount of resource utilization that ESX is “stealing” from your database server, you can immediately know whether or not your performance issue is caused by your SQL Server instance, or is caused by VMWare taking your resources.  Spotlight on SQL Server Enterprise can now provide full visibility into the entire OS stack that the database instance is running on. SQL Server -> Windows OS -> VMWare ESX.

This is exactly what DBA’s have been needing since VM’s became prevalent, the ability to know whether a problem is theirs to troubleshoot, or whether they are wasting their time looking into an issue that is caused by some other machine.  Obviously with any metric showing overhead, you want the value to be as low as possible.  SoSSE can raise alarms, if you’d like, whenever this metric exceeds a user definable percentage.

In addition to this single homepage metric, SoSSE also now has two additional drilldowns to provide more detailed information.  The Virtual CPU drilldown shows a historical graph showing  CPU % (idle and used) as well as the CPU that has been stolen by ESX% (in red).  In addition to CPU %, this drill down also shows the MHz of CPU that a VM has consumed.  We discussed in the last post how critical it is to analyze these two metrics  together!

If you determine that VMWare is taking resources from your VM, you can also view a list of VM’s managed by ESX so that you can view which VM is taking your resources.  You can sort this list by host to view all VM’s you are sharing an ESX server with, and then see how much (in GHZ) each VM is consuming.

We hope you find this feature useful and, as always, would love to hear your feedback!

Whether we like it or not (I’m not going to approach that political battle), virtualization is becoming a mainstay in not only our development environments, but also production.  Whether or not you agree with virtualizing production servers, you are eventually going to have to manage and tune them, either at your current job or your next.  I want to take this opportunity to explain and show the best way to analyze CPU utilization in a virtual environment.  The examples we show here are using VMWare vSphere 4 but the concepts apply regardless of your virtual platform.

Historically, when we look at a physical machine, the simple metric of CPU % Used is a pretty good measure of how busy a SQL Server is.  A server that is at one time showing 20% CPU utilization and then at another time is showing 80% CPU utilization is generating 4X as much work during the later time period.  In an operating system that is being hosted by a VM, we lose the luxury of knowing exactly how much CPU horsepower we have at any given time.  The are a few factors that attribute to this “grey area”:

1. A VMWare admin has the ability to set an upper limit on the amount of CPU that is available to your VM.  They can also set a reservation to guarantee an amount of CPU to your VM.
   
   I will not see this limit in My Computer -> Properties nor will I see any representation of this limit in task manager or perfmon.  Let’s assume that throughout the morning I have 2GHz available to my VM and am showing 20% CPU utilization.  Later in the afternoon, my VMWare admin needs to free up resources so they put a 1 GHz cap on my available CPU.  Now the exact same workload will show 40% CPU utilization.  Nothing has changed on the OS or in my SQL Server workload, yet I am showing twice the CPU %.  See where this gets confusing?
2. Even if the VMWare admin hasn’t set any resource cap on your VM’s available CPU, the ESX host could simply become overloaded.  Let’s say an ESX host has 8GHz of total processing power, and that host has 5 VM’s running on it.  Normally each VM uses about 1GHz of processing power, but all of a sudden, each VM needs 2GHz.  Like fitting 10 pounds of feathers into a 5 pound bag, something has to give.  What gives, is that ESX has to dynamically scale down each VM’s available CPU to account for the increased workload.  As a result, you may see 80% CPU utilization when looking at perfmon or task manager, but you have no idea what that 100% is of.
3. A virtual machine may not be tied to a single ESX host.  For DR or performance reasons, a VMWare administrator can move your VM from ESX host to ESX host without you knowing.  These ESX hosts also need not offer the same performance as one another.  You could be chugging along just fine during the morning with 4 GHz of processing power, and then in the afternoon be switched to an ESX host with 3 GHz of processing power.  Not only did you not know that this occured, but your VM’s CPU % will go up, even though the workload is unchanged.

Because of this, it is absolutely critical that you not simply look at CPU % as a measure of how busy SQL Server is or how much CPU it is using.  Percentages are always relative to a ceiling, and when that ceiling can move up or down at will (or whenever a VMWare admin decides that your ceiling is too high), the percentage itself loses meaning.  CPU% analyzed in conjunction with GHz used will allow you to paint the full picture of a VM’s CPU requirements.  Unfortunately, this data is not available by looking purely at the OS.  You will need metrics from the virtual layer as well.  That data is readily available in the built in VMWare client tools (vSphere), but you’ll either need to have access to the ESX or vCenter instance to view them, or a tight relationship with the VMWare admin who can send them to you.

For a better way to have this information at your fingertips, stay tuned…

Hello all, we get questions from time to time asking how alarms in Spotlight on SQL Server Enterprise can be configured to match a specific customers need.  Questions such as:

1) As a database administrator, all of my critical data files are on my F and G drives.  I don’t want to monitor those drives with the same thresholds and alerts as I do my C drive.

2) I have different backup policies for different databases on my servers and I don’t want to alarm on every database the same way.

All of these use cases can be met with Spotlight on SQL Server Enterprise’s alarm configurations.  Please watch the video below to see how its done.  If anyone has any questions or comments, or you want to be walked through a use case of your own, leave a comment or post in the forums!

Spotlight on SQL Server Enterprise has a great feature titled “Custom Counters” that allows you to incorporate your own custom data collections into the Spotlight Enterprise framework for data collection, diagnostics, and reporting.  This post will serve as a getting started guide to assist in adding custom counters to your Spotlight deployment.

First off, a disclaimer.  Due to the “open” nature of custom counters, Quest Software makes no guarantees as to the overhead or accuracy of any custom counter.  Custom Counters should be fully tested and validated in a customer’s environment prior to loading onto a production system.

For documentation on the counter used in this screencast as well as other possible counters, head to the Articles -> Documentation -> Spotlight on SQL Server Enterprise section of this site, or click on the link below:

http://sqlserver.quest.com/kbcategory.jspa?categoryID=372

If you have any questions, please post them to the forum.