Glenn Berry - Semi-random musings about SQL Server performance

A SQL Server Hardware Tidbit a Day – Day 30

Posted on: May 1, 2013 11:40 am

For the final post in this series, I want to list a few resources that you can use to help stay current with what is happening in the world of server hardware and storage. I really think that a database professional has a professional responsibility to know the details of their database server hardware and storage subsystem, especially for their most important database servers.

Many DBAs do not know much about hardware and storage, while many server and storage administrators don’t know much about SQL Server and the demands that it places on hardware for different types of workloads. Becoming educated and staying up to date is not that difficult, and it will make you a better DBA.

Web Sites and Blogs

AnandTech Enterprise

AnandTech

Tom’s Hardware

Real World Technologies

Transaction Processing Performance Council

Books

SQL Server Hardware book

Professional SQL Server 2012 Internals and Troubleshooting

Pluralsight Courses

Understanding Server Hardware

SQL Server 2012: Evaluating and Sizing Hardware

SQL Server 2012: Installation and Configuration

Posted in: Benchmarks, Hardware, Processors

Be the first to Reply!

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

A SQL Server Hardware Tidbit a Day – Day 26

By: Glenn Berry

Posted on: April 29, 2013 12:49 pm

For Day 26 of this series, I want to talk a little about laptop processor selection (since I get a lot of questions about it). Many DBAs, Developers, and consultants use laptop computers as their primary workstations for working with SQL Server. Even more than an actual database server, you are pretty much stuck with the processor that you initially buy in a laptop (unless you are pretty brave and willing to do some major surgery on the laptop).

Having the “right” processor for your needs is very important in a laptop. Making the wrong choice could mean that you have a lot less processing power or a lot less battery life than you expect. Unfortunately, you cannot usually rely on the sales clerk at Best Buy to give you good advice about which processor to pick for your new laptop.

If you need a new laptop right now (April 2013) you want a 22nm Intel Ivy Bridge processor in your laptop. In most cases, I would recommend a Core i7 model, such as a Core i7-3840QM that has four-cores plus hyper-threading (assuming that you want a larger, desktop-replacement type of laptop). If you are looking at an Ultrabook form factor, you will be stuck with a low-voltage processor such as a Core i7-3537U processor, which has two-cores plus hyper-threading. The U suffix on the processor model number is the giveaway that you are looking at a low-voltage processor, which will give you better battery life but much lower performance.

If you can wait until early June 2013, you should start seeing new laptops with the 22nm Intel Haswell processor. Haswell will have about 5-10% better processor performance at the same clock-speed, but much better integrated graphics performance and much better battery life than Ivy Bridge.

For some comparison results, the Geekbench blog has a post with a number of results for some different models of the MacBook Pro. You can use this to get a rough idea of how much better an Ivy Bridge based machine (Mac or PC) will perform compared to various older processors.

Another important benefit you get with a new Intel Ivy Bridge machine is native 6Gbps SATA III support, which means that you can take advantage of the fastest 6Gbps SSDs. You will also get USB 3.0 ports, which are a huge improvement over USB 2.0 ports (which are usually limited to about 25-30MB/sec throughput).

Posted in: Haswell, Intel

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 25

By: Glenn Berry

Posted on: April 25, 2013 7:58 am

For Day 24 of this series, I want to talk about the recent history of Dell rack-mounted servers, to help illustrate how processor performance and server capacity has dramatically improved over the past seven years.

Back in 2005-2006, you could buy a two-socket Dell PowerEdge 1850, with two hyper-threaded Intel Xeon “Irwindale” 3.2GHz processors and 16GB of RAM (with a total of four logical cores). This was fine for an application or web server, but it did not have the CPU horsepower (the 32-bit Geekbench score was about 2200) or memory capacity for a heavy duty database workload.

Around the same time, you could also buy a four-socket Dell PowerEdge 6850, with four dual-core, Intel Xeon 7040 “Paxville” 3.0GHz processors and 64GB of RAM (with a total of 16 logical cores with hyper-threading enabled). This was a much better choice for a database server because of the additional processor, memory, and I/O capacity compared to a PowerEdge 1850. Even so, its Geekbench score was only about 4400, which is pretty pathetic by today’s standards. Back in 2006-2007, it still made perfect sense to buy a four-socket database server for most database server workloads.

By late 2007, you could buy a two-socket Dell PowerEdge 1950, with two, quad-core Intel Xeon E5450 processors and 32GB of RAM (with a total of eight logical cores) and you would actually have a pretty powerful platform for a database server. A system like this would have a 32-bit Geekbench score of about 8000. The biggest weakness of this system was having only two x8 PCI-E 1.0 expansion slots.

By late 2008, you could buy a four-socket Dell PowerEdge R900, with four, six-core Intel Xeon X7460 processors and 256GB of RAM (with a total of of 24 logical cores). This was a very powerful , but costly platform for a database server, with a 32-bit Geekbench score of around 16500. There are still many of these model servers being used for production purposes, and while they sound impressive, that are actually a very bad choice for an upgrade to SQL Server 2012 because of their high physical core counts and low single-threaded performance. The Xeon X7460 was the last generation of Intel SMP processors, before the NUMA-capable Nehalem was introduced.

By early 2009, you could buy a two-socket Dell PowerEdge R710, with two, quad-core Intel Xeon X5570 processors, and 144GB of RAM (with a total of 16 logical cores) and you would have a very powerful database server platform. This system would have a 32-bit Geekbench score of around 15000. This would give you fairly close to the capacity of a four-socket R900, with better single-threaded performance.

By early 2010, you could buy that same Dell PowerEdge R710, with more powerful six-core Intel Xeon X5680 processors (with a total of 24 logical cores), and push the 32-bit Geekbench score to about 22500. This gives you quite a bit more CPU capacity than the PowerEdge R900 that you bought in late 2008. If you are concerned about 144GB of RAM not being enough memory in the R710, you could buy two R710s, and have nearly triple the CPU capacity of a single R900. This assumes that you can split your database workload between two database servers, by moving databases or doing things like vertical or horizontal partitioning of an existing large database.

Finally, by mid-2012, you could buy a 12th generation, Dell PowerEdge R720, with even faster eight-core Intel Xeon E5-2690 processors (with a total of 32 logical cores), which would push the 32-bit Geekbench score to about 29000. The R720 has 24 memory slots, so you can have 384GB of RAM with 16GB DIMMs or 768 GB of RAM with more expensive 32GB DIMMs. You also get seven PCI-E 3.0 expansion slots, which gives you more potential I/O bandwidth than you can get with a four-socket server (since they are still using the older PCI-E 2.0 standard).

This gap will open up even more in Q3 of 2013, when the 12-core, 22nm Intel Xeon E5-2600 v2 series (Ivy Bridge-EP) processors are released. These will be pin-compatible with the current E5-2600 series, so they will work with current model servers (probably requiring a BIOS update). They should be available very quickly after Intel releases them.

This overall trend has been continuing over the past several years, with Intel introducing new processors in the two socket space roughly a year ahead of introducing a roughly equivalent new processor in the four socket space. This means that you will get much better single-threaded OLTP performance from a two-socket system than from a four-socket system of the same age (as long as your I/O subsystem is up to par).

Given the choice, I would rather have two, two-socket machines instead of one, four-socket machines in almost all cases. The only big exception would be a case where you absolutely need to have far more memory in a single server that you can get in a two socket machine (a Dell PowerEdge R720 can now go up to 768GB if you are willing to pay for 32GB DIMMs), and you are unable to do any re-engineering to split up your load between two servers.

If you want to dive deeper into this subject, you might want to listen to my latest Pluralsight course, which is SQL Server 2012:Evaluating and Sizing Hardware. You can also contact us if you are interested in expert hardware consulting as you get ready to upgrade your database hardware.

Posted in: Dell, Hardware, Ivy Bridge-EP, Sandy Bridge-EP

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 24

By: Glenn Berry

Posted on: April 23, 2013 8:48 am

For Day 24 of this series, I want to talk a little about some things to consider as you make the decision whether to purchase a two-socket database server or a four- socket database server. Traditionally, it was very common to use a four-socket machine for most database server scenarios, while two-socket servers were most often used for web servers or application servers. With the advances in in new processors and the improvements in memory density of the past four to five years, you may want to reconsider that conventional wisdom.

Historically, two-socket database servers simply did not have enough processor capacity, memory capacity, or I/O capacity to handle many more demanding database workloads. Back in 2007, a two-socket server would typically have been limited to about eight processor cores, 32GB of RAM, and two or three PCI-E 1.0 or 2.0 expansion slots, which made it much more challenging to run a large database workload on a two-socket server in the past.

Processors have gotten far more powerful in the last few years, and memory density has gone up dramatically. Modern two-socket servers have 24 memory slots, which means that you can have 384GB of RAM with affordable 16GB DDR3 DIMMs or 768GB of RAM with more expensive 32GB DDR3 DIMMs. The price/GB of 32GB memory modules is still about $39/GB compared to about $13/GB for 16GB memory modules, but the prices for the larger modules has fallen 25% in the last two months.

It is also possible to get much more I/O capacity connected to a two-socket server than it was a few years ago. The latest generation, two-socket servers that use the Intel Xeon E5-2600 series processor can have six or seven PCI-E 3.0 expansion slots, that each have twice the bandwidth of the older PCI-E 2.0 standard.

Because of how the way that the Intel Tick-Tock processor release strategy works, two-socket servers get the latest processor microarchitectures and manufacturing process technology releases significantly sooner than when these same advances show up in the four-socket space. Right now, two-socket servers have the 32nm Sandy Bridge-EP, while four-socket servers are still using the older 32nm Westmere-EX that has higher core counts but lower single-threaded processor performance.

This performance gap will be even wider in Q3 of 2013, when the upcoming 22nm 12-core Ivy Bridge-EP is released. Four-socket servers will finally get caught up somewhat in Q4 of 2013, when the 22nm 15-core Ivy Bridge-EX is released but Ivy Bridge-EX will still have higher core counts and lower single-threaded processor performance than Ivy Bridge-EP. The gap will open up again, probably in early 2015, when Haswell-EP is released.

The final reason to think about this issue is the cost of SQL Server 2012 core licenses. If you can run your workload on a two-socket server instead of a four-socket server, you can save over 50% on your SQL Server core-based license costs, which can be a very substantial savings! Even with SQL Server 2012 Standard Edition licenses, the license cost savings would pay for a very capable two-socket database server (exclusive of the I/O subsystem).

Posted in: Hardware, Intel, Ivy Bridge-EP, Ivy Bridge-EX, Processors, SQL Server 2012

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 23

By: Glenn Berry

Posted on: April 23, 2013 6:07 am

For Day 23 of this series, I am going to briefly discuss hardware RAID controllers, also known as disk array controllers. Here is what Wikipedia has to say about RAID controllers:

A disk array controller is a device which manages the physical disk drives and presents them to the computer as logical units. It almost always implements hardware RAID, thus it is sometimes referred to as RAID controller. It also often provides additional disk cache.

Figure 1 shows a typical hardware RAID controller.

raid controller 7series A SQL Server Hardware Tidbit a Day – Day 23

Figure 1: Typical Hardware RAID Controller

For database server use (with recent vintage servers), you usually have an embedded hardware RAID controller on the motherboard, that is used for your internal SAS or SATA drives. It is pretty standard practice to have two internal drives in a RAID 1 array, controlled by the embedded RAID controller, that are used to host the operating system and the SQL Server binaries (for standalone SQL Server instances). This gives you a better level of redundancy against losing a single drive and going down.

If you are using Direct Attached Storage (DAS), you will also have one or more (preferably at least two) hardware RAID controller cards that will look similar to what you see in Figure 1. These cards go into an available PCI-E expansion slot in your server, and then are connected by a relatively short cable to an external storage enclosure (such as you see in Figure 2).

powervault md1220 overview1 A SQL Server Hardware Tidbit a Day – Day 23

Figure 2: Dell PowerVault MD1220 Direct Attached Storage Array

Each direct attached storage array will have anywhere from 14 to 24 drives. Figure 2 shows a Dell PowerVault MD1220 storage array. The RAID controller(s) are used to build and manage RAID arrays from these available drives, which eventually are presented to Windows as logical drives, usually with drive letters. For example, you could create a RAID 10 array with 16 drives and another RAID 10 array with eight drives from a single 24 drive direct attached storage array. These two RAID arrays would be presented to Windows, and show up as say the L: drive and the R: drive.

Enterprise level RAID controllers usually have some cache memory on the card itself. This cache memory can be used to cache reads or to cache writes, or split between both. For SQL Server OLTP workloads, it is a standard best practice to devote your cache memory entirely to write caching. You can also choose between write-back and write-through cache policies for your controller cache. Write-back caching provides better performance, but there is a slight risk of having data in the cache that has not been written to the disk if the server fails. That is why it is very important to have a battery-backed cache if you decide to use write-back caching.

Most enterprise-level RAID controllers will fall-back from write-back caching to write-though caching (which is safer, but slower) if the battery for the cache is not present and charged. Some newer, high-end RAID controllers are also able to use a feature developed by LSI called CacheCade that lets you use a number of SSDs as a cache in front of conventional SAS drives. This gives you much of the performance benefit of SSD storage without having to spend the money to have 100% SSD storage.

Posted in: Dell, Hardware, Storage

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 22

By: Glenn Berry

Posted on: April 22, 2013 3:21 pm

I have written previously about Building a Workstation for SQL Server 2012 Development and Testing and Building a Larger Capacity SQL Server 2012 Workstation. In those posts, I talked about the fact that it becomes increasingly expensive to build a system from parts as you move beyond mainstream desktop systems based on an Intel Core i7-3770 or i7-3770K processor.

If you actually need more than 32GB of RAM or more than four physical processor cores, you will have to move up to a single-socket, Sandy Bridge-E, Socket 2011 system, which maxes out at 64GB of RAM and six physical processor cores, at a considerably higher cost. The next step up is moving to a single-socket, Sandy Bridge-EP system with one Intel Xeon E5-1600 series or E5-2600 series processor with up to eight physical processor cores and 128GB of RAM.

Finally, you can buy the components for a dual-socket, Socket 2011 system with two Intel Xeon E5-2600 series Sandy Bridge-EP processors to build a much more powerful and expensive system with up to 16 physical processor cores and 256GB of RAM. By the time you do this, you may end up spending so much money that you would be much better off buying a refurbished, actual server from someplace like the Dell Outlet.

Depending on what you are planning on doing with your server, you can choose a Tower server, a Rack server, or even a Blade server. Depending on your infrastructure, you are probably more likely to be looking at a tower server or a rack server.

Dell Outlet PowerEdge Tower Servers

Dell Outlet PowerEdge Rack Servers

One tactic I like to use when I am searching for a server on the Dell Outlet is to focus on the installed processors in the server. You are very unlikely to ever upgrade the processors in a server after you buy it since processor upgrade kits are usually quite expensive. That means you should focus on the processors and not worry as much about the amount of RAM. Getting more RAM later is easy and inexpensive.

Storage is a little trickier. Sometimes you can find outlet servers that have a lot of big, fast internal drives that are bargains. You may also be better off to think about buying a number Intel DC S3700 SSDs along with compatible 2.5” drive carriers from someplace like Amazon (since Dell does not seem to like selling empty drive carriers very much). You should be on the lookout for systems that have decent RAID controllers in them, since they are relatively expensive to buy later.

As you get ready to buy a server from the Dell Outlet, you want to make sure you have the necessary means of payment ready to go as soon as you find the system you want, since their available inventory is constantly changing. If your organization has a lot of bureaucratic overhead, it may take too long for you to get the necessary approvals before that system you were interested in is no longer available.

Posted in: Dell, Hardware, Processors

1 Comment

A SQL Server Hardware Tidbit a Day – Day 21

By: Glenn Berry

Posted on: April 22, 2013 2:40 pm

For Day 21 of this series, I want to talk a little bit about the TPC-E OLTP benchmark.

The TPC Benchmark E (TPC-E) is an OLTP performance benchmark that was introduced in February 2007. TPC-E is a not a replacement for the older TPC-C benchmark, but rather is a completely new OLTP benchmark. It is an OLTP, database-centric workload that is meant to reduce the cost and complexity of running the benchmark compared to the older TPC-C benchmark. It simulates the OLTP workload of a brokerage firm that interacts with customers using synchronous transactions and with a financial market using asynchronous transactions.

The business model of the brokerage firm is organized by Customers, Accounts, and Securities. The data model for TPC-E is significantly more complex, but more realistic than TPC-C, with 33 tables and many different data types. The data model for the TPC-E database does enforce referential integrity, unlike the older TPC-C data model.

The TPC-E database is populated with pseudo-real data, including customer names from the year 2000 U.S. Census, and company listings from the NYSE and NASDAQ. Having realistic data introduces data skew, and makes the data compressible. Unlike TPC-C, the storage media for TPC-E must be fault tolerant (which means no RAID 0 arrays). Overall, the TPC-E benchmark is designed to have reduced I/O requirements compared to the old TPC-C benchmark, which makes it both less expensive and more realistic since the sponsoring hardware vendors will not feel as much pressure to equip their test systems with disproportionately large disk subsystems in order to get the best test results. The TPC-E benchmark is also more CPU intensive than the old TPC-C benchmark. It is essentially CPU-bound, as long as you have adequate I/O capacity to drive the workload.

The TPC-E implementation is broken down into a Driver and a System Under Test (SUT), separated by a mandatory network. The Driver represents the various client devices that would use an N-tier client-server system, abstracted into a load generation system. The SUT has multiple Application servers (Tier A) that communicate with the database server and its associated storage subsystem (Tier B). TPC provides a transaction harness component that runs in Tier A, while the test sponsor provides the other components in the SUT.

The performance metric for TPC-E is transactions per second, tpsE. The actual tpsE score represents the average number of Trade Result transactions executed within one second. To be fully compliant with the TPC-E standard, all references to tpsE results must include the tpsE rate, the associated price per tpsE, and the availability date of the priced configuration.

It seems interesting that, as of early 2013, Microsoft is the only database vendor that has any submitted TPC-E results, even though the TPC-E benchmark has been available for over six years. Whatever the reasons why the other database vendors haven’t allowed any TPC-E results to be submitted by the hardware vendors, there are certainly many results posted for SQL Server, which makes it a very useful benchmark when assessing SQL Server hardware.

The most recent posted TPC-E result is for an IBM System x3850 X5 Server with a 5457.20 tpsE score for an eight-socket system. This system has eight, ten-core Intel Xeon E7-8870 processors that have a total of 160 logical cores for the system. It also has 4TB of RAM and 236 SSDs in its I/O subsystem, using a 22TB initial database size for the test. Looking at the Executive Summary, you can see that it is running SQL Server 2012 Enterprise Edition on top of Windows Server 2012 Standard Edition. This is basically the biggest system (in terms of RAM) that you can build under the current Windows Server 2012 license limits for memory.

It is using RAID 5 for the data files, RAID 10 for the log file and RAID 1 for tempdb, with (236) 200GB 3Gbps SAS SSDs (model # 81Y9956), and (2) 600GB 6Gbps 10K SAS drives. The data files are spread across eleven, twenty-drive RAID 5 arrays (using 200GB 3Gbps SSDs), while the log file is on one, sixteen-drive RAID 10 array (using 200GB 3Gbps SSDs), and tempdb is on one, two-drive RAID 1 array, using 600GB 10K SAS drives). That shows you that tempdb is probably not hit very hard during the TPC-E benchmark!

Posted in: Benchmarks, TPC-E

Be the first to Reply!