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…

Most DBAs don’t read blogs.

They have “real jobs” that don’t afford them the time to surf the web, improve their training, or meet like-minded SQL Server professionals who want to help. When I talk to them about the power of the community and all this free help that’s available, they’re often completely surprised.

I think it has to do with the lonely nature of the DBA career. We usually stumble into this job by accident. We start as developers or network administrators, and for some odd reason we end up managing a SQL Server because nobody else is doing it. We tinker around with it, learn a lot of lessons the hard way, and struggle finding good training.

How Quest and SQLServerPedia Are Making a Difference

At the beginning of this year, we announced that SQLServerPedia would offer blog syndication.  We knew that a lot of bloggers were writing top-notch material, but they weren’t getting the exposure they deserved.  We wanted to help bloggers get their work to a wider audience.

Now, we’re kicking it up a notch.  Here’s the CPU diagnostics screen inside Quest Spotlight on SQL Server v6, and check out the links at the bottom right:

Quest Spotlight on SQL Server Enterprise

When you’re trying to troubleshoot a complex issue like CPU bottlenecks due to insufficient plan cache reuse, or too many adhoc queries running, you need help.  So when you click on those links….

SQLServerPedia Search Results

You’re introduced to community members, bloggers, wiki authors, and other folks who want to share their knowledge with you.

This is a completely new way that syndication pays off for bloggers. When you cover topics users don’t understand, you can show up on end user screens everywhere.  We’re only including our syndicated blogs in this search.

How Bloggers Can Benefit

In my Syndication FAQ for Bloggers, I talked about some ways you can leverage syndication to bring more readers to your site. These tips include:

• Include links to your other posts. When someone’s reading one of your posts, that’s your chance to bring them deeper into your site. For example, in this very blog post just a couple of lines above, I linked to my own syndication FAQ, and I’m going to do it again in a second.
• Include sample code. If you’re discussing table partitioning, for example, include the scripts to demonstrate what you’re talking about. The more scripts you include, the more likely someone will stumble across your blog entry when they’ve got questions about a particular command.
• Toot your own horn. If you’re a consultant and you happen to specialize in the area you’re blogging about, include a footer on every post with links to contact you for more information. FeedBurner makes this particularly easy.
• Include affiliate links to books. If you’re a big fan of a particular book to dive deeper into the blog post’s subject matter, include an Amazon Affiliate link to buy the book. You get paid 4% of the Amazon purchase, and if you’re an author, this is above and beyond your normal cut of the proceeds.
• Read your web statistics reports. Every now and then, dig into your reports to find out if one of your posts has become popular. If it has, update it to include more links to your other posts, as I discussed in my Buried Treasure Blog Posts article.

To read more tips like this, check out my Syndication FAQ for Bloggers. See how I did that?

Suggested Topics for Maximum Exposure

If you’d like some ideas on topics to write about, here’s a sampling of the keywords used as SQLServerPedia search links:

• Adhoc Queries
• Blocking
• Buffer Cache
• CPU Bottleneck
• Memory
• Performance Counters
• Plan Cache Reuse
• Procedure Cache
• Query Optimization
• Query Plan Hash
• Query Plan Parallel
• Query Plan Sort
• Query Tuning

If you wanted to get more exposure to more readers, you might look for keywords with less competition.  You could hit those links, see what kinds of results they bring back, and figure out how you could write something better.

When writing, keep in mind that SQLServerPedia can’t syndicate posts about third party products – and that includes Quest’s own products. SQLServerPedia has a very firm editorial policy because we focus strictly on things you can do with the native SQL Server tools. Sharp-eyed readers will notice that this particular post isn’t syndicated to SSP, for example, but it IS syndicated somewhere else – and I’ll talk more about that soon!