skip to main content

Optimizing Microsoft SQL Server 2022 on Lenovo ThinkAgile HX650 V3

Solution Brief

Home
Top

Data growth problem and a solution

In today’s world information is an asset that demands the right tools to store and manage critical business data. Organizations of all sizes are being inundated by information at an increasing pace. It is important to choose a database solution that matches the requirements of the company, resulting in the most productive outcome. By consolidating SQL Server in virtual machines on a Nutanix cluster, database workloads can experience improved efficiency. SQL Server 2022 includes database and business intelligence features for organizations that need a full featured enterprise database solution.

Lenovo Solutions for Microsoft SQL Server on ThinkAgile HX650 V3 are optimized for both Online Transaction Processing (OLTP) and are Accelerated by Intel offerings. This technical brief features Microsoft SQL Server 2022 Enterprise Edition running in Nutanix virtual machines on compact, high-performance Lenovo dual-socket 2U rack mounted systems. The server is configured with 4th Generation Intel® Xeon® Scalable processors, TruDDR5 4800MT/s 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 up to 16x 4800 MT/s DDR5 DIMMs per socket.

The HX650 V3 server 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. The HX650 V3 servers include Nutanix AHV pre-loaded and ready to setup in a cluster configuration.

Business database solutions with faster time-to-value

Lenovo HX650 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
  • Consolidate SQL workloads, running on multiple Nutanix virtual machines
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 ThinkAgile HX650 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.

Here are some performance enhancements in SQL Server 2022:

  • 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 (only software compression is available in SQL Standard)
  • TempDB performance enhancements for scalability
  • Shrink database uses low priority processing to minimize impact on performance
  • In-memory OLTP enhancements

Here are some management improvements:

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

Lenovo ThinkAgile HX650 V3

Lenovo ThinkAgile HX650 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.

The HX650 V3 servers offer the necessary performance for virtualized SQL Servers. High performance server consolidation and management can be achieved using Nutanix hypervisor technology. Several technologies like NVMe storage and Remote Direct Memory Access (RDMA) networking enable the highest levels of performance.

There are several differences between the different SQL editions, too many to list in this document. A detailed comparison of SQL editions can be found here.

Each server configuration features the following main components:

  • Server: Lenovo ThinkAgile HX650 V3
  • Processor: 2x 4th Gen Intel Xeon Scalable, Platinum 8468 48C 2.1 GHz
  • Memory: 1TB of TRUDDR5 4800 MT/s memory
  • Storage pool: 4x Intel P5620 3.2TB NVMe SSDs
  • OS Storage: 2x 960GB M.2 SATA SSDs (RAID 1)
  • Software:
    • Nutanix AHV OS / Hypervisor
    • Microsoft Windows Server 2022 VMs
    • Microsoft SQL Server 2022 Enterprise Edition

Best Practices for SQL Server on ThinkAgile HX650 V3

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

  • Configure UEFI (BIOS) settings for Operating mode to Maximum performance.
  • Enable hyper-threading in the BIOS.
  • Set network cards and switches MTU to 9000
  • Avoid over-subscribing VM memory or processors beyond physical availability.
  • 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.

 

Figure 1. Lenovo ThinkAgile HX650 V3

 

Performance Testing Details and Results

HammerDB Configuration

HammerDB is an open-source load testing / benchmarking tool for databases available at: http:///www.hammerdb.com. It offers tools for testing performance on OLTP and Analytics workloads. The OLTP workload is based on TPC-C benchmark from http://www.tpc.org and the Analytics workload is based on TPC-H benchmark from tpc.org. HammerDB 4.7 was used. Below are details of the testing and results.

Table 1. TPC-C performance testing details and results
Database tested MS SQL Server 2022 Enterprise Edition
System HX650 V3 Certified Node - 4th Gen Intel Xeon SP
Hardware Configuration 3x ThinkAgile HX650 V3 in Nutanix Cluster, 2x Intel Xeon Platinum 8468 48C 2.1 Ghz processors, 1TB memory, 4x Intel P5620 NVMe drives
VM configuration 12 VMs, each with 28 CPU, 96GB RAM
Benchmarks simulated TPC-C
Database size: TPC-C 100GB 800 warehouse, on each VM
Virtual users 50 per VM
User delay 1 ms
TPC-C results Total from 12 VMs
Transactions Per Minute (TPM) 5.1 million TPM

Bill of Materials

Table 2. Bill of Materials
7D6NCTO3WW Server: ThinkAgile HX650 V3 Certified Node 1
BRP4 ThinkAgile HX650 V3 Base 1
B0W3 XClarity Pro 1
B15S Nutanix SW Stack on Nutanix AHV 1
BM84 ThinkAgile HX Remote Deployment 1
BPPU Intel Xeon Platinum 8468 48C 350W 2.1GHz Processor 2
BNF9 ThinkSystem 64GB TruDDR5 4800 MHz (2Rx4) 10x4 RDIMM 16
BH8B ThinkSystem 2U/4U 8 x 2.5" AnyBay Backplane 1
B0SW Nutanix Flash Node Config 1
BNEH ThinkSystem 2.5" U.2 P5620 3.2TB Mixed Use NVMe PCIe 4.0 x4 HS SSD 4
B8P9 ThinkSystem M.2 NVMe 2-Bay RAID Adapter 1
BTTY M.2 NVMe 1
BKSR ThinkSystem M.2 7450 PRO 960GB Read Intensive NVMe PCIe 4.0 x4 NHS SSD 2
BN2T ThinkSystem Broadcom 57414 10/25GbE SFP28 2-Port OCP Ethernet Adapter 1
AUKP ThinkSystem Broadcom 57416 10GBASE-T 2-Port PCIe Ethernet Adapter 1
BMUF ThinkSystem 1800W 230V Platinum Hot-Swap Gen2 Power Supply 2
B8LA ThinkSystem Toolless Slide Rail Kit v2 1
BLL6 ThinkSystem 2U V3 Performance Fan Module 6
BPKR TPM 2.0 1
BRPJ XCC Platinum 1

Accelerated by Intel

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.

Why Lenovo

Lenovo is a US$70 billion revenue Fortune Global 500 company serving customers in 180 markets around the world. Focused on a bold vision to deliver smarter technology for all, we are developing world-changing technologies that power (through devices and infrastructure) and empower (through solutions, services and software) millions of customers every day.

 

 

For More Information

To learn more about this Lenovo solution contact your Lenovo Business Partner or visit: https://www.lenovo.com/us/en/servers-storage/solutions/database/

References:

Lenovo ThinkAgile HX650 V3: https://lenovopress.lenovo.com/lp1668

Microsoft SQL Server 2022: https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Related product families

Product families related to this document are the following:

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®, 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.