SQL Server 2022 on Lenovo ThinkAgile MX650 V3 Series

Deploying hyperconverged infrastructure has become the de-facto standard for organizations looking to modernize their aging infrastructure. Large storage deployments are increasingly being replaced by HCI based solutions for most general-purpose workloads. HCI has proven to deliver better efficiency and price performance in the datacenter. Additionally, customers have been choosing a hybrid approach, migrating certain workloads to the cloud, while keeping other workloads on-premises.

Azure Stack HCI, is Microsoft’s HCI solution for customers that wish to run workloads on-premises and extend easily to Azure for hybrid capabilities such as back-up, site recovery, storage, cloud-based monitoring and more.

Azure Stack HCI is a new HCI host operating system from Microsoft, delivered as an Azure service, providing the latest and up-to-date security, performance and feature updates. Azure Stack HCI builds on the foundation of the Microsoft Windows Server Software Defined program and provides a certification path for Storage Spaces Direct solutions.

Lenovo ThinkAgile MX Series (Integrated Systems and Certified Nodes) combine the Storage Spaces Direct technology included in this new host OS, with industry leading Lenovo servers to deliver HCI building blocks that build your infrastructure solutions. Lenovo ThinkAgile MX Integrated Systems map to Microsoft Azure Stack HCI Integrated Systems and ThinkAgile MX Certified Nodes map to Microsoft Azure Stack HCI Validated Nodes.

Figure 1. Lenovo ThinkAgile MX650 V3

Lenovo Solutions for Microsoft SQL Server on ThinkAgile MX650 V3 are optimized for both Online Transaction Processing (OLTP) and Data Warehouse (DW) and are Accelerated by Intel offerings. This technical brief features Microsoft SQL Server 2022 running on a high-performance ThinkAgile MX650 V3 servers in a 2-node cluster. The servers are configured with 4th Generation Intel® Xeon® Scalable processors, TruDDR5 4800MHz memory and P5620 NVMe drives among a variety of storage options, including support for the PCIe 5.0 standard devices for I/O. These new processors from Intel offer anywhere from 16 to 60 cores and 16x 4800 MHz DDR5 DIMMs per socket.

The MX650 V3 solution is a storage dense offering, with up to 40 2.5" drive bays in the front, middle and rear of the server and 5 different slot configurations at the rear of the server. Onboard NVMe PCIe ports allow direct connections to 16 NVMe SSDs, which frees up PCIe slots and lowers NVMe solution acquisition costs.

ThinkAgile MX Series are built with certified hardware components, are easily orderable as appliances, and include deployment/update features built into Windows Admin Center. Benefits like these allow businesses to deploy solutions quickly and easily.

The solution offers the following benefits:

• Simplicity: Lenovo XClarity extension integrates with Windows Admin Center from Microsoft, providing a unified single-pane-of-glass simple management experience across infrastructure, virtual machines, software, and hardware.
• Flexibility: ThinkAgile MX Nodes offer a wide range of processor, memory, and storage options to provide the maximum flexibility for customizing your infrastructure.
• Faster time to value: In traditional IT, a solution is designed, tested, implemented, and maintained requiring significant time and energy. With HCI, this burden has been transferred to the solution provider. Microsoft created S2D to solve the storage challenge and Azure Stack HCI solutions to ensure robust and viable platforms.

Lenovo has designed, tested, and validated ThinkAgile MX Series offerings to provide the solutions you need quickly and easily with the confidence required, to exceed the stringent requirements of today’s IT. The result is that you can quickly deploy a robust, high-performance HCI solution and rapidly solve your IT challenges.

The ThinkSystem MX650 V3 2U server nodes are equipped with two powerful 4th generation Intel® Xeon® Scalable processors, providing extreme reliability, huge internal storage and memory capacity, and a large number of I/O slots, making it an ideal foundation for this solution.

Lenovo and Lenovo partners have a comprehensive portfolio of professional services that supports the full life cycle of your infrastructure. At every stage—from planning to deploying, supporting, optimizing, and end of life—extra help is available to accelerate meeting your business objectives.

ThinkAgile MX Integrated Systems include Microsoft Azure Stack HCI, and optionally include Windows Server 2022 Datacenter in case you require unlimited guest OS virtual machine licenses. ThinkAgile MX Certified nodes optionally include the Azure Stack HCI software or Windows Server 2022 Datacenter depending on their needs.

The Azure Stack HCI OS license provides the following benefits:

• Storage Spaces Direct (S2D): State of the art software-defined storage from Microsoft with multiple high-performance resiliency options, deduplication, compression and more.
• Windows Admin Center (WAC): A web-based management portal is included with the software at no extra cost. Deployment and update features in WAC make deployment extremely simple and easy to perform. Additionally, Lenovo’s XClarity plugin allows you to deploy their hardware as well as software from the same interface, enabling single pane of management.
• Hyper-V: Hypervisor is included in the license
• Software-Defined Networking: Features such as virtual network encryption, firewall auditing, virtual network peering allows you to get the benefits of a more secure software defined network with Azure Stack HCI.
• Azure Stack HCI is hybrid by design, and you can benefit from native integration with Azure ARC and Azure Monitor and connect to Azure for a variety of Azure hybrid services seamlessly. Fleet management for hosts and VMs allows you to monitor and manage clusters at scale.

The flexible building block for your Microsoft Azure Stack HCI solutions.

Lenovo MX650 V3 systems are methodically tested and tuned to save you months of configuration, setup, testing, and tuning. With these new servers, you get the following advantages:

• Realize 40% better performance for workloads running on 4th generation Intel Xeon Scalable processors than on similar servers equipped with previous generation processors.
• Improve performance of SQL Server solutions with higher core counts, memory bandwidth and PCIe Gen 5 devices
• Improve density and support more and larger databases per host.

SQL Server 2022 includes updates to existing features like Intelligent Query Processing in addition to management, platform or language.

Starting with SQL 2022, runtimes for R, Python, and Java are no longer installed with SQL Setup. Instead, install any desired custom runtime(s) and packages.

Performance enhancements in SQL Server 2022 include:

• Improvements have been made to all columnstore indexes that benefit from enhanced segment elimination by data type.
• Concurrent updates to global allocation map pages reduce page latch contention
• Improvements in buffer pool scan operations on large-memory systems by using multiple CPU cores for parallel scans
• Improvements to Clustered ColumnStore Indices to sort existing data in memory before index builder compresses the data
• Support for Intel QuickAssist Technology (QAT) backup compression with software or hardware acceleration
• TempDB performance enhancements for scalability
• Shrink database uses low priority processing to minimize impact on performance
• In-memory OLTP enhancements

Management improvements include:

• Additional Azure integration
• Link to Azure SQL Managed Instance
• Accelerated Database Recovery (ADR)
• Always On Availability Group enhancements

Lenovo ThinkSystem MX650 V3 offerings are ideal for modernizing your legacy SQL Server applications because of their low cost and high-performance capabilities. They are industry standard x86 servers providing cost effective computing and fast high-density local storage.

Lenovo ThinkSystem MX650 V3 servers offer the necessary performance for bare metal or virtualized SQL Servers. High performance can be achieved using Hyper-V and Storage Spaces Direct technology which are built into Windows Server. Several technologies like NVMe storage and Remote Direct Memory Access (RDMA) networking are natively supported in Windows Server to enable the highest levels of performance.

This configuration features the following main components:

• Server: Lenovo ThinkSystem MX650 V3
• Processor: 2x 4th Gen Intel Xeon Platinum 8480+ processors, 56 core
• Memory: 4TB of TruDDR5 4800 MHz memory
• DB Storage: 6x Intel P5620 1.6TB NVMe SSDs
• Log Storage: 2x Intel P5620 1.6TB NVMe SSDs RAID-1
• OS Storage: 2x 480GB M.2 SATA SSDs for the operating system (RAID-1)
• Software:
  • Microsoft Windows Server 2022
  • Microsoft SQL Server 2022

This high-performance database solution with Microsoft SQL Server 2022 Enterprise Edition features the latest NVMe SSDs. These SSDs help build a low latency solution for mission critical SQL Server applications.

For a high-performance SQL Server solution, implement the following best practices:

• Configure UEFI settings to set Operating mode to Maximum performance.
• Enable Hyper-Threading in the UEFI.
• Configure power profile in Windows Server to "High performance".
• SQL Server database and log drives are recommended to be formatted with 64KB NTFS cluster size.
• SQL Server database and log files should be on separate physical drives.
• The OS and SQL Server binary drives are recommended to be formatted with standard 4KB NTFS cluster size.
• TempDB is shared by many processes and users as a temporary working area and should be configured appropriately. Default configuration will be suitable for most workloads. Use the install experience for guided configuration. More info in Microsoft TempDB Database documentation.
• If the server is dedicated to the SQL Server workload, use the default dynamic memory management model or follow Microsoft SQL documentation guidelines for manually configuring memory options if finer grain control is desired.

This section lists the configuration and HammerDB benchmark result for the MX650 V3.

HammerDB is an open-source load testing and benchmarking tool for databases, available from http:///www.hammerdb.com. It offers tools for testing performance of OLTP and Analytics workloads. The OLTP workload is based on TPC-C benchmark from http://www.tpc.org. Hammerdb was run on a separate load servers.

Server configuration (each server in a two-node cluster):

• ThinkAgile MX650 V3 with 4th Gen Intel Xeon Scalable Processors
• 2x Intel Xeon Platinum 8480+ processors
• 4TB memory
• Intel P5620 NVMe drives
• Microsoft SQL Server 2022 Enterprise Edition

Benchmarks simulated: TPC-C on 8 VMs and 16 VMs

Database size (TPC-C): Each VM has a 100 GB database, comprised of 800 warehouses. VMs were distributed equally between the two nodes

Run time parameters (TPC-C):

• Virtual users: 400
• User delay: 1 ms

The following table lists the benchmark results.

The following table lists the bill of materials (BOM) for the MX650 V3.

With co-located engineering organizations and a history of technical collaboration, Microsoft and Lenovo consistently deliver innovative joint solutions for the data center. Lenovo’s leadership in reliability, customer satisfaction, and performance, combined with Microsoft’s leadership in software and cloud services, continues to deliver innovative data center solutions and lower TCO for our joint customers.

To deliver the best experience possible, Lenovo and Intel have optimized this solution to leverage Intel capabilities like processor accelerators not available in other systems. Accelerated by Intel means enhanced performance to help you achieve new innovations and insight that can give your company an edge.

To learn more about this Lenovo solution contact your Lenovo Business Partner or visit the following pages;

• Reference Architecture: Microsoft SQL Server 2022 on Lenovo ThinkAgile MX with Azure Stack HCI
  https://lenovopress.lenovo.com/lp1876
• Lenovo database solutions:
  https://www.lenovo.com/us/en/servers-storage/solutions/database/
• Lenovo ThinkSystem MX650 V3:
  https://lenovopress.lenovo.com/lp1675-thinkagile-mx650-v3-2u-integrated-system-and-certified-node-4th-generation
• Microsoft SQL Server 2022:
  https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022? view=sql-server-ver16

Vinay Kulkarni is a Principal Technical Consultant in the Software and Solutions Development group at ISG, Lenovo. His current focus is on solution architectures in the areas of cloud, database analytics and AI. Vinay has more than 20 years’ experience with Lenovo, and IBM. During this time, Vinay has led diverse projects ranging from performance benchmark publications to solutions development, as well as business and technical strategy.

David West is a Lenovo solutions engineer working onsite at the Microsoft Redmond campus. He has 20 years of experience in the industry with a diverse IT background in solutions engineering, testing and performance tuning. He worked with Microsoft and IBM for the past 11 years on enterprise storage performance and tuning projects for SQL Server and Exchange Server. He has authored numerous technical papers on Microsoft enterprise solutions with IBM storage and System x servers. David has also contributed to System x technical architectures for SQL Data Warehouse Fast Track and the Exchange Solution Reviewed Program (ESRP). David is currently working on SQL performance and Microsoft cloud solutions such as Edge Cloud.

Laurentiu Petre is a Lenovo solutions engineer working in Bucharest, Romania. He has over 10 years of experience in the IT field being accustomed with large scale deployments of Microsoft Exchange and other Microsoft products. Before Lenovo, he managed the infrastructure of key players in telecommunications and petroleum exploitation companies. Laurentiu is currently working on SQL performance and Microsoft cloud solutions such as Edge Cloud.