Date parsed: 11/10/2007 16:40:12
Date: Thu, 11 Oct 2007 23:40:12 -0700

"JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:jjttg3lgsnegibnm38qsoumprrqfr9lgmt@4ax.com...
> Had a report today from our data warehouse guy, he'd been torturing
> the server for a couple of weeks, tuning queries to get some summaries
> built nightly from a base of about 34m records.
>
> Server is running on VMWare on 64bit machine, 64bit SQLSerer 2005, sp1
> I believe, all disks currently virtual on VMFS RAID5, database on
> bulk-logged recovery. Well, he dumped the log for the first time in a
> week, and tells me his queries (that is, 17m inserts and 7m updates)
> from that point ran about 4x faster.
>
> I'm mystified by this, unless it's some effect of VMWare. The log was
> auto-expanding expanding by 100mb at a pop, so he might have been
> hitting a couple of dozen of those per run, but that wouldn't seem to
> give such a large result, ... would it?
>
> One query went from 16 minutes down to 3 minutes.
>
> Any other major factors anyone can suggest?
>
> Any general comments about SQL Server on VMWare?

What you don't say clearly is what is this result being compared against?
Was the 16 minute measurement made on a discrete server running 64-bit
Windows? What is your baseline? In any case, if you really want to get a
precise answer, you need to find where was the bottleneck on the old
hardware (it was probably either CPU or disk), and then find the reason why
that bottleneck isn't there with the virtualized VMWare implementation.

My comment is not specific to SQL Server, but in our own case we have seen
dramatic performance increases running 32-bit virtual machines on a 64-bit
Windows 2003 host OS with VMWare Server. I strongly suspect that the
reason for this is the large number of disks involved in file I/O for every
virtual disk.

The way we have implemented things we have two primary storage systems, both
using large numbers of 2.5" SAS drives, and each of those arrays is attached
to a separate hardware controller. We create a single large RAID 6 array
spread across something like 12 drives on each controller. We then create
a system drive for the host computer on the boot controller, and use Veritas
Storage Foundation to mirror over to the other array. Then we create a
large data volume on the remaining space of the large array, and then mirror
over with Storage Foundation to the other array. That gives us the
ability to stop the virtual machines, then split off either the system or
data volume on the host and back up to tape, then rebuild or rejoin it to
the primary online volumes after backup. Having something like 24 drives
working for every single virtual file system attached to every virtual
machine really gives amazing performance. Even older Windows 2000 virtual
machines just boot up in very short order.

So far with seven virtual machines we are way under 20% CPU utilization and
disk isn't close to being a bottleneck. Since disk is so underutilized,
booting any virtual machine puts many of those 24 available drives to work
for the virtual machine, almost as if it owned those disks exclusively.

Virtualization for PC operating systems is definitely the most important
development in PC based computing in 10 years. It's a very very big deal.

--
Will

Date parsed: 18/10/2007 14:53:23
Date: Thu, 18 Oct 2007 21:53:23 -0700

Let me say it again:

>> Let's just assume that nothing changed but what I said changed.

As this is certainly the case.

Josh


On Mon, 15 Oct 2007 23:49:01 -0700, John Bell
<jbellnewsposts@hotmail.com> wrote:

>Hi Josh
>
>So what was this query tuning exercise?
>
>If you have worked the virtual server then I would expect VM ware to
>allocate more resources to that VM if it can, have you monitored the actual
>resources available against those configured? I assume this is not the only
>VM on the system, therefore what was happening on those could influence the
>behavior.
>
>John
>
>"JXStern" wrote:
>
>> On Fri, 12 Oct 2007 00:00:00 -0700, John Bell
>> <jbellnewsposts@hotmail.com> wrote:
>>
>> >Hi
>> >
>> >From you statement it seems you don't think this is to do with tuning the
>> >queries?
>>
>> Code unchanged. Data unchanged.
>>
>> >Have you restarted the VM? Defragged the VMs discs or the underlying disc?
>> >Changed anything on the host server? Are resources fixed for the VM or shared
>> >to a maximum?
>>
>> Let's just assume that nothing changed but what I said changed.
>>
>> Josh
>>
>>
>>