Reference Architecture
Intel® Select Solutions – QCT Database Solution for SQL Server OLTP
2
CONTENTS
Executive Summary ...................................................................................... 4
Solution Overview ........................................................................................ 5
An Intel Select Solution ................................................................................. 6
Solution Architecture .................................................................................... 7
Hardware Configuration ................................................................................................ 7
QCT Plus Configuration SKU – D52B – 1U ................................................................................................. 7
QCT Base Configuration SKU – D52BQ – 2U ........................................................................................... 10
Software Configuration ................................................................................................ 12
Firmware Settings ........................................................................................................ 12
Operating System Settings ........................................................................................... 14
SQL Settings ................................................................................................................. 14
Intel® Select Plus Solution SQL Settings .................................................................................................. 14
Intel® Select Base Solution SQL Settings ................................................................................................. 14
SQL Scripts for Configuration .................................................................................................................. 15
Benchmark Methodology & Results ............................................................ 16
References ................................................................................................. 17
About QCT .................................................................................................. 18
3
REVISIONS
Version Date Description Authors
1.0 12/06/2017 Infrastructure content
Tiger Chung
1.1 12/06/2017 Testing content Pei-Ying Huang
1.2 12/06/2017 Marketing, business related content
Kevin Wang
1.3 12/07/2017 First draft of RA as a whole
Kevin Wang
1.4 12/22/2017 Final version after Intel review
QCT & Intel Team
4
Executive Summary
Today’s ever-changing business world relies more than ever on data collection and
analysis, while the quantity, size, and complexity of workloads increase almost every
day. In the database world, the demand for high-performance database solutions,
such as Microsoft SQL Server solutions are increasing at an extremely fast pace.
More specifically, an OLTP database solution that requires fast transaction
processing speed, while maintaining low-latency SLAs is the largest challenge that
most customers would like to tackle.
On the other hand, both database software and hardware continues to develop with
new functionalities and better performances. It is critical to select the key
components and build up the best combination between software and hardware, in
order to meet the requirements on performance speeds, scalable capacity and
flexibility of today’s business.
This Reference Architecture will demonstrate two highly reliable and workload-
optimized configurations for OLTP database, with Microsoft SQL Server 2016 and
Windows Server 2016 on QCT high efficient servers using new Intel® Xeon® Scalable
processors. With an all-flash (all-NVMe) storage of high performing NVMe Intel SSD
DC series, the random, mix read/write workload characteristics of OLTP database can
be achieved accordingly.
Target audience:
This Quanta Cloud Technology reference architecture on our SQL database solution,
is written for IT professionals who either program or manage databases as
administrators, or evaluate, recommend, and design database architectures as
consultants and system integrators.
Document purpose:
The purpose of this document is to describe a reference configuration, highlighting
benefits and key implementation details to technical audiences, especially those who
are considering to move their SQL OLTP databases to the new platform based on
Intel® Xeon® Scalable processors and corresponding new server technologies such as
Intel® SSD DC series and Intel® Ethernet Converged Network Adapters.
5
Solution Overview
QCT Intel Select Solutions for Microsoft SQL Server Business Operations optimized
for OLTP can help minimize the time, cost, and complexity required for an enterprise
to evaluate hardware and software integrations. It includes a hardware and software
stack that has been verified following organized procedures and intensive testing
efforts by QCT and Intel. QCT uses the Hammer DB benchmark to validate the
configuration for Online Transaction Processing (OLTP). Furthermore, based on the
Transactions Per Minute (TPM) metric, QCT’s SQL OLTP solution has consistently
produced high benchmark results.
QCT features two configurations in order to help customers thrive in today’s dynamic
application scenarios. The “Plus” configuration, with all-NVMe Intel® SSD DC Series,
is designed for mission-critical workload environments to provide excellent
performance for database transactions and queries.
On the other hand, the “Base” configuration is a good fit for mainstream database
application with consideration for better TCO. Both configurations are verified using
the Hammer DB benchmark, which serves as a great measurement for OLTP
performance (Online Transaction Processing). The “Plus” configuration achieves
6,328,799 TPM (Transaction Per Minute) scores ensuring the solution stack can
deliver a premium customer experience.
(Exceed Intel performance specification by 27%)
6
An Intel Select Solution
Intel® Select Solutions are workload-optimized data center solutions for the data
center that speed up deployment. These verified
hardware and software configurations help
customers accelerate their process when selecting
and deploying the hardware and software needed
for today’s broad array of workloads and applications. Based on the Intel® Xeon®
Scalable processors, Intel® Select Solutions enable Enterprises, Cloud Service
Providers, and Communication Service Providers to make confident choices when
deploying their data center infrastructure due to that fact that these solutions are
predefined and purpose built. Intel® Select Solutions for Microsoft SQL Server
Business Operations deliver qualified and optimized solution configurations for a
scalable, hybrid database platform. QCT Intel® Select Solutions for Microsoft SQL
Server Business Operations optimized for OLTP is no exception as they provide
strong reliability, security, and software innovations under the Intel® Select Solution
brand.
7
Solution Architecture
QCT Intel® Select Solutions for Microsoft SQL Server Business Operations optimized
for OLTP includes a software and hardware stack that has been verified following
organized procedures and intensive testing efforts by QCT and Intel. QCT uses the
HammerDB benchmark to validate the configuration for Online Transaction
Processing (OLTP). Furthermore, based on the Transactions Per Minute(TPM) metric,
the QCT solution has considerably outperformed Intel standards on both base and
plus configuration.
Intel Select Solutions
Configuration
QCT Offering
Market Segmentation
Key Features
Plus D52B-1U Mission-critical, OLTP database
• NVMe enabled • Mission-critical workload • Large capacity of memory provides SQL in-
memory OLTP
Base D52BQ-2U Value for money,
OLTP database
• Mainstream database application for better TCO
• Provide solid performance under attractive price / performance ratio
Hardware Configuration
QCT Plus Configuration SKU – D52B – 1U
Plus configuration is designed for mission-critical, intensive business operations
workload environments. With all-NVMe SSDs on both data and log drives, it can
provide excellent performance for database transactions and queries. Also, the large
capacity of memory spaces can work well with SQL Server in-memory OLTP
technology to provide significant performance boost when the hot data can become
fully resident in RAM. Its operational environment architecture as well as the
software and hardware default specifications are as follows:
8
Reference Architecture Diagram
Software List
Type Software product OS Windows Server 2016 Datacenter edition Database Software MS SQL Server 2016 Enterprise edition
Hardware list
Role QTY Series
DB nodes 1 QuantaGrid D52B-1U (S5B) Switch (40Gbps) 1 QuantaMesh T3048-LY8
Hardware Specifications
When using all Flash architecture with NVMe Intel® SSD DC series on both data and
log drives, it can provide excellent performance for database transactions and
queries. The following is the hardware specifications for server and switch:
Hardware Role Hardware Specs QTY
DB Node
QuantaGrid D52B-1U (S5B) 1
CPU: Intel® Xeon® Gold-6152 processor 2
RAM: 32GB 2666MHz DDR4 RDIMM 12
Boot: 512GB PCIe M.2 SSD 2
Log Drive: Intel® DC P4600 Series, 1.6TB NVMe 2
Data Drive:Intel® DC P4500 Series, 2TB NVMe 4
Network: Intel Ethernet Converged Network 1
9
Adapter XL710, 10/40GbE
TPM:Trusted Platform Module (TPM) 2.0 1
Hardware Role Hardware Specs QTY
Network Switch
QuantaMeash T3048-LY8
48 Ports: 1/10GbE SFP+
6 Ports: 40GbE QSFP+
Switching Capacity: 1440 Gbps
Latency: < 600 ns
40G QSFP+ DAC Cable
40G QSFP+ DAC Cable - 3m
1G Cat5e CABLE - 2m
Intel Technologies Settings
Hardware Description Required or Recommended
Quantity
Intel® VMD No Recommended N/A
Intel® RSTe for SATA Yes Recommended
Intel® RSTe for NVMe Yes Recommended
Intel® QAT No Recommended N/A
iWARP RDMA No Recommended N/A
TPM TPM 2.0 Required N/A
Intel® Trusted Execution Technology
No Recommended N/A
Intel® Boot Guard No Recommended N/A
Firmware/Software Optimizations
Intel® Hyper-Threading Technology Enabled
Yes Required N/A
Intel® Turbo Boost Technology
Yes Required N/A
10
Intel® Speed Shift Technology
HWP Native Required N/A
C-States Disabled Required N/A
Power Management Settings
Performance Required N/A
QCT Base Configuration SKU – D52BQ – 2U
Base configuration is a good fit for mainstream database application with
consideration for better TCO. We choose Intel® Xeon® Gold 6136 processor (3.00GHz
/12 cores) along with Intel® SSD DC Series on data drives that can provide good
performance under attractive price / performance ratio. Its operation environment
architecture as well as the software and hardware default specifications are as
follows:
Reference Architecture Diagram
Software List
Type Software Product OS Windows Server 2016 Datacenter edition
Database Software MS SQL Server 2016 Enterprise edition
Hardware List
Role QTY Series
DB node 1 QuantaGrid D52BQ-2U (S5BQ) Switch (10 Gbps) 1 QuantaMesh T3048-LY8
11
Hardware Specification
When using hybrid hardware architecture of NVMe SSD and SATA SSD, it can provide
good performance under attractive price / performance ratio. The following is the
hardware specifications for server and switch:
Role Hardware Spec QTY
DB Node
QuantaGrid D52BQ-2U (S5BQ)
CPU: Intel® Xeon® Gold-6136 processor 2
RAM: 32GB DDR4 RDIMM 12
Boot: 512GB PCIe M.2 SSD 2
Log Drive: Intel DC P4600 Series, 1.6TB NVMe 4
Data Drive:Intel DC S4500 Series, 1.9TB SATA
SSD 4
Network : Intel Ethernet Network Connection
OCP X527-DA4, 10GbE/ Intel Ethernet Converged Network Adapter XL710, 40GbE
1
TPM:Trusted Platform Module (TPM) 2.0 1
Role Hardware Spec QTY
Network Switch
QuantaMeash T3048-LY8
48 Ports: 1G/10GbE SFP+
6 Ports: 40GbE QSFP+
Switching Capacity: 1440 Gbps
Latency: < 600 ns
10G SFP+ DAC Cable
10G SFP+ DAC Cable - 3m
1G Cat5e CABLE - 2m
Intel Technologies Settings
Hardware Description Required or Recommended
Quantity
Intel® VMD No Recommended N/A
Intel® RSTe for SATA Yes Recommended
Intel® RSTe for NVMe Yes Recommended
12
Intel® QAT No Recommended N/A
iWARP RDMA No Recommended N/A
TPM TPM 2.0 Required N/A
Intel® Trusted Execution Technology
No Recommended N/A
Intel® Boot Guard No Recommended N/A
Firmware/Software Optimizations
Intel® Hyper-Threading Technology Enabled
Yes Required N/A
Intel® Turbo Boost Technology
Yes Required N/A
Intel® Speed Shift Technology
HWP Native Required N/A
C-States Disabled Required N/A
Power Management Settings
Performance Required N/A
Software Configuration
This section describes the configurations that QCT achieved the best optimal
benchmark results.
Firmware Settings
The following are the firmware settings in the reference hardware used for the Intel
Select base and plus reference designs.
Intel® HTT: Advanced > Processor Configuration > Intel HTT > Enabled
MLC Streamer: Advanced > Processor Configuration > Enabled
MLC Spatial Prefetcher: Advanced > Processor Configuration > Enabled
DCU Data Prefetcher: Advanced > Processor Configuration > Enabled
DCU Instruction Prefetcher: Advanced > Processor Configuration > Enabled
LLC Prefetcher: Advanced > Processor Configuration > Enabled
CPU Power & Performance Policy: Advanced > Power & Performance > CPU Power &
13
Performance Policy > Performance
Workload Configuration: Advanced > Power & Performance > Workload
Configuration > Balanced
Uncore Power Management: Advanced > Power & Performance > Uncore Frequency
Scaling > Disabled
Uncore Power Management: Advanced > Power & Performance > Performance P-
Limit > Disabled
CPU P-State Control: Advanced > Power & Performance > CPU P State Control >
Enhanced Intel Speedstep Tech > Enabled
CPU P-State Control: Advanced > Power & Performance > CPU P State Control > Intel
Configuration TDP > Disabled
CPU P-State Control: Advanced > Power & Performance > CPU P State Control > Intel
Turbo Boost Technology > Enabled
CPU P-State Control: Advanced > Power & Performance > CPU P State Control >
Energy Efficient Turbo > Enabled
Hardware P-States: Advanced > Power & Performance > Hardware P states >
Hardware P-States > Native Mode
Hardware P-States: Advanced > Power & Performance > Hardware P states >
HardwarePM Interrupts > Disabled
Hardware P-States: Advanced > Power & Performance > Hardware P states > EPP
Enable > Enabled
Hardware P-States: Advanced > Power & Performance > Hardware P states > APS
Rocketing > Disabled
Hardware P-States: Advanced > Power & Performance > Hardware P states >
Scalability > Disabled
Hardware P-States: Advanced > Power & Performance > Hardware P states > PPO-
Budget > Disabled
CPU C-State Control: Advanced > Power & Performance > CPU C State Control >
Package C-State > C0/C1 state
CPU C-State Control: Advanced > Power & Performance > CPU C State Control > C1E
> Disabled
CPU C-State Control: Advanced > Power & Performance > CPU C State Control >
Processor C6 > Disabled
14
Operating System Settings
Power Plan – High Performance: Control Panel > System & Security > Power Options
> High Performance
Firewall – Disabled: Control Panel > System & Security > Windows Firewall > "Turn
Windows Firewall on or off" (left side pane) > "Turn off Windows Firewall" (for both
private and public networks)
System Tuning: Control Panel > System & Security > System > “Advanced System
Settings” (left side pane) > Advanced > Performance > Settings > Visual > Adjust for
best performance
System Tuning: Control Panel > System & Security > System > “Advanced System
Settings” (left side pane) > Advanced > Performance > Settings > Advanced > “Adjust
for best performance of” > Programs
System Tuning: Control Panel > System & Security > System > “Advanced System
Settings” (left side pane) > Advanced > Performance > Settings > Advanced > “Virtual
Memory” > Programs > Custom size: 4096 initial and maximum (not system
managed)
SQL Settings
Intel® Select Plus Solution SQL Settings
• max server memory (MB): 353894MB (90% of physical memory)
• lightweight pooling: 1 (Enabled)
• max worker threads: 3000
• priority boost: 1 (Enabled)
• recovery interval (min): 32767
Intel® Select Base Solution SQL Settings
• max server memory (MB): 353894MB (90% of physical memory)
• lightweight pooling: 1 (Enabled)
• max worker threads: 3000
• priority boost: 1 (Enabled)
• recovery interval (min): 32767
15
SQL Scripts for Configuration
Script #1 (SQL Server Settings)
exec sp_configure 'show advanced options', '1'
reconfigure with override
/* Replace <<MemoryPerConfiguration>> with value from previous section */
exec sp_configure 'max server memory', 353894
exec sp_configure 'recovery interval','32767'
exec sp_configure 'max degree of parallelism', '1'
exec sp_configure 'lightweight pooling', '1'
exec sp_configure 'priority boost', '1'
exec sp_configure 'max worker threads', 3000
exec sp_configure 'default trace enabled', '0'
go
reconfigure with override
alter server configuration set process affinity numanode=0 To 1; select
cpu_id from sys.dm_os_schedulers where status=’VISIBLE ONLINE’
Script #2 (Partition the History Table – as described in the HammerDB
documentation)
USE [TPCC] -- Name of test database
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION[HISTORY_PART](int) AS RANGE LEFT FOR VALUES (1,
100, 200, 300, 400, 500, 600, 700, 800)
CREATE PARTITION SCHEME [HISTORY_PART] AS PARTITION [HISTORY_PART] TO
([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIM
ARY],[PRIMARY],[PRIMARY])
CREATE CLUSTERED INDEX [ClusteredIndex_on_HISTORY_PART_634589363881526517]
ON [dbo].[history]
(
16
[h_w_id]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF) ON [HISTORY_PART]([h_w_id])
DROP INDEX[ClusteredIndex_on_HISTORY_PART_634589363881526517] ON
[dbo].[history] WITH ( ONLINE = OFF )
COMMIT TRANSACTION
Benchmark Methodology & Results
Benchmark Methodology
QCT Intel Select Solutions for Microsoft SQL Server Business Operations optimized
for OLTP uses Hammer DB v2.23 SQL Server OLTP Performance (TPC-C) workload for
performance benchmarking.
DB Definition
Initial Database size 100GB with autogrow
Initial Log Size 64GB with autogrow
History Partition Created per HammerDB OLTP on SQL Server best practices document http://www.hammerdb.com/hammerdb_mssql_oltp_best_practice.pdf
Client System Details QCT QuantaGrid D51BP-1U with 2S Intel® Xeon® processor E5-2695 v4 @ 2.10GHz 18C/36T; 512GB Memory; 128GB SATADOM SSD; 10GbE direct connection to server
DB Schema (Schema Build)
800 WH
Ramp Time 2 mins
Test Time 3 mins
Use All Warehouses Option
Enabled
Checkpoint on End of Test
Enabled
Virtual Users Enabled
17
User Delay 1 ms
Benchmark Results
This section presents that the benchmark results for Base and Plus Configurations on
QCT servers. The configurations demonstrated the ability to consistently deliver
performance as required by Intel’s guidelines.
QCT SQL Benchmark Result Performance Score (TPM)
Base Configuration 4,928,408 TPM
Plus Configuration 6,328,799 TPM
*The Transactions Per Minute (TPM) scores reported by the benchmark is used as the Key Performance
Indicator (KPI) metric to qualify for Intel Select Solution.
References Intel® Select Solutions for Microsoft SQL Server* Solution Brief
https://www.intel.com/content/www/us/en/cloud-computing/select-sql-server-solution-brief.html
Hammer DB Benchmark Tool
http://www.hammerdb.com/
18
United States
China
QCT LLC., Silicon Valley office 1010 Rincon Circle, San Jose, CA 95131 TOLL-FREE: 1-855-QCT-MUST TEL: +1-510-270-6111 FAX: +1-510-270-6161 Support: +1-510-270-6216 QCT LLC., Seattle office 13810 SE Eastgate Way, Suite 190, Building 1, Bellevue, WA 98005 TEL: +1-425-633-1620 FAX: +1-425-633-1621
云达科技, 北京办公室(Quanta Cloud Technology)
北京市朝阳区东三环中路 1 号,环球金融中心东楼 1508 室
Room 1508, East Tower 15F, World Financial Center No.1, East 3rd Ring Zhong Rd., Chaoyang District, Beijing, China TEL: +86-10-5920-7600 FAX: +86-10-5981-7958
云达科技, 杭州办公室(Quanta Cloud Technology)
浙江省杭州市西湖区古墩路浙商财富中心 4 号楼 303 室
Room 303,Building No.4,ZheShang Wealth Center
No. 83 GuDun Road, Xihu District, Hangzhou, Zhejiang , China TEL: +86-571-2819-8660
Japan
Taiwan
Other regions
Quanta Cloud Technology Japan 株式会社
日本国東京都港区芝大門二丁目五番八号
牧田ビル3階
Makita Building 3F, 2-5-8, Shibadaimon , Minato-ku, Tokyo 105-0012, Japan TEL: +81-3-5777-0818 FAX: +81-3-5777-0819
雲達科技(Quanta Cloud Technology)
桃園市龜山區文化二路 211 號 1 樓
1F, No. 211 Wenhua 2nd Rd., Guishan Dist., Taoyuan City 33377, Taiwan TEL: +886-3-286-0707 FAX: +886-3-327-0001 Quanta Cloud Technology No. 211 Wenhua 2nd Rd., Guishan Dist., Taoyuan City 33377, Taiwan TEL: +886-3-327-2345 FAX: +886-3-397-4770
About QCT
QCT (Quanta Cloud Technology) is a global datacenter solution provider extending the power of hyperscale datacenter design in standard and open SKUs to all datacenter customers.
Product lines include servers, storage, network switches, integrated rack systems and cloud solutions, all delivering hyperscale efficiency, scalability, reliability, manageability, serviceability and optimized performance for each workload.
QCT offers a full spectrum of datacenter products and services from engineering, integration and optimization to global supply chain support, all under one roof.
The parent of QCT is Quanta Computer Inc., a Fortune Global 500 technology engineering and manufacturing company.
http://www.QCT.io
All specifications and figures are subject to change without prior notice. Actual products may look different from the photos.
QCT, the QCT logo, Rackgo, Quanta, and the Quanta logo are trademarks or registered trademarks of Quanta Computer Inc.
All trademarks and logos are the properties of their representative holders.
Copyright © 2014-2015 Quanta Computer Inc. All rights reserved.