Introduction
At VMworld 2008 in Las Vegas several of us in
our virtual performance team met with a variety of customers to talk
about Microsoft SQL Server. We already had a large base of customers
running very many SQL Server DBs on our products and we wanted to
collect information on the challenges posed in the process of
virtualizing this critical workload. We were pleased to see that ESX
Server handled SQL VMs with excellent performance. But, for many
customers, the first efforts at virtualizing SQL didn't yield
high-performing SQL VM. After careful investigation and many, many
discussions we've started to put together the puzzle as to where SQL
Server performance problems come from. This page will document these
common problems, borrowing slides from our presentations on the
subject.
Virtualizing SQL: The Checklist
We've talked with
dozens of customers in the past months to document the issues that
resulted in poor SQL performance. Happily, none of the issues were due
to underlying technologies. Here is a list of issues and an explanation
of the impacts. These items are roughly listed in the order of
decreasing likelihood of occurrence.
Item 1: Configure Storage Correctly
Storage
configuration problems are the number one cause of SQL performance
issues. Usually these problems arise because the DBA requests a
virtual disk of the VI admin, the VI admin places the VMDK on a LUN
that may or may not meet the DBA's performance needs. For instance:
- VMs' VMDK files placed on VMFS volumes without enough spindles.
- Many VMDK files placed on a single VMFS volume which could use more spindles.
- Database and log files placed on the same LUN which, you guessed it, could use more spindles.
This may be obvious to some, but this problem occurs again and again.
The VI administrator should be aware of a few technical items that can
help understand and avoid this problem:
- Based on the IO
demands of the DB files, a certain number of spindles should be
guaranteed to this file. This means that its VMDK must be placed on a
VMFS volume to accout for the SQL Server's demands and all of the other
demands on that volume.
- Mixing sequential activity (such as log
file update) and random activity (such as database access) results in
random behavior. This means that the LUN configuration in the
pre-virtual physical environment may not be sufficient for the
consolidated environment. This is discussed some in Storage Performance: VMFS and Protocols.
- When
storage isn't meeting the SQL Server's demands, the device latency or
kernel latency (queueing time) will increase. Read up on these
counters in Storage Performance Analysis and Monitoring.
Item 2: Use Recent Hardware
Often companies that are dipping their metaphorical toes into
virtualization want to run proof-of-concept (POC) experiments to verify
that the virtual platform can meet their performance expectations. But
its surprising how many times these experiments are run on older,
poorly-performing hardware. Presumably the shiny, new systems were in
use for production applications so only the mothballed, cobweb-covered
servers from a previous generation were available for the POC. This
causes many problems. Check out this slide from a talk on SQL Server at VMworld Europe 2009:
The
slide points out a couple of things. First, the larger caches and
shorter pipelines on newer Intel processors results in a considerable
drops in performance overheads. Second, the latency of the VMEXIT
instruction, which determines the amount of time it takes to transition
from the VM to the VMkernel, has shrunk by a large amount with
subsequent generations of hardware. And don't forget the other
additions from Intel and AMD such as hardware assisted memory
management and IO virtualization.
Item 3: Follow SQL Server Best Practices
Microsoft has kindly provided a
web page of best practices for SQL Storage configuration. These be practices should still be followed when configuring your virtual SQL deployments!
Item 4: Configure VM Identically to Native and Run The Right Test
For
many SQL Server POCs the goal is to measure the VM's ability to
perform, with respect to the virtual platform. If this comparison is to
be performed, its critical that the VM be configured identically to
the physical hardware. Obviously this means that the VM should be run
on the same hardware using identically configured LUNs. Its also
important to ensure that the VM has the same number of vCPUs and amount
of memory as the physical baseline. This means restricting the number
of pCPUs and amount of memory with NUMPROC and MAXMEM, respectively, in
boot.ini.
It also means that the test being applied should be
understood. If a benchmark is chosen that uses a very small database,
the content will be cached and the storage system won't be used. This
can skew the results and produce recommendations not consistent with
production deployments. Here is another slide from the same VMworld
Europe 2009 presentation detailing some of what we know about the SQL
Server benchmarking alternatives:
We at VMware prefer DVD Store.
Item 5: Use VMware's ESX Server
VMware's hosting products, VMware Server, VMware Workstation, and even
VMware Fusion, are all capable of running SQL Server. But if the
database is going to be run in production on enterprise-class hardware,
use VMware's enterprise-class hypervisor: ESX Server. These products
are not often confused by the initiated but rogue members of large
companies often run off-the-books proof-of-concept experiments on
VMware's hosted products. When they produce results they don't like,
the results get spread throughout the company which can slow the
virtual deployment.
Consider the following data, again from the VMworld Europe 2009 SQL Server presentation:
This
information is getting a bit dated now, as it was performed years ago
on ESX Server 3.0. But the point stands: before believing results
claiming that "VMware cannot run SQL Server" its worth investigating
the platform used to generate the results.
Item 6: Understand Memory Management and Configure Correctly
Database
performance is heavily dependent on the amount of memory available.
Almost without exception, providing more memory to SQL Server will
improve performance. However, if that memory is coming from a host that
is already over-committed or is being provided through workarounds to
32-bit limitations, performance may suffer. Here are a few keys for SQL
Server memory management:
- If more than 3 GB is desired, use 64-bit versions of the OS and application.
- If
memory is over-committed on the box, set reservations for
performance-critical SQL Server VMs to guarantee that those VMs' memory
isn't ballooned or swapped out.
- If SQL Server's "lock pages in
memory" parameter has been set, provide set the VM's reservations to
the amount of memory in the VM. This setting can adversely interfere
with ESX Server's balloon driver. Setting reservations will stop the
balloon driver from inflating into the VM's memory space.
Item 7: Align Disk Partitions
This
item is really a special but very important case of item two, follow
best practices. Partition alignment can impact storage performance
which can be critical to some SQL Server VMs' performance. See VMware's
paper on partition alignment for more information on this.
