WIXLIB

Lenovo Database Configuration forMicrosoft SQL Server 2019on ThinkAgile MXReduce time to value with pretested hardware configurationsData growth problem and a solutionThe rapid growth of technology means that the amount of available data and the ability to collect thatdata increased to a level unthinkable as little as five years ago. As the volume and velocity of dataincreased, 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 centuryare demanding the tools to derive true value from their data.Lenovo Solutions for Microsoft* SQL Server on Windows are optimized for both Online TransactionProcessing (OLTP) and Decision Support System(DSS). This solution features Microsoft SQL Server2019 running on high performance Lenovo ThinkAgile MX certified nodes for Azure Stack HyperConverged 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 missioncritical SQL database applications with databases over 300 TB across all the nodes in a ThinkAgile MXcluster. The ThinkAgile MX offering is ideal for consolidating legacy SQL servers which might be closeto end of life/support.Enterprise database solutions with faster time to valueLenovo ThinkAgile MX certified nodes for Azure Stack HCI can be implemented anywhere from 2 nodeto 16 node clusters. These offerings are methodically tested and tuned to save you months ofconfiguration, setup, testing, and tuning. With these offerings, you get the following advantages:ThinkAgile MX offerings are thoroughly tested and certified with optimal components to ensurehighest levels of reliability and performanceThinkAgile MX offerings are validated by Microsoft under the Azure Stack HCI programSelect from different levels of scalability, performance and price to suit your business needsHighlightsReduce time to value with pretested and certified hardware configurationsAs a ThinkAgile offering, the specifications and performance results are validated by Lenovo forsimplified evaluation, fast and easy deployment, and workload optimized performanceMicrosoft Certified solution with optimal compute, memory, storage and networking componentsfor HCIReduce TCO through better performance, rapid deployment and advanced hardwareOptimize cost and performance with ThinkAgile MX configurations that have been verified by Intelvia the Intel Select Solution program

2 Microsoft SQL Server 2019 on ThinkAgile MXMicrosoft SQL Server 2019The SQL Server 2019 databaseengine includes performanceand scale improvements indiverse areas of functionality.Support for Persistent Memory(PMEM) devices is improved inthis release. Any SQL Server filethat is placed on a PMEM deviceoperates in enlightened mode.SQL Server directly accesses thedevice, bypassing the storagestack of the operating system.Lenovo ThinkAgile MX offeringsare ideal for modernizing yourlegacy SQL Server applicationsbecause of their low cost andhigh performance capabilities.Microsoft Azure Stack HCIsolutions use industry standardx86 servers to provide costeffective highly availableinfrastructure using softwaredefined compute, storage andnetworking.PMEM is also known as storageclass memory (SCM). Alsoincluded are several features toimprove the performance ofcolumnstore indexes, such asautomated columnstore indexmaintenance, better columnstoremetadata memory management,a low memory load path forcolumnstore tables, andimproved performance for bulkloading to columnstore indexes.The server startup process hasbeen made faster for databasesthat use in memory columnstoretables for hybrid transactionaland analytical processing(HTAP).Lenovo ThinkAgile MX certifiednodes in a multi nodeconfiguration offer the necessaryperformance for virtualized SQLServers. High performance isachieved using Hyper V andStorage Spaces Directtechnology which are built intoWindows Server 2019. Severaltechnologies like NVMe storage,Remote Direct Memory Access(RDMA) networking are nativelysupported in Windows Server toenable highest levels ofperformance.Lenovo has measured over 2million IOPs using a 4KB randomIO profile with 90/10 read/writeratio on a 4 node ThinkAgile MXconfiguration.High Performance configuration with Lenovo ThinkAgile MX certifiednodes and Intel P4610 NVMe U.2 SSDsThis configuration features thefollowing main components:Server: Lenovo ThinkAgile MXProcessor: 2x 2nd Gen IntelXeon Scalable, 8-28 coresMemory: 192GB-3TB ofTRUDDR4 memoryStorage:OS Storage: 2x 480GB M.2SATA SSDs for the operatingsystem (RAID 1)Cache Tier: 2-4x 750 GBIntel P4800X NVMe SSDsCapacity Tier: 4-20 IntelS4510 SATA SSDs -ORSingle Tier: Intel P4610NVMe SSDsSoftware: Microsoft WindowsServer 2019 Standard EditionMicrosoft SQL Server 2019Enterprise EditionThis high performance databasesolution with Microsoft SQLServer 2019 Enterprise Editionfeatures the Intel P4610 NVMeSSDs. These SSDs help build alow latency solution for missioncritical SQL Server applications.

3 Microsoft SQL Server 2019 on ThinkAgile MXBest practices for running SQL Server on ThinkAgile MXFor 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/Operformance 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 databasecould 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 SQLperformance. The number of TempDB files to be used is based on the vCPU count. If the vCPUs are lessthan 8, then configure the same number of TempDB files. If the number of vCPUs is higher than 8, thenstart with 8 TempDB files and monitor the contention for in memory allocation. The number of TempDBfile 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 nodeSolution BenefitsHigh performance HCI solution from LenovoBalanced and optimized configurationA high-performance solution with 2nd Gen Intel Xeon scalable processors, Intel Optane P4800Xstorage or Intel P4610 NVMe storage with SQL Server 2019 Enterprise EditionReduced time to value

4 Microsoft SQL Server 2019 on ThinkAgile MXPerformance testing details and resultsHammerDB Configuration and ParametersHammerDB is an open source load testing and benchmarking tool for databases available at: http://www.hammerdb.com. It offers tools for testingperformance on OLTP and Analytics workloads. OLTP workloads is based on TPC-C benchmark from http://www.tpc.org and Analytics workloadis 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 thetesting and results. HammerDB was run on a separate Load server.Hardware Configuration12x ThinkAgile MX nodes; each with: 2x Intel Xeon 8276 processors, 768GB memory, 2x P4800Xs 4x S4510sVirtual Machine configuration2x VMs per node; each with: 36 vCPUs and 64GB memoryDatabase testedMS SQL Server 2019 Enterprise EditionDatabase Size per VM100GBRun time parameters (per VM)Virtual users400User delay1msRepeat delay1msPerformance resultsHammerDB TPC-C SQL ServervCPUs, Memory, StorageBatch Requests per SecondTransactions per de902,95255,729,800Bill of MaterialsPart numberProduct DescriptionQuantity7Z20CTO2WWServer : ThinkAgile MX Certified Node - All Flash12B4E3ThinkAgile MX Certified Node - All Flash12

5 Microsoft SQL Server 2019 on ThinkAgile MXB4H8Intel Xeon Platinum 8276 28C 165W 2.2GHz Processor24B4H3ThinkSystem 32GB TruDDR4 2933MHz (2Rx4 1.2V) RDIMM288B49CThinkSystem 2.5" Intel S4510 3.84TB Entry SATA 6Gb Hot Swap SSD48B2ZJThinkSystem U.2 Intel Optane P4800X 750GB Performance NVMe PCIe 3.0 x4 Hot Swap SSD24ATRPMellanox ConnectX-4 2x100GbE/EDR IB QSFP28 VPI Adapter24AUKHThinkSystem 1Gb 4-port RJ45 LOM12B3XSXClarity 3 Year12B6P2Windows Server Datacenter 2019 for Microsoft Azure Stack HCI - English (factory installed)12AVWFThinkSystem 1100W (230V/115V) Platinum Hot-Swap Power Supply2462011.5m, 10A/100-250V, C13 to IEC 320-C14 Rack Power Cable24AXCAThinkSystem Toolless Slide Rail12B4NLThinkSystem SR650 Refresh MB129200Preload Specify129205Drop-in-the-Box Specify129201Windows Specify12AUR5ThinkSystem 2U/Twr 2.5" AnyBay 8-Bay Backplane12AURAThinkSystem 2U/Twr 2.5" SATA/SAS 8-Bay Backplane245977Select Storage devices - no configured RAID required12AUNLThinkSystem 430-8i SAS/SATA 12Gb HBA36AUMVThinkSystem M.2 with Mirroring Enablement Kit12B11VThinkSystem M.2 5100 480GB SATA 6Gbps Non-Hot Swap SSD24AURCThinkSystem SR550/SR590/SR650 (x16/x8)/(x16/x16) PCIe FH Riser 2 Kit12AUR3ThinkSystem SR550/SR590/SR650 x16/x8 PCIe FH Riser 1 Kit12AUPWThinkSystem XClarity Controller Standard to Enterprise Upgrade12AUK7Feature Win Server 2016 Configuration (TPM 2.0 and Secure Boot)12AURDThinkSystem 2U left EIA Latch Standard12AUTJThinkSystem common Intel Label12AUSALenovo ThinkSystem M3.5" Screw for EIA48AVJ2ThinkSystem 4R CPU HS Clip24AURPLenovo ThinkSystem 2U 2FH Riser Bracket12AUSUThinkSystem Package for SR65012B51VMX Badge 112B51WMX Series 2U Agency Label 2U12B13MThinkAgile EIA Plate12B0MLFeature Enable TPM on MB12AWFFThinkSystem SR650 WW Lenovo LPK12B7E5MR customer specific - ROW12AUSGThinkSystem SR650 6038 Fan module12AUT8ThinkSystem 1100W RDN PSU Caution Label12AUTQThinkSystem small Lenovo Label for 24x2.5"/12x3.5"/10x2.5"12AUTUThinkSystem 4-7 NVMe sequence Label for 16x2.5"and 24x2.5"12B173Companion Part for XClarity Controller Standard to Enterprise Upgrade in Factory12AUTYThinkSystem 12-15 sequence Label for 24x2.5"Chassis12AURRThinkSystem M3.5 Screw for Riser 2x2pcs and Planar 5pcs48AUSFLenovo ThinkSystem 2U MS CPU Performance Heatsink24AVENThinkSystem 1x1 2.5" HDD Filler120AVEPThinkSystem 4x1 2.5" HDD Filler24B31FThinkSystem M.2 480GB SSD Thermal Kit12AWF9ThinkSystem Response time Service Label LI12B13QThinkAgile 2U Service Label LI12AUTAXCC Network Access Label12

6 Microsoft SQL Server 2019 on ThinkAgile MXAURQLenovo ThinkSystem 2U 3FH Riser Bracket12AUSHMS First 2U 8x2.5" HDD BP Cable Kit12AUSMMS 2nd 2U 8X2.5" Cable Kit24AUSQOn Board to 2U 8x2.5" HDD BP NVME Cable12A2HPConfiguration ID 01367S05CTO3WWWindows Server 201912S09EWindows Server 2019 Datacenter (16 core) - English (factory installed)123444Serial Number Only123523Drop-in-the-Box125641PX3XClarity Pro, Per Endpoint w/3 Yr SW S&S121340Lenovo XClarity Pro, Per Managed Endpoint w/3 Yr SW S&S123444Serial Number Only127S05CTO6WWWindows Server 2019 Additional License12S090Windows Server 2019 Datacenter Additional License (2 core) (No Media/Key) (POS Only)2405372SWXxSeries HIPO12S094Windows Server 2019 Datacenter Additional License (2 core) (No Media/Key) (POS Only)2405372SWXxSeries HIPO12S0APWindows Server 2019 Datacenter (16 core) - English (factory installed)12