Glenn Berry

SQL Server Database Server Hardware Upgrade Case Study

Download the SentryOne Plan Explorer Extension for Azure Data Studio
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Register to Download

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts

It is a fairly common scenario to want to compare the performance and capacity of an existing legacy database server to a proposed new database server. Rather than just guessing about the relative performance characteristics of different servers, it is much more useful to have some sort of methodology for doing this.

One method is to use actual published TPC-E benchmark scores for similar systems as an initial basis for comparison. There have been 63 published TPC-E benchmark scores (all for SQL Server) since 2007, so it is usually possible to find a roughly comparable system to use for this purpose. In some cases, you cannot find a comparable system in the TPC-E results, so you will have to use your knowledge of processors and server hardware to make credible adjustments to compensate for the differences between a published TPC-E benchmark result and a particular system you want to compare it to.

For this example, imagine that you have a very limited hardware and software license budget available for a platform upgrade. You want to move to SQL Server 2012 Standard Edition, on a server with better single-threaded performance (since you have an OLTP workload), and higher overall capacity, while minimizing your hardware and SQL Server license costs.

The Legacy System

The existing legacy system is an HP DL380 G3, with two, single-core 130nm 3.06GHz Intel Xeon "Prestonia" processors and 4GB of RAM. These were the Intel Xeon processors based on the old Pentium 4 NetBurst architecture, and they actually had pretty decent single-threaded performance. They were 32-bit only, using the old Symmetric Multi-Processing (SMP) architecture with a 533MHz front-side bus. They also support the initial implementation of Intel hyper-threading (HT) that did not work as well for many server workloads, such as SQL Server.

This system is running 32-bit SQL Server 2000 on 32-bit Windows Server 2003, so we have a situation where the hardware is long out of warranty, the operating system is out of mainstream support and SQL Server is out of extended support.

The oldest and lowest TPC-E benchmark score available is for a Dell PowerEdge 2900 system with one quad-core, 65nm 2.66GHz Intel Xeon X5355 processor, with an actual TPC-E score of 144.88. I calculate that a two-socket server with two 3.06GHz Intel Xeon Prestonia processors would have an estimated TPC-E score of about 70. Having two total physical cores, with a higher clock speed on a much older microarchitecture is how I justify this estimate.

We can compare this legacy system to five different possible replacement servers, using TPC-E scores as a basis for comparison. In order to minimize our hardware and SQL Server 2012 license costs, we will use a two-socket server with only one processor socket populated.

A Single Xeon X5570 System (Nehalem-EP)

There is a TPC-E result for an IBM x3650 M2 system with two, quad-core 45nm 2.93GHz Intel Xeon X5570 processors, with an actual TPC-E score of 798.0. A two-socket system with only one Xeon X5570 processor would have an estimated TPC-E score of 420. SQL Server 2012 requires that you buy at least four core licenses per physical processor. The Intel Nehalem microarchitecture is relatively old, since Intel released it in the two-socket space in late 2008. The Nehalem architecture does use Non-Uniform Memory Access (NUMA) instead of SMP. It also supports hyper-threading and Intel Turbo Boost.

A Single Xeon X5690 System (Westmere-EP)

There is a TPC-E result for an HP DL380 G7 system with two, six-core 32nm 3.46GHz Intel Xeon X5690 processors, with an actual TPC-E score of 1284.14. A two-socket system with only one Xeon X5690 processor would have an estimated TPC-E score of 675. This newer processor would also work in a system that supported the older Xeon 5500 series processors, such as an HP DL380 G7 or a Dell PowerEdge R710.

A Single Xeon E5-2690 System (Sandy Bridge-EP)

There is a TPC-E result for an HP DL380p G8 system with two, eight-core 32nm 2.9GHz Intel Xeon E5-2690 processors with an actual TPC-E score of 1881.76. A two-socket system with only one Xeon E5-2690 processor would have an estimated TPC-E score of 980. The Intel Sandy Bridge was a Tock release with much better memory and I/O bandwidth compared to previous releases. This type of processor will work in an HP DL380 G8 or a Dell PowerEdge R720 server.

A Single Xeon E5-2697 v2 System (Ivy Bridge-EP)

There is a TPC-E result for an IBM x3650 M2 system with two, twelve-core 22nm 2.7GHz Intel Xeon E5-2697 v2 processors with an actual TPC-E score of 2590.93. This processor has a base clock speed of 2.7GHz, and a Turbo Boost speed of 3.5GHz. It has a 30MB L3 cache that is shared between twelve physical cores. A two-socket system with only one Xeon E5-2697 v2 processor would have an estimated TPC-E score of 1340.

A Single Xeon E5-2637 v2 System (Ivy Bridge-EP)

A less expensive, but faster alternative would be to use a two-socket system with only one, quad-core 22nm 3.5GHz Xeon E5-2637 v2 processor to get significantly better single-threaded performance and lower SQL Server 2012 license costs compared to the higher core count model processors.

This processor has a base clock speed of 3.5GHz, and a Turbo Boost speed of 3.8GHz. It has a 15MB L3 cache that is only shared between four physical cores. Because of these differences, this processor will have about 35% better single-threaded performance than the Intel Xeon E5-2697 v2 processor, but less overall processor capacity. A two-socket system with only one Xeon E5-2637 v2 processor would have an estimated TPC-E score of 603.

Analysis

Table 1 summarizes these systems, starting with the legacy system at the top:

Processor TPC-E Score Physical Cores Processor Cost License Cost
(2) 3.06GHz Xeon 70 2 ??? $7,172.00
(1) Xeon X5570 420 4 $1,386.00 $7,172.00
(1) Xeon X5690 675 6 $1,666.00 $10,752.00
(1) Xeon E5-2690 980 8 $2,061.00 $14,344.00
(1) Xeon E5-2697 v2 1340 12 $2,618.00 $21,504.00
(1) Xeon E5-2637 v2 603 4 $996.00 $7,172.00

Table 1: System Comparison Metrics

I estimate that you would have 5-6X the processor capacity of the legacy system with a single Xeon X5570 processor, while you would have about 8-9X the processor capacity of the legacy system with a single Xeon E5-2637 v2 processor.

You can divide the TPC-E score by the number of physical cores in the system to get a score per core to get an idea of relative single-threaded processor performance. Table 2 shows the results of this calculation.

Processor TPC-E Score Physical Cores Score/Core
(2) 3.06GHz Xeon 70 2 35
(1) Xeon X5570 420 4 105
(1) Xeon X5690 675 6 112.5
(1) Xeon E5-2690 980 8 122.5
(1) Xeon E5-2697 v2 1340 12 111.7
(1) Xeon E5-2637 v2 603 4 150.8

Table 2: System TPC-E Score/Core Results

This blog post talks in more detail about some of the best current processor choices for SQL Server 2012 from the latest Intel Xeon E5-2600 v2 processor family.