A SQL Server Hardware Tidbit a Day – Day 29

By: Glenn Berry
Posted on: April 30, 2013 9:48 am

For Day 29 of the series, I will talk about AMD Turbo CORE technology. AMD Turbo CORE is a technology that was first introduced in the AMD Phenom II X4 desktop processor, but the way AMD implemented it in the Bulldozer family and Piledriver family of processors is greatly enhanced. AMD Turbo CORE is similar to Intel Turbo Boost technology in concept (although AMD claims that it works better).  According to AMD:

AMD Turbo CORE is deterministic, governed by power draw, not temperature as other competing products are. This means that even in warmer climates you’ll be able to take advantage of that extra headroom if you choose. This helps ensure a max frequency is workload dependent, making it more consistent and repeatable

AMD Turbo CORE allows individual cores in the processor to speed up from the base clock speed up to the TDP level, automatically adding extra single-threaded performance for the processor. Conceptually, it is the opposite of AMD PowerNow! technology. Instead of trying to watch for usage patterns and lowering the processor core speed to try to reduce power consumption, Turbo CORE is watching the power consumption to see how high it can move the clock speed up.

This feature, which is new to AMD server processors, allows individual cores to use the extra power headroom between average and maximum power, turning it into more clock speed. Bulldozer implements a significantly more aggressive version of this capability than the AMD Phenom desktop processor. Should the processor get too close to the TDP power limit, it will automatically throttle back somewhat to ensure that it is continuing to operate within the specified TDP guidelines. This allows for significantly higher maximum clock speeds for the individual cores.

AMD has stated that Bulldozer will boost the clock speed of all 16 cores by 500MHz, even when all cores are active with server workloads. Even higher boost states available with half of the cores active, anywhere from 700Mhz to 900MHz. With the Bulldozer and Piledriver processors you see processors marketed with a base and a maximum frequency, base will reflect the actual clock speed on the processor and max will reflect the highest AMD Turbo CORE state.

Just like with Intel Turbo Boost technology, I think this is a very beneficial feature that you should take advantage of for database server usage. I don’t see any controversy here (such as with hyper-threading). Since Microsoft changed over to core-based licensing for SQL Server 2012, it is much less practical to choose an AMD processor (especially for an OLTP workload) because of their high physical core counts and low single-threaded performance.

One scenario where an AMD-based database server could make some sense would be for a dedicated OLAP server, using SQL Server 2012 Business Intelligence Edition, with server-based licensing. Having lots of physical cores without having to pay a huge amount for your SQL Server 2012 licenses is not a bad scenario.

Posted in: AMD, Fujitsu, Processors
Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 28

By: Glenn Berry
Posted on: April 30, 2013 8:22 am

For Day 28 of this series, we are going to talk about some factors to consider if you are thinking about building a desktop SQL Server 2012 system for development or testing use. I get lots of questions about this subject, and I have been thinking about it some anyway, hence today’s topic.

In many organizations, old retired rack-mounted servers are repurposed as development and test servers. Sometimes, old retired workstations are used for this purpose. Quite often, these old machines are three to five years old (or even older). For example, you will often find old Dell PowerEdge 1850, PowerEdge 6850, and PowerEdge 1950 servers being used for this purpose. These vintage machines are about four to seven years old, and long out of warranty. Their performance and scalability is quite miserable by today’s standards, even compared to a modern desktop.

For example, a Dell PowerEdge 1850, with two Intel Xeon Irwindale 3.0GHz processors and 8GB of RAM has a 32-bit Geekbench score of about 2250. A Dell PowerEdge 6800 with four Xeon 7140M 3.4GHz processors and 64GB of RAM has a 32-bit Geekbench score of 5023. A newer Dell PowerEdge 1950 with two Intel Xeon 5440 processors and 32GB of RAM will have a 32-bit Geekbench score of about 7500. For comparison, my current main workstation has a 22nm Intel Core i7-3770K processor with 32GB of RAM and a 512GB OCZ Vertex 4 SSD. This system has a 32-bit Geekbench score of 12713.

My argument is that in many situations, given a very limited hardware budget, it may make more sense (for development and testing) to build or buy a new desktop system based on a modern platform rather that using relatively ancient “real” server hardware. Your main limiting factors with a new desktop system will be I/O capacity (throughput and IOPS) and memory capacity, but there are some ways around that..  You should be able to build or buy a very capable test system for less than $1500.00, perhaps far less, depending on how you configure it.

Your two main good choices right now are a 22nm Core i7 Ivy Bridge (using a Core i7-3770 or i7-3770K processor) with an Z77 chipset-based motherboard, or a slightly less expensive Core i5-3570 or i5-3570K processor. The Core i7 will have four cores plus hyper-threading, while the Core i5 will have four cores, but no hyper-threading. Either one of these systems will support up to 32GB of RAM, which is how much you should get (since desktop DDR3 RAM is so affordable).

You need to look at the motherboard features and specifications closely to make sure you get what you need without paying too much for unnecessary features. You want to get a motherboard that has as many SATA ports as possible (preferably newer 6Gbps SATA III ports) with hardware RAID support if possible. At the same time, you don’t really need the premium gaming (such as SLI or Crossfire support) and over-clocking features in a top-of-the line motherboard. The entry level motherboards will usually have fewer SATA ports, which is a good reason to go a little higher in the lineup. You can also buy inexpensive PCI-e SATA III expansion cards to add even more SATA ports. You also want to make sure to get a motherboard with four memory slots, since some entry-level motherboards will only have two slots.

Depending on your motherboard vendor, you might run into driver issues with Windows Server 2012. The problem is not that there are no drivers, but the fact that the motherboard vendors sometimes wrap the actual driver installation programs in their own installation programs that do OS version checking that fails with Windows Server 2012 (since they assume you will be using Windows 7 or Windows 8).

You can buy a large, full tower case, with lots of internal 3.5” drive bays. Then you can buy a number of 1TB Western Digital Black 6Gbps hard drives and/or some consumer grade SSDs, depending on your needs and budget. This will let you have a pretty decent amount of I/O capacity for a relatively low cost. Very fast consumer SSDs are now available for less than $1/GB of space, so you can probably find a way to afford one or more of them for your system.

If you can wait until early to mid June, the 22nm Intel Haswell processors will be available. These will require a new motherboard, but will give you about 5-10% better single-threaded CPU performance at the same clock speed as Ivy Bridge, along with a few other benefits.

Posted in: Dell, Hardware, Haswell, Intel
Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 27

By: Glenn Berry
Posted on: April 29, 2013 1:12 pm

For Day 27 of this series, I am going to talk about Power Management and its effect on processor performance. I have written about this subject a couple of times before, here and here. Other people, such as Paul Randal (blog|Twitter) and Brent Ozar (blog|Twitter) have written about this subject here and here.

Power Management is when the clock speed of your processors is reduced (usually by changing the processor multiplier value) in order to use less electrical power when the processor is not under a heavy load. On the surface, this seems like a good idea, since electrical power costs can be pretty significant in a data center. Throttling back a processor can save some electricity and reduce your heat output, which can reduce your cooling costs in a data center. Unfortunately, with some processors, and with some types of SQL Server workloads (particularly OLTP workloads), you will pay a pretty significant performance price (in the range of 20-25%) for those electrical power savings.

When a processor has power management features that are enabled, the clock speed of the processor will vary based on the load the processor is experiencing. You can watch this in near real-time with a tool like CPU-Z, that displays the current clock speed of Core 0. The performance problem comes from the fact that some processors don’t seem to react fast enough to an increase in load to give their full performance potential, particularly for very short OLTP queries that often execute in a few milliseconds.

This problem seems to show up especially with Intel Xeon 5500, 7500 (Nehalem-EP and EX), Intel Xeon 5600, E7 series processors (Westmere-EP and EX families) and with the AMD Opteron 6100, 6200, and 6300 series (Magny Cours, Bulldozer and Piledriver families). Much older processors don’t have any power management features, and some slightly older processors (such as the Intel Xeon 5300 and 5400 series) seem to handle power management slightly better. I have also noticed that the Intel Sandy Bridge-EP processors seem to handle power management a little better than the Nehalem and Westmere did, i.e. they don’t show as noticeable of a performance decrease when power management is enabled.

Basically, you have two types of power management that you need to be aware of as a database professional. The first type is hardware-based power management, where the main system BIOS of a server is set to allow the processors to manage their own power states, based on the load they are seeing from the operating system. The second type is software-based power management, where the operating system (with Windows Server 2008 and above) is in charge of power management using one of the standard Windows Power Plans, or a customized version of one of those plans. When you install Windows Server 2008 or above, Windows will be using the Balanced Power Plan by default. When you are using the Balanced Power Plan, Intel processors that have Turbo Boost Technology will not use Turbo Boost (meaning that they will not temporarily overclock individual processor cores for more performance).

So, after all of this, what do I recommend you do for your database server? First, check your Windows Power Plan setting, and make sure you are using the High Performance Power Plan. This can be changed dynamically without a restart. Next, run CPU-Z, and make sure your processor is running at or above its rated speed. If it is running at less than its rated speed with the High Performance Power Plan, that means that you have hardware power management overriding what Windows has asked for. That means you are going to have to restart your server (in your next maintenance window) and go into your BIOS settings and either disable power management or set it to OS control (which I prefer).

Posted in: AMD, Intel, Sandy Bridge-EP
4 Comments