Best Processor for Server

Selecting the Best Intel Xeon E5-2600 v2 CPU for SQL Server 2012

It is 2016 and some people still think SQL Server cannot be run on a virtual machine. SQL Server can successfully run in a VM but SQL is resource-intensive by nature and so if you are going to virtualize SQL then you simply must adhere to best practices. Not following best practices can be the difference between poor vs exceptional virtual SQL Server performance. Please see my previous blog post on general SQL server best practices as these apply in a virtualized environment also.

Power Management

The physical VM host should be set to high performance in the BIOS to ensure that it is firing on all cylinders which in turn will allow the hypervisor to allocate the abstracted resources as it sees fit.

Power management should always be set to high performance within Windows VMs. Balanced is a setting for laptops which need to reserve power. VMs can have serious performance issues if not configured correctly. In some environments VM power management settings can be controlled by the hypervisor but when resource intensive apps such as SQL server are in play, make sure that Windows power management is set to high performance.

Always Use SLAT Compatible Server Hardware

Although it might not be the case with older hardware, most modern servers have x64 processors which support SLAT (Second Level Address Translation).

VMware and Hyper-V hosts should run 64-bit x64 processors (AMD or Intel). It is absolutely vital that the host processor supports SLAT. SLAT goes by several aliases.

  • Intel calls it Extended Page Tables
  • AMD calls it Nested Page Tables or Rapid Virtualization Indexing

SLAT enables the CPU to maintain the mapping between the virtual memory used by the VMs and the physical memory on the hypervisor host. If the CPU cannot perform this memory mapping then it would fall to the hypervisor to do so. Performance and scalability are both improved by having the CPU perform the memory mapping.

Microsoft studies have proved that SLAT:

  • Considerably reduces the host processing overhead to about 2 percent
  • Reduces the host memory requirements by about 1MB per running VM

Don’t think too much about it – just make sure that the underlying VM host’s hardware supports SLAT.

Do Not Overcommit the VM Host CPU

I cannot stress this point enough. If you overcommit the VM host, and have resource intensive applications such as SQL server running on the VMs on that VM host, then you will encounter performance issues sooner or later. It is not a problem if you have a bunch of low resource usage web / app servers sharing resources as the hypervisor can easily keep up with which VM needs which resources but when you bring resource intensive apps into the mix it is a recipe for disaster.

If your virtualized SQL Server workload is highly intensive, then make sure you are running the latest version of Hyper-V or vSphere as each iteration comes with new maximums for scalability.

Best practice for the initial sizing of a VM, especially one that will host a resource intensive application such as SQL server, is to make sure that the total number of virtual CPUs assigned to the VM does not exceed the number of physical CPU sockets (as opposed to the logical cores) available on host machine.

CPU Ready

This is not something you want to encounter as it is indicative of an overprovisioned VM and/or host. CPU ready is the amount of time a VM is ready (needs) CPU clock cycles on the physical host but has to wait to get time because other VMs are already using the resources.

Calculating ready time can be a pain because it depends on the polling interval for the metric presented on the VM host e.g. 20 seconds (20, 000 milliseconds):

(CPU ready value / 20, 000 ms) x 100% = Percentage performance impact per 20 second interval.

If you extrapolate over time you can quickly see how this would cause performance degradation, especially if running high performance applications such as SQL server.

Ready values 5% per vCPU are a warning and you are likely already experiencing performance degradation.

Without any misconfiguration it is not at all difficult to find CPU ready values of >=10% due to some large VMs with several vCPUs running on a few physical cores or a similar disproportion of vCPU to pCPU.

If the VM itself is overprovisioned e.g. a VM with 8 vCPUs must wait for all 8 pCPUs on the underlying VM host to be free before getting any clock cycles. This is where right sizing comes into pay. If the VM truly needs a large number of vCPUs then by all means add them. If you are sizing for a new application then only add vCPUs as you monitor performance. Windows task manager is not a great indicator of performance in a virtualized environment and so monitor from the VM host side. If all vCPUs are maxed then it likely needs more vCPUs. If not then leave well enough alone. I’ve seen situations where removing vCPUs from a VM actually improved the performance of the applications with databases hosted on that virtual SQL server.

If the VM host is overprovisioned then there are several VMs running on that host which are all competing for resources. If this is the case you should migrate some VMs to other hosts to alleviate the resource contention issues.

The equivalent to CPU ready on Hyper-V is the Perfmon counter Hyper-V Hypervisor Virtual Processor\CPU Wait Time Per Dispatch which is available since Windows Server 2012.

Hyper-threading

Hyper-threading is an Intel technology that exposes two hardware contexts (threads) from a single physical core. These threads are referred to as logical CPUs. It is a common misconception that hyper-threading doubles the number of CPUs or cores. This is simply not the case. Hyper-threading improves the overall host throughput from 10-30% by keeping the processor pipeline busier and allowing the hypervisor more opportunities to schedule CPU clock cycles and so you should definitely take advantage of hyper-threading by enabling it in the BIOS of the VM host machine.

Cores per Socket

NUMA (Non-Uniform Memory Access) allocates each CPU its own local memory. The CPU and memory combined are known as a NUMA node. The advantages of NUMA is that it enables a processor to access its own local memory faster than it would non-local memory. Both Windows and SQL are fully NUMA aware and make scheduling decisions for threads based on the NUMA topology.

vNUMA presents the physical VM host’s NUMA architecture directly to the VM guest OS. The vNUMA topology of a VM can span across multiple physical NUMA nodes. After a vNUMA-enabled VM is powered on, the architecture presented to the OS cannot be altered. This is actually a positive thing because altering the vNUMA architecture can cause instabilities in the OS. This restriction can however cause problems if an attempt is made to migrate the VM to another VM host which has a different NUMA architecture.

vNUMA is enabled by default for VMs which have more than 8 vCPUs (regardless of the combination of sockets and cores which makes up the number of vCPUs in play).

Cable Matters Cable Matters 160021 5-Feet Cat6 Snagless Ethernet Patch Cable, Pack of 5 (Black/ Blue/ White/ Red/ Green)
Personal Computer (Cable Matters)
  • High Performance Cat6, 24 AWG, RJ45 Ethernet Patch Cable provides universal connectivity for LAN network components such as PCs, computer servers, printers, routers...
  • Cat6 performance at a Cat5e price but with higher bandwidth; Future-proof your network for 10-Gigabit Ethernet (backwards compatible with any existing Fast Ethernet...
  • A Category 6 Ethernet patch cable is also referred to as a Cat6 network cable, Cat6 cable, Cat6 Ethernet cable, or Cat 6 data/LAN cable. A wired Cat 6 network is...
  • The connectors with gold-plated contacts, molded strain-relief boots, and snagless molds resist corrosion, provide durability, and ensure a secure connection; Bare...
  • Flexible and durable Cat6 cable with high bandwidth of up to 550 MHz guarantees high-speed data transfer for server applications, cloud computing, video surveillance...
InstallerParts InstallerParts 12Ft Coiled Handset Cord -- RJ22 (4P4C) Connectors -- Black
CE (InstallerParts)
  • The Handset Coil Cord Is Durable and made to Endure Extended Use
  • Connect RJ22 (4P4C) to RJ22 (4P4C) Using the Black Coiled Telephone Handset Cord
  • The Coiled Black Handset Cord is 12ft in Length
Update International Stainless Steel Sauce and Condiment Cups, Set of 36 (3 Dozen)
Kitchen (Update International)
  • Sauce and Condiment Cups, Set of 12
  • Stainless steel construction
  • Perfect for serving sauces and condiments, like ketchup
  • Dishwasher safe
  • 2-1/3 diameter x 1-1/2 H
InstallerParts InstallerParts DSL Filter -- Male to female DSL Filter -- Eliminates interference from DSL Modem
PC Accessory (InstallerParts)
  • INLINE: Simple design from Jack to Phone (RJ11 male to RJ11 female) in bright white color.
  • SOUND CLARITY: Shields devices from excess noise, providing exceptional clarity with devices connected to the DSL inline filter.
  • MULTI-USE: For use with phones, televisions, fax machines, or any other connection with a jack.
  • ONE-LINE CONNECTION: Simply plugs in as an extension to the phone jack. No extra installation required.
  • EXTENSION: Provides an additional 4.4 in. of length to cable.
InstallerParts InstallerParts Cat.5E Junction Box -- 110 Punch Down Type -- Secure Shielded Outdoor Junction Box -- UL Listed
PC Accessory (InstallerParts)
  • HIGH QUALITY DESIGN: Secure outdoor junction box provides durable housing for a cat5e ethernet cable
  • MEETS INDUSTRY STANDARDS: UL/cUL listed; provides most direct connection between two cat5e cables.
  • WIRE GUAGE: The 110 block is designed to use 22 through 26 gauge solid wire.
  • COLOR CODED: 568A/568B color coded.
  • COMPATABILITY: Ethernet cat5e Junction Box is 110 and Krone Punch Down Type

Related posts: