Press "Enter" to skip to content

Category: Virtualization

Understanding Power BI Data Virtualization Queries

Gerhard Brueckl walks us through a few examples of queries Power BI makes when virtualizing data:

Even though this query only touches two different data sources, it is a good way to analyze the queries sent to the data sources. To track these queries I used the built-in Performance Analyzer of Power BI desktop which can be enabled on the “View”-tab. It gives you detailed information about the performance of the report including the actual SQL queries (under “Direct query”) which were executed on the data sources. The plain text queries can also be copied using the “Copy queries” link at the bottom.

Read on for the queries and for Gerhard’s analysis.

Comments closed

Beware CPU Oversubscription with SQL Server

Monica Rathbun shares a tale of terror:

Recently I had a client complain of chronic high CPU utilization. The performance of their SQL Server had degraded, and it appeared to be related to higher than normal CPU utilization in conjunction with symptoms of unresponsive user queries.  The root cause was twofold—a third party hosting provider had overallocated virtual processors on the physical host where the virtual machine (VM) running SQL Server was residing, as well as a recent upgrade from a version of VMWare that was not patched for Spectre and Meltdown. The host had 16 physical cores and was hyperthreading (making it effectively 32 cores) until the hosting provider patched from VMWare 5.5 to a newer release (we believe 6.5) which was required for Meltdown and Spectre processor vulnerabilities. This patch disabled hyperthreading from the hypervisor to mitigate the security risk from speculative execution. Note, this patch is over a year old and a critical security risk; most software vendors (including VMWare) put this out as an immediate requirement after the announcement of the vulnerabilities.

Given this was a virtual machine, it shared a physical host with many other VMs; this is a very common configuration. However, this host was VERY overallocated.  As mentioned above, there were 16 cores–however 61 additional vCPUs had been allocated to other machines. That’s 4.3 times the number of CPUs available for allocation.  The screenshot below shows this singular Host, highlighting the vCPUs allocated.

So, uh, that’s a bad thing. Monica explains in detail why exactly it’s a bad thing, which is helpful when you’re trying to explain to the server admin why it’s a bad thing. CPU oversubscription can work for things like dev boxes or web servers, where they typically aren’t anywhere near 100% utilization. It does not work at all for busy database servers.

Comments closed

Optimizing SQL Server Workloads On VMware

Jeff Mlakar shares a few tips on hosting SQL Server via VMware virtual machine:

Why is Over-Allocating a VM Bad?

Why is is so important to allocate just the right amount of resources for your guest VM running SQL Server? We all know the problem with an undersized system; however, what about an oversized system? What problem could that be? Here are a few common issues to consider:

  • Over-allocating CPU causes poor resource utilization across all the guest VMs

  • Over-allocating memory unnecessarily increases memory contention and overhead on other guest VMs

  • Having more vCPUs assigned to the VM can have an impact on licensing.

There are some good points in the post, so check it out.

Comments closed

Configuring Disk Controllers In VMs

David Klee explains how you can get a performance improvement in high-I/O virtual machines:

Both Hyper-V and VMware’s default controller emulates the LSI Logic SAS controller, because that’s what is built into Windows driver storage, and *just works* without having to do anything fancy.

But… it’s not necessarily there for speed. It’s there for compatibility so that you can boot up a VM without having to deal with extra drivers.

VMware created a driver a while back that comes with the VMware Tools package called the Paravirtual SCSI controller, and it gives a 10-30% bump in performance (depending on the speed of the underlying storage) because it’s built for speed from the beginning. It’s just not native to Windows, so I don’t personally feel comfortable using it for the C: drive controller unless required. You can change the controller type for these controllers, so we use it by default for non-OS SQL Server object drives.

Definitely worth the read.

Comments closed

Getting Fancier With VM Creation

Raul Gonzalez shows how to spin up a Hyper-V VM using Powershell:

There are a lots of command to create or manipulate VM’s and I’m still only scratching the surface, but although I’m not a PS person, I have to admit that every time I want to do something, I find relatively easy to find a powershell command or a script for it, so I like it.

For instance, creating new virtual machines it’s a simple as one command

New-VM

And that’s only the beginning, we can add the different virtual hardware like in the UI, Drives, Network Adapters and so on. And then configure memory, CPU and NUMA, etc…

This is the script which I’m more or less running to create my VM’s, this in particular will be a Hyper-V Host itself, so there are a couple of interesting settings I’ll tell you about later.

Click through for Raul’s script.

Comments closed

Spinning Up Hyper-V VMs With Powershell

Andrew Pruski shows us how to enable Hyper-V and create a Windows VM using Powershell:

I’m constantly spinning up VMs and then blowing them away. Ok, using the Hyper-V GUI isn’t too bad but when I’m creating multiple machines it can be a bit of a pain.

So here’s the details on the script I’ve written, hopefully it could be of some use to you too.

Click through for the script; it’s ultimately just a few lines of code.

Comments closed

VMware Configuration For SQL Server

Jeff Mlakar talks about things you want to look at if you’re running SQL Server on VMware:

In a virtual data center CPU is spread across many guest VMs. This is one of the key drivers behind the effort to virtualize – CPU cores mostly sit unused. For example, we can take a host with maybe 48 cores and virtualize many machines that present logically with > 48 cores. The hypervisor can swap in and our cores as it needs based on what the guest VMs are doing. If the baseline for a guest VM is only 10% CPU usage then this is easy. However, when an intense application like SQL Server is virtualized it must have CPU available otherwise performance will suffer noticeably.

Generally for CPU on a guest VM:

  • Reservations on CPU are not often possible but consider them if you data center allows for it.

  • You want more cores than sockets. So if you are aiming for 8 cores you want something like 2 sockets with 4 cores each instead of 8 sockets with 1 core each.

  • If priority can be given to the SQL VM for CPU then change the Shares Resource Allocation from normal to high.

Click through for more helpful hints.

Comments closed

Figuring Out Virtual Sockets And Cores

Denny Cherry looks at a few considerations regarding virtual sockets and cores for a VM running SQL Server:

Standard Edition

You wants 1×6 (one socket, 6 cores) because standard edition will only use the first 4 sockets in a server (up to 16 cores combined). There’s no getting around that.

From a NUMA perspective as long a vNUMA at the Hypervisor is disabled then it doesn’t matter as SQL Server standard edition isn’t NUMA aware (NUMA awareness is an Enterprise Edition feature).

Read on for a more nuanced answer when it comes to Enterprise Edition.

Comments closed