How are my servers doing?

How are my databases doing?

If my boss came in and asked which servers needed more horsepower or which ones could be consolidated together, how would I come up with an answer?

I’ve always found it funny that DBAs and sysadmins don’t have good answers to these questions.  We roll our own monitoring solutions, cobble things together from a bunch of parts, or fork over money to third party vendors to get a picture of how our environment’s doing.

Virtualization admins, on the other hand, have fantastic answers to these questions.  Take this screenshot from VMware vSphere’s management console:

Host Server Utilization Rates

The sysadmin can see a lot of relevant information at a glance: how utilized the host server CPUs are, how utilized the server memory is, each server’s status, and more.  Wouldn’t it be nice to get a simple report like that for SQL Server?  Check out this screenshot from the new SQL Server 2008 R2 CTP:

SQL Server 2008 R2 Utility Explorer

The first thing to notice is the two pie charts: Managed Instance Health and Data-tier Application Health.

Not servers and databases – instances and applications.  That’s your first hint that things are going to be different long-term.

Virtualization Changed Everything, But Took Time

Looking ahead, Microsoft wants us to start thinking of databases as being less connected to physical servers, and to think of our physical servers as a resource pool.  Imagine if databases were self-contained packages that could be moved from server to server – just like virtual servers can be moved from host to host today.

The virtualization push took years to accomplish because there were so many things that had to be handled.  We had to figure out how to handle drivers, how to handle resource sharing, how network segregation and storage throughput would work, and even systems management had to be rethought from the ground up.  It took a long time to get it right, and today virtualization is pushing into enterprises everywhere.

Today, SQL Server faces struggles not unlike the early days of virtualization. There are several factors that cloud the Utopian vision of moving databases around seamlessly:

• Connection strings – our apps call for their data by a specific server name.  If we’re going to abstract servers away, then we need a way to find our data.
• Logins – logins are set at the server level, yet are tied into databases.  If we move a database from one server to another, we have to make sure that the login exists on the new server, has the same password, and has the same level of access rights.  If the application frequently uses a specific login to call the TRUNCATE TABLE command, for example, we need to know it’ll have that same level of permissions on the new server.
• SSIS/DTS packages – these techniques can be used to pipe data in and out of our database servers, and they’re often tied in with local servers.
• Scheduled jobs – as much as I fight developers who want to put jobs on their servers, reality is that I don’t always win.  (Schedulers belong in applications, not databases.)
• Anal retentive DBAs – we know best, right?  We finely tune some of our applications so that the data lives on one set of disks, logs live on another set of disks, and maybe indexes or partitioned data live on yet another set of disks.  If we start shuffling databases around, we’re going to need to abandon that level of control.

Abstracting all of this stuff out of the database architecture isn’t going to be easy, but SQL Server 2008 R2 is starting to take the first step.

The SQL Server Utility: Virtualization for Databases

SQL Server 2008 R2 introduces the concept of the SQL Server Utility: a pool of resources (instances) that host applications (databases).

The Utility is managed by a Utility Control Point: a single SQL Server instance that gathers configuration and performance data.

All of this is visualized through SQL Server Management Studio’s Utility Explorer:

SSMS 2008R2 Utility Explorer

This dashboard shows some basic metrics about CPU use and storage use at both the instance level and the application level.

Down the road – years down the road – this might provide DBAs with the same level of fast reaction times that virtualization admins currently enjoy.  Got a database running out of space?  Move it to a server with more space.  Got an application burning up too much CPU power?  Slide it over to one that’s got the latest and greatest processors.

In order for this concept to work, though, we need to do more than just think differently about our databases; we’re going to need to deploy them differently.  SQL Server 2008 R2 introduces the concept of the Data-tier Application (DAC), a self-contained package with everything our applications need in order to store and process their data.  It’s not going to work for everyone, and in my next post, I’ll talk about what SQL Server 2008 R2′s DACPack means for DBAs.

Continue to Part 2: What the DAC Means for DBAs

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

Last Friday, I did a series of presentations with Jason Hall (Twitter) and Geoff Hiten (Twitter – Blog) on performance tuning SQL Server.  The slide decks are online at SlideShare along with a few other SQLServerPedia presentations:

• Advanced Index Tuning
• Perfmon and Profiler 101

Here’s the links we discussed:

• Filtered Indexes in SQL Server 2008
• Indexing Best Practices notes from Jose Barreto
• Perfmon tutorial at SQLServerPedia
• Index tuning DMV queries at SQLServerPedia
• INCLUDE feature for building covering indexes
• vLaunchPad with VMware blogs – especially Yellow Bricks and vCritical, but remember that vCritical does a lot of MS-bashing so read between the lines.
• Putting SQL Server on Shared or Dedicated Drives on SANs

The archived video/audio copy of the webcast should be online this week, and I’ll let you know when those are up.  In the meantime, here’s a recap of some of the questions that came up outside of the decks:

How do Perfmon memory guidelines change between 32-bit and 64-bit OS’s?

In my presentation, I note that if you’re averaging less than 250mb of free memory, you need to ease off SQL Server’s maximum memory settings.  That guideline holds for both 32 and 64 bit systems.  One of the attendees noted that he wants to see a much higher number (like 1gb free) and I agree, that’s fantastic.  The 250mb is a fire alarm number, though, and the presentation focuses on things that you need to focus right away when you see them.

How do Performance Monitor guidelines change for virtual systems?

Perfmon numbers are nearly meaningless under virtualization.  100% CPU use doesn’t mean that your virtual SQL Server is actually using 100% of the possible CPU power – it means 100% of the available CPU power.  Instead, it might mean:

• You’re hitting the CPU limit that the virtualization sysadmin assigned to your virtual server (yes, they can limit you, and you’ll never know).
• Another guest is using a lot of available CPU power, leaving less for you, and you’re using 100% of what’s available to you.
• And if you’re looking at yesterday’s numbers, you were hitting the CPU limit of the physical CPUs you were on at the time, but you might be on different CPUs now.  The virtualization sysadmin or the virtualization software can magically move your virtual server to another physical host without you knowing.  (It’s called vMotion for VMware, or Live Migration for Microsoft Hyper-V.)

Instead of using percentage metrics, I recommend that DBAs focus on time-focused metrics and (some) queue-length metrics for virtual servers.  For storage monitoring, focus on seconds per read and seconds per write, because those tell you if the storage is responding fast enough.  For processor and memory, focus on queue length metrics, because those tell you how many things are waiting on those types of resources in order to execute.

Should TempDB be placed on local disk or on the SAN?

If it’s currently on local disk, use the Perfmon monitoring to determine if that disk is currently a bottleneck.  If it is, and if you’ve got free performance (not just space, but performance) available on the SAN arrays, then yes, you should consider moving it to the SAN.

Keep in mind that if you’re doing SAN-to-SAN replication, you don’t want to put TempDB on the same replicated array that you’re copying to a remote datacenter.  Use a different array that isn’t being replicated.

In VMware, how many CPUs should I use for SQL Server?

As few as you can get away with.  If the virtual server is set up with 4 virtual CPUs, then VMware will wait until there are four available cores before it gives any CPU time to the guest OS – even if the guest OS only needs a single thread.  I’ve personally experienced faster performance when turning down the number of virtual cores assigned to a server, and that lines up with what’s been seen by VMware forum users.  Avoid using 4 virtual CPUs unless you’re actually saturating 2 virtual CPUs, and don’t try running too many 4-virtual-cpu guests on a single host.

This is counter-intuitive for us power-hungry DBAs, who like to assign every server 4 virtual CPUs just because we can.  Ease off, though.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

I talked about why sysadmins want to virtualize servers, and today it’s time to talk about why you as a DBA or developer might want to virtualize some of your SQL Servers.

Virtualization Means Cheap High Availability

Yo momma was an early adopter. That's right - you're adopted.

In a perfect world, every SQL Server would be a cluster on a SAN with instant failover.  This isn’t a perfect world, and we can’t afford to implement that level of protection for every server in the shop.

Virtual SQL Servers provide a level of high availability without the expense and hassle of clustering.  In my last post, I explained that sysadmins love virtualization because they can easily move guest servers around from one piece of hardware to another.  As a DBA, I love it for that same reason: suddenly, if the underlying hardware fails, I’m not sweating bullets.  If the RAID card fails, if the motherboard fails, if the memory fails, if the network card blows chunks, you name it, it’s not my problem.  The sysadmin simply boots up my SQL Server on another virtual host, and I don’t have to deal with screaming users.

I especially like this option for development and QA servers.  Management is rarely willing to implement expensive high-availability setups for development environments, but when the dev server goes down, the developers and project managers are kicking my door down.  The PM’s scream about how there’s so many expensive developers sitting around idle waiting for the server to come back up.  Virtualization avoids this problem altogether.

Virtual SQL Servers can React Quickly to Changing Needs

Capacity planning is hard work.  How much speed do we really need for a new application?  What happens if our estimates are wrong, and we suddenly need a whole lot more oomph?  What happens if we overbought, and we’ve got all this expensive hardware sitting around idle because the application never caught on with end users?

Virtual servers can be easily scaled up or scaled down.  Simply shut the virtual machine down, change the number of CPUs and memory it has with a few mouse clicks, and boot it back up again.  Presto change-o.

We can even move storage around.  Say we initially decided to put the server’s data on a cheap RAID 5 SATA array, and now our users have fallen in love with the application and they’re adopting it like crazy.  We can shut it down, move the virtual hard drives over to a faster RAID 10 SAS array, and boot it back up again.  Presto change-o, rapid storage performance improvements without long downtime windows to backup/restore.

I loved this approach because it let me keep old SQL Servers around as long as necessary.  For example, we decommissioned an old help desk database server, but I told the help desk folks they could keep it around as long as they wanted.  It hardly used any resources at all, because nobody queried it unless they had a question about an old help desk ticket that didn’t get transitioned correctly to the new system.  I set the server up with one virtual CPU, 1gb of memory, and put it on cheap RAID 5 SATA.  The end users loved me because I could provide the service for no cost.

Virtual Server Performance Isn’t That Bad

Virtualization has a really bad reputation because in the beginning, the software really did suck and admins didn’t understand how to use it.  Raise your hand if you installed VMware Workstation on your desktop years ago, and you tried to run multiple operating systems on a machine that barely had the horsepower to run Internet Explorer.  I was there, and I know how it was.

Things have changed: the software’s gotten much better, memory became absurdly cheap, and there’s resources to help use it better.

Busted!

The 2008 book Oracle on VMware by Bert Scalzo is a good example.  Bert’s one of the Oracle experts at Quest, and he wrote this to help DBAs understand how to implement database servers in a virtual environment.  It does a good job of explaining enough about virtualization to make a DBA comfortable with the technology and avoid some common pitfalls.  Years ago, books like this weren’t available because the technology was changing so fast that best practices were wrong as fast as they were written.

You can still get really bad performance with virtualization, just as you can get really bad performance with SQL Server on native hardware.  But follow a few basic guidelines like avoiding oversubscription, properly configuring networking & storage, and using the right OS configurations, and you can get perfectly acceptable performance.  Yes, you’re still going to lose a few percent of overhead – but with today’s redonkulously fast CPUs and memory, I’m more concerned about optimizing T-SQL code than I’m concerned with losing 5% of my CPU speed due to virtualization.  It’s all about priorities, and that small performance loss is just one piece of the picture.

Virtualization is All About Give and Take

The economy has gone down the toilet.

To make matters worse, it’s one of those pay toilets, and US Treasury Secretary Timothy Geithner has run out of quarters.

Invisible Quarters

Until he’s willing to go through the Oval Office couch looking for more change – and I don’t blame him for being hesitant, because Bill Clinton left some disgusting stuff in that couch – we’re going to have to buckle down and find ways to save money.  As I mentioned in my last post, we can either cut hardware costs, or we can cut people costs.

Virtualizing non-mission-critical SQL Servers gives us the chance to cut costs without cutting service levels, and furthermore, gives us the chance to show management that we’ve got skin in the game.  If we insist that every single SQL Server is too important to be virtualized, and that there’s no way we can save money, then management just might look at us as part of the problem.  Be part of the solution: be proactive, find SQL Servers with low utilization levels, and offer them up as the first phase.

My approach was to start with the SQL Servers that I frankly didn’t really want to survive on their own anyway.  I had one particular application that had always demanded its own standalone SQL Server with no other applications on it.  The server was barely used, and it was a completely out-of-date and unsupported version of SQL Server.  The application manager wouldn’t allow me to patch it, wouldn’t upgrade to a newer version, and didn’t have the budget to move to newer hardware.

Virtualization administrators have slick tools like VMware Converter and Vizioncore vConverter to automate the physical-to-virtual (P2V) process.  These solutions take a backup of the physical server and clone it into a brand-new virtual server.  When the virtual server starts up, the new virtualization drivers are installed automatically, and the server is up and running smoothly without time-intensive application reinstalls.  I’ve blogged about virtualizing SQL Servers with P2V and I’ve performed it many a time – it’s a great way to get rid of hardware dinosaurs.

Even better, this whole process doesn’t require work from the DBA.  The time and effort is all put in by the virtualization administrator.  If your sysadmin is dying to try virtualizing a SQL Server, you can point them at your newly chosen dinosaur and tell ‘em to have at it – you don’t have to be involved.  That’s the best of both worlds: management will see you as a part of the solution, but you won’t actually have to do any work.

Sounds Great! Let’s Virtualize SQL Server Today!

Well, it’s not all unicorns and rainbows: there’s some reasons why you might not want to virtualize SQL Server, and I’ll talk through those next.

Continued: Why You Shouldn’t Virtualize SQL Server

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me: