Updated
30 Jan 2020Form Number
SB0002PDF size
7 pages, 221 KBReduce time to value with pretested hardware configurations
Data growth problem and a solution
The rapid growth of technology means that the amount of available data and the ability to collect that data increased to a level unthinkable as little as five years ago. As the volume and velocity of data increased, however, extracting meaningful insight in a timely manner became more complex. Therefore, opportunities are being missed and effort is being wasted. To compete, businesses in the 21st century are demanding the tools to derive true value from their data.
Lenovo Solutions for Microsoft* SQL Server on Windows are optimized for both Online Transaction Processing (OLTP) and Decision Support System(DSS). This solution features Microsoft SQL Server 2019 running on high performance Lenovo ThinkAgile MX certified nodes for Azure Stack Hyper Converged Infrastructure (HCI). The nodes are configured with 2nd Gen Intel Xeon Scalable processors, TruDDR4 2933MHz memory, 430 16i or 430 8i SAS HBAs, Mellanox CX4 (RoCE) or Qlogic (iWARP) RDMA NICs and a variety of storage options. This solution is ideal to solve highly demanding mission critical SQL database applications with databases over 300 TB across all the nodes in a ThinkAgile MX cluster. The ThinkAgile MX offering is ideal for consolidating legacy SQL servers which might be close to end of life/support.
Enterprise database solutions with faster time to value
Lenovo ThinkAgile MX certified nodes for Azure Stack HCI can be implemented anywhere from 2 node to 16 node clusters. These offerings are methodically tested and tuned to save you months of configuration, setup, testing, and tuning. With these offerings, you get the following advantages:
- ThinkAgile MX offerings are thoroughly tested and certified with optimal components to ensure highest levels of reliability and performance
- ThinkAgile MX offerings are validated by Microsoft under the Azure Stack HCI program
- Select from different levels of scalability, performance and price to suit your business needs
Highlights
- Reduce time to value with pretested and certified hardware configurations
- As a ThinkAgile offering, the specifications and performance results are validated by Lenovo for simplified evaluation, fast and easy deployment, and workload optimized performance
- Microsoft Certified solution with optimal compute, memory, storage and networking components for HCI
- Reduce TCO through better performance, rapid deployment and advanced hardware
- Optimize cost and performance with ThinkAgile MX configurations that have been verified by Intel via the Intel Select Solution program
Microsoft SQL Server 2019
The SQL Server 2019 database engine includes performance and scale improvements in diverse areas of functionality. Support for Persistent Memory (PMEM) devices is improved in this release. Any SQL Server file that is placed on a PMEM device operates in enlightened mode. SQL Server directly accesses the device, bypassing the storage stack of the operating system.
PMEM is also known as storage class memory (SCM). Also included are several features to improve the performance of columnstore indexes, such as automated columnstore index maintenance, better columnstore metadata memory management, a low memory load path for columnstore tables, and improved performance for bulk loading to columnstore indexes. The server startup process has been made faster for databases that use in memory columnstore tables for hybrid transactional and analytical processing (HTAP).
Lenovo ThinkAgile MX offerings are ideal for modernizing your legacy SQL Server applications because of their low cost and high performance capabilities. Microsoft Azure Stack HCI solutions use industry standard x86 servers to provide cost effective highly available infrastructure using software defined compute, storage and networking.
Lenovo ThinkAgile MX certified nodes in a multi node configuration offer the necessary performance for virtualized SQL Servers. High performance is achieved using Hyper V and Storage Spaces Direct technology which are built into Windows Server 2019. Several technologies like NVMe storage, Remote Direct Memory Access (RDMA) networking are natively supported in Windows Server to enable highest levels of performance.
Lenovo has measured over 2 million IOPs using a 4KB random IO profile with 90/10 read/write ratio on a 4 node ThinkAgile MX configuration.
This configuration features the following main components:
Server: Lenovo ThinkAgile MX
Processor: 2x 2nd Gen Intel Xeon Scalable, 8-28 cores
Memory: 192GB-3TB of TRUDDR4 memory
Storage:
- OS Storage: 2x 480GB M.2 SATA SSDs for the operating system (RAID 1)
- Cache Tier: 2-4x 750 GB Intel P4800X NVMe SSDs
- Capacity Tier: 4-20 Intel S4510 SATA SSDs -OR-
- Single Tier: Intel P4610 NVMe SSDs
Software: Microsoft Windows Server 2019 Standard Edition Microsoft SQL Server 2019 Enterprise Edition
This high performance database solution with Microsoft SQL Server 2019 Enterprise Edition features the Intel P4610 NVMe SSDs. These SSDs help build a low latency solution for mission critical SQL Server applications.
| High Performance configuration with Lenovo ThinkAgile MX certified nodes and Intel P4610 NVMe U.2 SSDs |  |
Best practices for running SQL Server on ThinkAgile MX
For a high performance SQL Server virtualized solution, implement the following best practices:
- Configure UEFI (Bios) settings to set Operating mode to Maximum performance.
- Configure power profile in Windows Server to ‘High performance’.
- Enable lock pages in memory option using Windows Group policy tool to prevent paging of data.
- Configure high availability for the operating system with 2 disk Raid 1.
- SQL database and log drives should be formatted with 64KB NTFS cluster size as it enhances the I/O performance without adding any overhead.
- The OS and SQL binary drives should be formatted with the standard 4KB NTFS cluster size.
- To achieve high performance, the database should be split into multiple files across multiple virtual disks. In general, one database file per vCPU is ideal. For example a VM with 4 vCPUs hosting 400GB database could be split into four 100GB database files and should be spread evenly across the two virtual disks.
- TempDB is used as scratch space by the applications and is one of the most important factors of SQL performance. The number of TempDB files to be used is based on the vCPU count. If the vCPUs are less than 8, then configure the same number of TempDB files. If the number of vCPUs is higher than 8, then start with 8 TempDB files and monitor the contention for in memory allocation. The number of TempDB file should be increased in increments of four until contention is eliminated.
- If the server is dedicated to SQL Server workload:
- Set processor affinity for SQL Server to use all the processors in the system.
- Set SQL Server Maximum Server Memory to 90% of the total memory available on the server.
Lenovo ThinkAgile MX certified node
Solution Benefits
- High performance HCI solution from Lenovo
- Balanced and optimized configuration
- A high-performance solution with 2nd Gen Intel Xeon scalable processors, Intel Optane P4800X storage or Intel P4610 NVMe storage with SQL Server 2019 Enterprise Edition
- Reduced time to value
Performance testing details and results
| HammerDB Configuration and Parameters | ||
|---|---|---|
| HammerDB is an open source load testing and benchmarking tool for databases available at: http://www.hammerdb.com. It offers tools for testing performance on OLTP and Analytics workloads. OLTP workloads is based on TPC-C benchmark from http://www.tpc.org and Analytics workload is based on TPC-H benchmark from tpc.org. The TPC-C workload was used to perform testing for this document. Here are some details of the testing and results. HammerDB was run on a separate Load server. | ||
| Hardware Configuration | 12x ThinkAgile MX nodes; each with: 2x Intel Xeon 8276 processors, 768GB memory, 2x P4800Xs + 4x S4510s | |
| Virtual Machine configuration | 2x VMs per node; each with: 36 vCPUs and 64GB memory | |
| Database tested | MS SQL Server 2019 Enterprise Edition | |
| Database Size per VM | 100GB | |
| Run time parameters (per VM) | ||
| Virtual users | 400 | |
| User delay | 1ms | |
| Repeat delay | 1ms | |
| Performance results | HammerDB TPC-C SQL Server | |
| vCPUs, Memory, Storage | Batch Requests per Second | Transactions per minute |
| 1-node | 77,462 | 4,760,406 |
| 2-node | 146,774 | 8,997,626 |
| 4-node | 289,805 | 17,769,720 |
| 8-node | 594,206 | 36,466,551 |
| 12-node | 902,952 | 55,729,800 |
Bill of Materials
| Part number | Product Description | Quantity |
| 7Z20CTO2WW | Server : ThinkAgile MX Certified Node - All Flash | 12 |
| B4E3 | ThinkAgile MX Certified Node - All Flash | 12 |
| B4H8 | Intel Xeon Platinum 8276 28C 165W 2.2GHz Processor | 24 |
| B4H3 | ThinkSystem 32GB TruDDR4 2933MHz (2Rx4 1.2V) RDIMM | 288 |
| B49C | ThinkSystem 2.5" Intel S4510 3.84TB Entry SATA 6Gb Hot Swap SSD | 48 |
| B2ZJ | ThinkSystem U.2 Intel Optane P4800X 750GB Performance NVMe PCIe 3.0 x4 Hot Swap SSD | 24 |
| ATRP | Mellanox ConnectX-4 2x100GbE/EDR IB QSFP28 VPI Adapter | 24 |
| AUKH | ThinkSystem 1Gb 4-port RJ45 LOM | 12 |
| B3XS | XClarity 3 Year | 12 |
| B6P2 | Windows Server Datacenter 2019 for Microsoft Azure Stack HCI - English (factory installed) | 12 |
| AVWF | ThinkSystem 1100W (230V/115V) Platinum Hot-Swap Power Supply | 24 |
| 6201 | 1.5m, 10A/100-250V, C13 to IEC 320-C14 Rack Power Cable | 24 |
| AXCA | ThinkSystem Toolless Slide Rail | 12 |
| B4NL | ThinkSystem SR650 Refresh MB | 12 |
| 9200 | Preload Specify | 12 |
| 9205 | Drop-in-the-Box Specify | 12 |
| 9201 | Windows Specify | 12 |
| AUR5 | ThinkSystem 2U/Twr 2.5" AnyBay 8-Bay Backplane | 12 |
| AURA | ThinkSystem 2U/Twr 2.5" SATA/SAS 8-Bay Backplane | 24 |
| 5977 | Select Storage devices - no configured RAID required | 12 |
| AUNL | ThinkSystem 430-8i SAS/SATA 12Gb HBA | 36 |
| AUMV | ThinkSystem M.2 with Mirroring Enablement Kit | 12 |
| B11V | ThinkSystem M.2 5100 480GB SATA 6Gbps Non-Hot Swap SSD | 24 |
| AURC | ThinkSystem SR550/SR590/SR650 (x16/x8)/(x16/x16) PCIe FH Riser 2 Kit | 12 |
| AUR3 | ThinkSystem SR550/SR590/SR650 x16/x8 PCIe FH Riser 1 Kit | 12 |
| AUPW | ThinkSystem XClarity Controller Standard to Enterprise Upgrade | 12 |
| AUK7 | Feature Win Server 2016 Configuration (TPM 2.0 and Secure Boot) | 12 |
| AURD | ThinkSystem 2U left EIA Latch Standard | 12 |
| AUTJ | ThinkSystem common Intel Label | 12 |
| AUSA | Lenovo ThinkSystem M3.5" Screw for EIA | 48 |
| AVJ2 | ThinkSystem 4R CPU HS Clip | 24 |
| AURP | Lenovo ThinkSystem 2U 2FH Riser Bracket | 12 |
| AUSU | ThinkSystem Package for SR650 | 12 |
| B51V | MX Badge 1 | 12 |
| B51W | MX Series 2U Agency Label 2U | 12 |
| B13M | ThinkAgile EIA Plate | 12 |
| B0ML | Feature Enable TPM on MB | 12 |
| AWFF | ThinkSystem SR650 WW Lenovo LPK | 12 |
| B7E5 | MR customer specific - ROW | 12 |
| AUSG | ThinkSystem SR650 6038 Fan module | 12 |
| AUT8 | ThinkSystem 1100W RDN PSU Caution Label | 12 |
| AUTQ | ThinkSystem small Lenovo Label for 24x2.5"/12x3.5"/10x2.5" | 12 |
| AUTU | ThinkSystem 4-7 NVMe sequence Label for 16x2.5"and 24x2.5" | 12 |
| B173 | Companion Part for XClarity Controller Standard to Enterprise Upgrade in Factory | 12 |
| AUTY | ThinkSystem 12-15 sequence Label for 24x2.5"Chassis | 12 |
| AURR | ThinkSystem M3.5 Screw for Riser 2x2pcs and Planar 5pcs | 48 |
| AUSF | Lenovo ThinkSystem 2U MS CPU Performance Heatsink | 24 |
| AVEN | ThinkSystem 1x1 2.5" HDD Filler | 120 |
| AVEP | ThinkSystem 4x1 2.5" HDD Filler | 24 |
| B31F | ThinkSystem M.2 480GB SSD Thermal Kit | 12 |
| AWF9 | ThinkSystem Response time Service Label LI | 12 |
| B13Q | ThinkAgile 2U Service Label LI | 12 |
| AUTA | XCC Network Access Label | 12 |
| AURQ | Lenovo ThinkSystem 2U 3FH Riser Bracket | 12 |
| AUSH | MS First 2U 8x2.5" HDD BP Cable Kit | 12 |
| AUSM | MS 2nd 2U 8X2.5" Cable Kit | 24 |
| AUSQ | On Board to 2U 8x2.5" HDD BP NVME Cable | 12 |
| A2HP | Configuration ID 01 | 36 |
| 7S05CTO3WW | Windows Server 2019 | 12 |
| S09E | Windows Server 2019 Datacenter (16 core) - English (factory installed) | 12 |
| 3444 | Serial Number Only | 12 |
| 3523 | Drop-in-the-Box | 12 |
| 5641PX3 | XClarity Pro, Per Endpoint w/3 Yr SW S&S | 12 |
| 1340 | Lenovo XClarity Pro, Per Managed Endpoint w/3 Yr SW S&S | 12 |
| 3444 | Serial Number Only | 12 |
| 7S05CTO6WW | Windows Server 2019 Additional License | 12 |
| S090 | Windows Server 2019 Datacenter Additional License (2 core) (No Media/Key) (POS Only) | 240 |
| 5372SWX | xSeries HIPO | 12 |
| S094 | Windows Server 2019 Datacenter Additional License (2 core) (No Media/Key) (POS Only) | 240 |
| 5372SWX | xSeries HIPO | 12 |
| S0AP | Windows Server 2019 Datacenter (16 core) - English (factory installed) | 12 |
Powered by Lenovo ThinkAgile MX
The Lenovo ThinkAgile MX Certified Nodes support compute and storage functions for this solution. These nodes are designed for deploying highly available, highly scalable hyper converged infrastructure and software defined storage from Microsoft on Lenovo enterprise platforms that feature the second generation of the Intel Xeon Processor Scalable Family. The ThinkAgile MX Certified Nodes deliver fully validated and integrated Lenovo hardware and firmware that is certified with Microsoft Azure Stack HCI.
Why Lenovo
Lenovo is a leading provider of x86 servers for the data center. Featuring rack, tower, blade, dense and converged systems, the Lenovo server portfolio provides excellent performance, reliability and security. Lenovo also offers a full range of networking, storage, software, solutions, and comprehensive services supporting business needs throughout the IT lifecycle. With options for planning, deployment, and support, Lenovo offers expertise and services needed to deliver better service level agreements and generate greater end user satisfaction.
For More Information
To learn more about the Lenovo Database Validated Design for Microsoft SQL Server OLTP on ThinkAgile MX, contact your Lenovo Business Partner or visit: www.lenovo.com/systems
Why Intel Select SolutionsKey benefits of investing in an Intel Select Solution from Lenovo include:
|
 |
Related product families
Product families related to this document are the following:
Trademarks: Lenovo, the Lenovo logo, AnyBay®, ThinkAgile®, ThinkSystem®, TruDDR4, XClarity®, and xSeries® are trademarks or registered trademarks of Lenovo. Intel®, Intel Optane™, and Xeon® are trademarks of Intel Corporation or its subsidiaries. Azure®, Microsoft®, SQL Server®, Windows Server®, and Windows® are trademarks of Microsoft Corporation in the United States, other countries, or both. TPC, TPC-C, and TPC-H are trademarks of Transaction Processing Performance Council. Other company, product, or service names may be trademarks or service marks of others.
DBSMS23XX93
