skip to main content

SQL Server 2022 on Lenovo ThinkAgile MX650 V3 Series

Solution Brief

Home
Top
Published
27 Sep 2023
Form Number
LP1826
PDF size
9 pages, 336 KB

Abstract

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 with up to 60 cores, up to 16x TruDDR5 4800MHz memory DIMMs per socket and P5620 NVMe drives among a variety of storage options, including support for the PCIe 5.0 standard devices for I/O.

Introduction

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.
Lenovo ThinkAgile MX650 V3 2U Integrated System and Certified Node with 2.5-inch drive bays
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.

Key benefits

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.

Excellent value

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.

ThinkAgile MX Series

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

Table 1. ThinkAgile MX650 V3 and MX630 V3 specifications
Model ThinkAgile MX Series (Integrated Systems & Certified Node) – SR650 V3 ThinkAgile MX Series (Integrated Systems & Certified Node) – SR630 V3
Form Factor 2U 1U
Segment Hyperconverged Infrastructure – Microsoft Azure Stack HCI solutions
Processor 2x 4th generation Intel Xeon Scalable processors, 8-120 cores
Memory Up to 8TB in 32x slots, using 16GB-256GB DIMMS
Network 10/25GbE adapters (RoCE or iWARP), 100GbE (RoCE) RDMA network adapters
Drive Bays 16x 3.5", 28x 2.5" 4x 3.5", 12x 2.5"
Storage: Capacity Tier Depending on configuration:
4-14x HDDs: 1TB to 14TB
4-30x SSDs: 480GB to 7.68TB
4-24x NVMe: 750GB to 7.68TB
Depending on configuration:
4-10x HDDs: 600GB to 14TB
4-12x SSDs: 480GB to 7.68TB
4-12x NVMe: 750GB to 7.68TB
4-16x E1.S NVMe: 4TB
Storage: Cache Tier Depending on configuration:
2-6x SSDs: 800GB to 6.4TB
2-8x NVMe: 750GB to 6.4TB
Depending on configuration:
2-4x SSDs: 800GB to 6.4TB
2-4x NVMe: 750GB to 6.4TB
Systems Management Optional hardware management via Lenovo XClarity and resource management through Microsoft Windows Admin Center
License Options Azure Stack HCI Software (Included with Integrated Systems), Windows Server 2022 Datacenter (optional)
Warranty and Support Lenovo 3-year, 4-year, or 5-year hardware warranty; ThinkAgile Advantage Single Point of Support where available (included with Integrated Systems)

Business database solutions with faster time-to-value

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.

Highlights

  • Reduce time to value with pretested and sized hardware configurations.
  • Simplified evaluation, fast and easy deployment and workload optimized performance.
  • Database sized solution with optimal compute, memory, storage and networking components.
  • Reduce TCO through better performance, rapid deployment and advanced hardware.
  • Optimize performance with pretested ThinkSystem MX650 V3 hardware configurations.

 

Microsoft SQL Server 2022

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.

Best practices for SQL Server on ThinkSystem MX650 V3

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.

Performance testing and results

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.

Table 2. HammerDB TPC-C benchmark results
Metric 8 Virtual Machines 16 Virtual Machines
New Orders Per Minute (NOPM) 3.2 million 2.8 million
Transactions Per Minute (TPM) 7.4 million 6.6 million

Bill of materials

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

Table 3. Bill of materials
Feature code Description Quantity
7D76CTO1WW Server: ThinkSystem MX650 V3 - 3yr Warranty 1
BLKK ThinkSystem V3 2U 24 x 2.5" Chassis 1
BNOM Intel Xeon Platinum 8480+ 56C 350W 2.0GHz Processor 2
BNFC ThinkSystem 128GB TruDDR5 4800 MHz (4Rx4) 3DS RDIMM 32
B8NY ThinkSystem RAID 940-8i 4GB Flash PCIe Gen4 12Gb Adapter 1
BNEG ThinkSystem 2.5" U.2 P5620 1.6TB Mixed Use NVMe PCIe 4.0 x4 HS SSD 8
B8LU ThinkSystem 2U 8 x 2.5" SAS/SATA Backplane 1
BH8D ThinkSystem 2U/4U 8 x 2.5" NVMe Backplane 1
BM8X ThinkSystem M.2 SATA/x4 NVMe 2-Bay Enablement Kit 1
AUUV ThinkSystem M.2 128GB SATA 6Gbps Non-Hot Swap SSD 2
B93E ThinkSystem Intel I350 1GbE RJ45 4-port OCP Ethernet Adapter 1
BLKM ThinkSystem V3 2U x16/x16/E PCIe Gen4 Riser1 or 2 2
BMUF ThinkSystem 1800W 230V Platinum Hot-Swap Gen2 Power Supply 2
BLL6 ThinkSystem 2U V3 Performance Fan Module 6
BRQ1 ThinkSystem SR650 V3,SATA CBL,SLx8-SLx4,M.2-M.2(MB),150mm 1
BSYM ThinkSystem SR650 V3,PCIe4 Cable,Swift8x-SL8x,2in1,PCIe 6/5(MB) to BP1/BP2/td> 1
BETS ThinkSystem V3 2U SFF C0 (RAID) to Front 8x2.5" BP1 1
BPE3 ThinkSystem MX650 V3 MCIO8x to SL8x CBL, PCIe4, 8x2.5 AnyBay, 200mm 2
BQ12 G4 x16/x16/E PCIe Riser BLKM for Riser 1 Placement 1
BQ19 G4 x16/x16/E PCIe Riser BLKM for Riser 2 Placement 1
7S0XCTO2WW Lenovo XClarity XCC2 Platinum Upgrade 1
5641PX3 XClarity Pro, Per Endpoint w/3 Yr SW S&S 1
1340 Lenovo XClarity Pro, Per Managed Endpoint w/3 Yr SW S&S 1
QAA8 MX650 V3 3Y STANDARD 1

Lenovo and Microsoft

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.

Accelerated by Intel

Accelerated by IntelTo 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.

For more information

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

Authors

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.

Trademarks

Lenovo and the Lenovo logo are trademarks or registered trademarks of Lenovo in the United States, other countries, or both. A current list of Lenovo trademarks is available on the Web at https://www.lenovo.com/us/en/legal/copytrade/.

The following terms are trademarks of Lenovo in the United States, other countries, or both:
Lenovo®
AnyBay®
ThinkAgile®
ThinkSystem®
XClarity®

The following terms are trademarks of other companies:

Intel® and Xeon® are trademarks of Intel Corporation or its subsidiaries.

Microsoft®, Azure®, Hyper-V®, SQL Server®, Windows Server®, and Windows® are trademarks of Microsoft Corporation in the United States, other countries, or both.

TPC® and TPC-C® are trademarks of Transaction Processing Performance Council.

Other company, product, or service names may be trademarks or service marks of others.