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.

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 gauging 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

• Mellanox SX6036 InfiniBand Switch
  • 36 FDR (56Gb/s) ports
  • 4Tb/s aggregate switching capacity

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 classes 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 methodology 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)

Local Flash Storage Virtualized SQL Performance Results (4 VM Aggregate)

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

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

Caching Solution Performance Results (1,500 scale)