Press "Enter" to skip to content

Category: Virtualization

VM Firmware and Windows Secure Boot

David Klee gives us the lowdown on firmware specifications in virtual machines:

The Register is reporting that future versions of Windows Server OS is going to require the TPM 2.0 chip and Secure boot enabled by default. Secure boot is quite helpful to validate that servers boot into trusted environments. It sounds basic and straightforward, but if your VM administrators are not preparing for this change now, a much-overlooked setting in the hypervisor might backfire and you might not be able to enable this setting. That scenario would be a disaster if your security team suddenly issued a decree stating that you must enable this setting by some date.

Read on to see what this means if you’re using Hyper-V or VMware.

Leave a Comment

Hyperthreading and VMs

David Klee shares some thoughts on hyperthreading in virtual environments:

I recommend leaving the hyper-threaded logical cores enabled in the host BIOS, but not depending on them for performance gains. Hyperthreaded CPU cores, or logical cores, should not be factored into CPU overcommitment rations as if they are full processor cores.

Every task that is triggered inside a virtual machine must be scheduled to run on a physical compute resource. These scheduled tasks must be placed into a scheduling queue inside the hypervisor layer before it gets its time on the physical compute resource. If the hypervisor is overloaded, or if the vCPU scheduling queues are imbalanced from an incorrect vCPU configuration, these queues can grow, and the performance impact on the vCPU performance can suffer.

Click through for an explanation of hyperthreading and David’s guidance on the topic.

Comments closed

How VMware Resource Pools Affect SQL Server

David Klee walks us through the concept of resource pools in VMware:

Resource pools are used to hierarchically partition available CPU and memory resources, and are available for use at the VMware host cluster layer.

To better prioritize certain VMs over others, especially in a highly concurrent VM farm, I recommend leveraging three resource pools for SQL Server-on-VMware environments. Tier-1 can be created with a high value of resources assigned for CPU and memory; Tier-2 is normal; Tier-3 is low. Do not manually specify the amount of shares for each, as this metric will become skewed if compute hardware is added or removed from the host cluster.

Read on to understand why and how, as well as a few more tips around resource pools.

Comments closed

Dynamic Resource Scheduling and SQL Server

David Klee offers some advice on Dynamic Resource Scheduling:

Dynamic Resource Scheduling (DRS) should be enabled for all VMware host clusters, especially those who run SQL Server. It provides for resource consumption load balancing functionality into a host cluster. Consider enabling the DRS load balance based on consumed memory rather than active memory (available as of vSphere 6.7), as the active memory counter for SQL Server-based VMs is not a true representation of memory usage by the SQL Server layer.

Read on for more details.

Comments closed

Improving vCenter Performance Metric Logging

David Klee has some recommendations on settings for vCenter performance metric collection:

The default performance metric statistics collection interval within vCenter is to start rolling up data in an aggregation method starting at just one hour. Much of the data necessary for troubleshooting of performance challenges reported either same day or on the previously day is lost from the vCenter data and forces the administrator to revert to cumbersome and/or time-consuming tooling, such as vRealize Operations Manager. DBAs might not have access to such tools. Hopefully by now they have read-only access to vCenter!

The vCenter performance statistics collection and rollup settings can be customized to provide a longer window of time for critical metrics to be available to the administrator for management.

Click through for some recommendations of aggregation intervals and collection durations to help with virtual machine troubleshooting.

Comments closed

Setting Up a SQL Server Lab with AutomatedLab

Jess Pomfret looks at a very interesting Powershell module:

There is a fantastic PowerShell module called AutomatedLab that can enable you to easily build out a lab for the  specific scenario you need to test. Even better is the module comes with 70 sample scripts that you can start with and adapt to meet your needs.

The module gives you the option to work with Hyper-V or VMWare. I will say most of the examples are using Hyper-V, and that is what I’ll be using also.

For my lab I want a SQL Server 2019 instance joined to a domain, and a separate client machine that I can manage the SQL Server from. On the client I would need to be able to connect to the internet as I want to be able to download PowerShell modules from the gallery easily.

It’s about time for me to rebuild my lab, so I’ll need to check that out.

Comments closed

The Costs of Virtualization

David Klee points out that virtualization, configured correctly, should not harm SQL Server performance much:

A wonderful reader of my blog sent me a note (thanks Jess!) about a single line notation in the latest SQL Server release notes. The notes is as follows.

Running SQL Server on a virtual machine will be slower than running natively because of the overhead of virtualization.

The question was simple. Why would Microsoft add this disclaimer? It was being used as a negative talking point towards SQL Server virtualization, and holding the DBA team back from getting the benefits of virtualization.

David gives us some rough numbers on what that means. Spoiler alert: if you set up your environment right, it’s not much.

Comments closed

Building a SQL Cluster Lab

Ryan Adams has started a series on building a Windows cluster in Hyper-V and layering SQL Server on top of it:

Before we start to build a SQL Cluster Lab, let’s look at the desired result. You will build a 3-node cluster replicating an environment that has two data centers. As a result, the first two nodes will reside in data center 1 and the third node in data center 2. We are creating this architecture because it is the most common architecture I see for Availability Groups. It is multi-subnet and can solve for both HA and DR.

You will notice the domain controller in the middle. That piece is certainly not representative of a production environment. However, we are using it in our lab for several different functions and being a router is one of them.

Part 1 is the only part which is currently up, but this looks like it will be a good one. Go buy a couple more sticks of RAM for your PC and get reading.

Comments closed

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