Lenovo Database Configuration for

Microsoft SQL Server 2019 on ThinkAgile MXSolution Brief

Updated
30 Jan 2020
Form Number
SB0002
PDF size
7 pages, 274 KB

Reduce 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   Intel%20P4610%20NVMe%20drive.jpg

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

 

 

SQL%20Server%20batch%20graph.png

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/solutions

 

Why Intel Select Solutions

Key benefits of investing in an Intel Select Solution from Lenovo include:

  • Simplified evaluation. New workload integration and the transition to software-defined infrastructure are two areas where IT managers spend more and more time and money sorting through endless options, searching for optimal solutions. Intel Select Solutions are tightly specified in terms of hardware and software components to eliminate guesswork and speed decision-making.

  • Fast and easy deployment. With pre-defined settings and rigorous system-wide tuning, Intel Select Solutions are designed to increase efficiency in IT's testing process, speed time to service delivery, and increase confidence in solution performance.

  • Workload-optimized performance. Intel Select Solution configurations are designed by Intel and our partners to deliver to a performance threshold for the workload, and are built on the latest Intel architecture foundation including the recently-launched Intel® Xeon® Scalable platforms.

  Intel%20Select%20Solutions%20logo.jpg

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®, Optane™, and Xeon® are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries. 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