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…

More, different, or better?  Whatever you need, our SQL Server community at http://sqlserver.quest.com is there to help.  Many of you already frequent our forum location to engage in discussions about our database management products, but to improve upon the experience we’ve recently added a sticky topic specifically for you to request videos and collateral.  Do you want to know how Spotlight on SQL Server Enterprise can help you identify the I/O latency for an instance using SAN storage, and feel that a short video would be more helpful?  Would you like a brief tutorial on how to make the most out of the History view in Foglight Performance Analysis for SQL Server?  If so, we want to hear from you here: http://sqlserver.quest.com/forum.jspa?forumID=888, which is the direct link to our forum area entitled “Request Product Videos and Collateral Here”.  We’re looking forward to hearing from you!

Looking to use LiteSpeed for SQL Server for Log Shipping?  Check out this video, created by Nina Philippova our lead technical writer for LiteSpeed, for a step-by-step overview of configuring a Log Ship plan through the LiteSpeed UI.  Please provide any feedback that you may have, we’d love to hear it.  Thanks!

When an issue occurs during your backup and recovery process such as performance spikes, it is extremely beneficial to get as clear a picture as possible of what activities are occurring in your SQL Server environment.  LiteSpeed for SQL Server offers this clarity by logging these activities for immediate analysis and isolation of any bottlenecks.  This video takes you through the process of enabling logging through the LiteSpeed for SQL Server console and will cover four different areas within the product.

• Backup Wizard
• Restore Wizard
• Maintanance Plans
• Console Logging

Special thanks to April Bucher on the LiteSpeed QA team for putting this short video together. 

Sound familiar?

“The SQL Server was performing great yesterday and all of a sudden today the performance is terrible.”

Foglight Performance Analysis has a very powerful feature that allows you compare two different properties of your SQL Server workload.  This scenarios could be:

• Comparing the performance of a database over two different time ranges.
• Comparing the performance of a single stored procedure over two different time ranges.
• Comparing the performance of database A to database B over the same time range.
• etc…

Take a look at this short tutorial and see how the feature could help you in your environment!  Any questions, feel free to comment.

Brent Ozar says, “This is definitely my favorite way to check wait stats performance on my SQL Servers to find out what’s going wrong at a glance.”

If you’re familiar with Foglight Performance Analysis for SQL Server (PA SQL), you know how useful it can be to spend a couple of hours with a product expert.  Whether you have questions about preparing the installation environment, or about how to make the most of your investment in PA SQL, having someone show you the way instead of reading through the manuals is always a welcome alternative. Well, Quest has just initiated a program in conjunction with LeadThem Consulting to offer 2 fully remote, 2 hour engagements with a seasoned PA SQL expert to cover product scoping and sizing, and product usage training.  The cost for each of the workshops is $350, and you can sign up for these at http://www.quest.com/sql-training-leadthem/.

Following the initial rollout of this program, we will be implementing equivalent offerings for Spotlight on SQL Server Enterprise and Foglight for SQL Server.  Stay tuned for updates!

Quest’s new LiteSpeed Engine for SQL Server is a transparent way to compress and encrypt your Microsoft SQL Server backups – without changing your existing backup scripts or maintenance plans.  Instead of calling special stored procedures at backup time, just configure the Engine once and be done with it.  Learn how to set it and forget it in this video by Brent Ozar.

When it absolutely, positively has to be backed up, don’t just rely on one backup file.  Quest LiteSpeed for SQL Server can write database backups simultaneously to two places.  This protects DBAs from other users accidentally deleting backup files, from crashed file servers, and from corrupted tapes.  Learn how to mirror your LiteSpeed backups in this video by Brent Ozar:

Need to send a SQL Server backup to someone, but it’s too big and you’re not sure if they use LiteSpeed?  Quest LiteSpeed for SQL Server can back up databases into a single executable file.  You can send that file to someone else or copy it to another server, and that other server doesn’t need LiteSpeed.  The person doing the restore doesn’t even have to understand how SQL Server works – LiteSpeed takes care of all the details.

In this video, Brent Ozar shows how both the backup and restore process works: