SQL Server OLTP Benchmark

StorageReview’s SQL Server OLTP testing protocol employs the current draft of the Transaction Processing Performance Council’s Benchmark C (TPC-C), an online transaction processing benchmark that utilizes a combination of read-only and update-intensive transactions to simulate the activities found in complex database application environments. While the TPC-C benchmark cannot simulate the complete range of database applications in use, it comes much closer than simpler synthetic performance benchmarks when guaging the performance strengths and bottlenecks of storage and server infrastructure in database environments.

The TPC-C simulates an environment where many terminal operators execute transactions against a database, including essential online transaction processing (OLTP) activities such as entering and delivering orders, recording payments, checking the status of orders, and monitoring the level of stock at warehouses. The scenario and workload is modeled on the requirements and workloads of a wholesale supplier, but is intended to broadly reflect any industry that must manage, sell, or distribute a product or service.

Five types of transactions are used to model this business activity, with the results expressed in terms of orders that can be fully processed per minute. The TPC-C benchmark requires the simultaneous execution of multiple transaction types of different complexities, processing for online and deferred transactions, transaction integrity (ACID properties), non-uniform distribution of data access through primary and secondary keys, and contention on data access and update.

Dell Benchmark Factory for Databases

Dell Benchmark Factory for Databases is a database performance testing tool that is intended to test patches and other new code, perform migrations, consolidate databases, virtualize existing databases, and make other changes to hardware or software environments. Benchmark Factory for Databases also features a workload capture and replay technology that scripts Oracle and SQL Server production workloads on test environments to measure performance, functionality which we use as part of our SQL Server OLTP benchmark protocol.

Windows Server 2012

Windows Server 2012 incorporates a wide variety of server functionality including storage, networking, virtualization, and automation capabilities. Windows Server 2012 can make use of the Hyper-V virtualization platform with up to 64 virtual processors and 1TB of memory per virtual machine.

SQL Server 2012 SP1

SQL Server 2012 is a platform for on-premise and public cloud information storage, and incorporates AlwaysOn high availability functionality, consistent data via BI Semantic Model and Data Quality Services, and data discovery via Power View and PowerPivot. According to Microsoft, SQL Server 2012’s xVelocity in-memory technologies provide query performance gains over SQL Server 2008.

SQL Server OLTP Testing Environment

Storage solutions are tested with the SQL Server OLTP benchmark in the StorageReview Enterprise Test Lab utilizing multiple servers connected over a high-speed network. We utilize the Dell PowerEdge R730 for different segments of the Environment, including four R730s acting as ESXi 5.1 hosts, one R730 running a VMware vCenter Appliance, and one R730 as a bare-metal server running SQL Server 2012 on top of Windows Server 2012. The Dell PowerEdge brand is currently our top choice when designing this platform,  as it offers superior performance and still driving great value. The PowerEdge line also offers excellent hardware compatibility, which is an absolute must as we incorporate different forms of storage and networking technology into our testing platform.

Mellanox 56Gb InfiniBand interconnects were used to provide the highest performance and greatest network efficiency on each ESXi vSphere host to ensure that the VMs connected are not network-limited. We use one single-port Mellanox ConnectX-3 NIC operating in IPoIB mode giving us a 56Gb/s link between each server. This alleviates any network constraints and reduces the complexity of the environment in our multi-use testing infrastructure.

First Generation SQL Server OLTP Benchmark Factory LoadGen Equipment

  • Lenovo ThinkServer RD630 VMware ESXi vSphere 2-node Cluster
    • Four Intel E5-2690 CPUs in cluster (Two per node, 2.9GHz, 8-cores, 20MB Cache)
    • 256GB RAM (128GB per node, 8GB x 16 DDR3, 64GB per CPU)
    • 120GB OCZ Deneva 2 SATA SSD x 4 (via LSI 9207-8i)
    • 2 x Mellanox ConnectX-3 InfiniBand Adapter (vSwitch for vMotion and VM network)
    • VMware ESXi vSphere 5.1 / Enterprise Plus 4-CPU

Second Generation SQL Server OLTP Benchmark Factory LoadGen Equipment

  • Dell PowerEdge R730 VMware ESXi vSphere Virtual Client Hosts (2)
    • Four Intel E5-2690 v3 CPUs for 124GHz in cluster (Two per node, 2.6GHz, 12-cores, 30MB Cache) 
    • 512GB RAM (256GB per node, 16GB x 16 DDR4, 128GB per CPU)
    • SD Card Boot (Lexar 16GB)
    • 2 x Mellanox ConnectX-3 InfiniBand Adapter (vSwitch for vMotion and VM network)
    • 2 x Emulex 16GB dual-port FC HBA
    • 2 x Emulex 10GbE dual-port NIC
    • VMware ESXi vSphere 6.0 / Enterprise Plus 4-CPU

SQL Server OLTP Benchmark Results

The primary performance metric reported by TPC-C is a "business throughput" measurement for the number of orders processed per minute, expressed as transactions-per-minute-C (tpmC). Other metrics including price-per-tpmC and watts-per-tpmC are also reported.

The Company portrayed by the benchmark is a wholesale supplier with a number of geographically distributed sales districts and associated warehouses. As the simulated business expands, new warehouses and sales districts are created. Each regional warehouse covers 10 districts and each district serves 3,000 customers. Each simulated warehouse has ten terminals and all five transactions are available at each terminal. The tpm-C metric is the number of 'New Order' transactions executed per minute.

In our Benchmark Factory testing environment, we use a modified TPC-C testing scheme, with VU latencies set to 1/4 of their original figures to drive additional I/O to the storage medium being tested. We use 1,500 scale and 3,000 scale database size for testing different classis of products, which works out to database sizes measuring 333GB and 685GB respectively.

Newer SAN and Hyper-converged platforms run 4 VMs simultaneously to show how well multiple workloads operate at the same time on each. This testing methology helps demystify the performance comparisons between newer hyper-converged systems against traditional SAN storage arrays.

Hyper-Converged / SAN Virtualized SQL Performance Results (4 VM Aggregate)

Device 15k Virtual User Aggregate TPS 15k Virtual User Average Response Time (ms)
X-IO ISE 860
(4) Dell R730, X-IO ISE 860 AFA
(2) 10TB Volumes
12,565  13
DotHill Ultra48 Hybrid
(4) Dell R730, DotHill Ultra48 Hybrid
(2) 14-disk RAID1 Pools, 40 1.8TB HDDs, 8 400GB SSDs
12,480  65.5
VMware VSAN (ESXi 6.0)
(4) Dell R730xd, 80 1.2TB HDDs, 16 800GB SSDs
12,437  74

PCIe Application Accelerator /  Multi-SSD/HDD RAID Performance Results (3,000 scale)

Device 30k Virtual User Average TPS 30k Virtual User Average Response Time (ms)
Fusion ioDrive2 Duo 2.4TB
6322.78 3
Huawei ES3000 2.4TB
6321.67 3
Fusion SX300 3.2TB
6321.1 3
Fusion ioDrive2 Duo 1.2TB
6320.93 3
Huawei ES3000 1.2TB
6320.76 3
Memblaze PBBlaze3L 2.4TB
MLC PCIe x 1
6320.7 3
Fusion PX600 2.6TB
6320.5 3
Fusion ioDrive2 1.2TB
6319.61 4
Hitachi SSD800MM 400GB
eMLC SAS x 4, Windows Storage Spaces, Mirror
6316.21 7
Infortrend ESDS S16F-R2651
Sandisk Optimus Eco 400GB x 16, RAID10 (1) Pool
6316.08 7
Memblaze PBBlaze3L 1.2TB
MLC PCIe x 1
6315.3 7
Virident FlashMAX II 2.2TB
MLC PCIe, HP mode
6314.71 8
Fusion ioScale 3.2TB
6314.26 8
Samsung XS1715 NVMe 1.6TB
6311.9 10
Samsung 845DC PRO 400GB
6309.4 12
OCZ ZD-XL 1.6TB (Flash Volume)
6309.12 12
LSI Nytro WarpDrive 800GB
6306.10 15
Intel P3700 NVMe 800GB
6303.72 15
Sandisk Optimus Eco 400GB
MLC SAS x 4, Windows Storage Spaces, Mirror
6300.18 19
Micron P420m 1.4TB
6299.99 19
Hitachi SSD800MM 400GB
MLC SAS x 8, RAID10, 10GbE x 2 iSCSI from Supermicro S2012 R2 FileServer
6298.17 21
OCZ Talos 2 400GB
MLC SAS x 4, Windows Storage Spaces, Mirror
6297.17 21
Samsung SM843T 480GB
6290.4 26
Sandisk CloudSpeed 1000E
MLC SATA x 4, Windows Storage Spaces, Mirror
6270.32 42
Intel SSD S3500 480GB
MLC SATA x 4, Windows Storage Spaces, Mirror
6265.92 46
Samsung PM853T 960GB
6262.6 48
Samsung SSD 845DC EVO 480GB
6244.8 62.0
Micron P400m 400GB
MLC SATA x 4, Windows Storage Spaces, Mirror
6231.87 72
Micron M500 960GB
MLC SATA x 8, RAID50, 10GbE x 2 iSCSI from Supermicro S2012 R2 FileServer
6222.76 79
X-IO ISE 710
FC Hybrid
6153.12 133
Dell EqualLogic PS6210XS
iSCSI Hybrid
6135.52 146
Dell EqualLogic PS6110XS
iSCSI Hybrid
5794.10 434
Seagate Turbo SSHD 600GB SAS
H/W LSI 9286-8e x 16, RAID10
4689.80 1,523
WD Xe 900GB 10K SAS
H/W LSI 9286-8e x 16, RAID10
3729.85 3,303
Samsung SSD 840 Pro 512GB
MLC SATA x 4, Windows Storage Spaces, Mirror
2616.08 6,725

Individual SAS / SATA SSD Performance Results (3,000 scale)

Device 30k Virtual User Average TPS 30k Virtual User Average Response Time (ms)
Toshiba HK3R2 960GB
6309.7 12
SanDisk CloudSpeed Eco 960GB
6307.0 14
OCZ Intrepid 3600 400GB
6281.2 32
Hitachi SSD800MM 800GB
eMLC SAS x 1
6277.76 36
Intel S3700 800GB
6219.12 82
Toshiba PX02SM 800GB
eMLC SAS x 1
6193.63 102
Toshiba PX03SN 800GB
eMLC SAS x 1
6181.64 111
Samsung SSD 840 EVO 1TB
2560.33 6,974
Micron M500 960GB
107.13 275,318
Toshiba HG6 512GB
96.1 307,558

Caching Solution Performance Results (1,500 scale)

Device 15k Virtual User Average TPS 15k Virtual User Average Response Time (ms)
Seagate Turbo SSHD 600GB SAS
H/W LSI 9286-8e x 16, RAID10
3049.46 176
Seagate Savvio 10k.7 600GB SAS
H/W LSI 9286-8e x 16, RAID10
3027.22 212
Seagate Enterprise Capacity SAS 1.2TB
H/W LSI 9286-8e x 16, RAID10
3001.5 255
H/W LSI 9286-8e x 16, RAID10
2948.79 347
H/W LSI 9286-8e x 16, RAID10
2629.40 964
WD Xe 900GB 10K SAS
H/W LSI 9286-8e x 16, RAID10
2187.89 2,114