Running Microsoft SQL Server 2022 on Lenovo ThinkSystem SR665 V3 Servers

Information is a crucial asset. Having the right tools to collect and manage critical business data is of paramount importance in a world where businesses of all sizes are flooded with ever-expanding volumes of information at ever-increasing speeds. Businesses must therefore select the database solution that most efficiently matches their requirements. SQL Server Enterprise Edition includes advanced database and business intelligence tools for larger organizations that need a full featured, highly available database solution.

Lenovo Solutions for Microsoft SQL Server 2022 on ThinkSystem SR665 V3 servers are optimized for both Online Transaction Processing (OLTP) and Data Warehouse (DW). This technical brief features Microsoft® SQL Server 2022 running on a high-performance Lenovo dual socket 2U rack mount enterprise server. This server is configured with two 32-core AMD EPYC™ 9334 processors, TruDDR5 4800MHz memory, and ThinkSystem 2.5" PM1653 drives (from among a variety of storage options), and includes support PCIe® Gen 5.0 I/O. 4th Gen AMD EPYC processors offer up to 128 “Zen 4” cores, high memory bandwidth, and excellent energy efficiency.

The SR665 V3 server is a storage-dense offering that comes with up to 40 2.5" drives in a variety of front- accessible bays (up to 24), mid bays (8), and rear-accessible bays (8). This server supports up to 32x NVMe drives without oversubscribing of PCIe lanes (1:1 connectivity) or 20x SATA drives using the onboard SATA controller with no additional adapter needed.

Lenovo ThinkSystem SR665 V3 servers are methodically tested and tuned with MS SQL Server 2022 to save you months of configuration, setup, testing, and tuning. These new servers powered by 4th Gen AMD EPYC processors deliver the following benefits compared to servers powered by prior 3rd Gen AMD EPYC processors:

• Increased maximum cores per processor
• Increased “Zen 4” per-core performance
• Support for advanced technologies, such as DDR5 memory, PCIe Gen 5.0, and CXL 1.1
• Improved MS SQL Server performance thanks to the higher core counts, improved per-core performance and advanced technologies, supporting improved host density and more/larger databases per host

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

Note: MS SQL 2022 no longer installs R, Python, and Java runtimes. Manually install any desired custom runtime(s) and packages.

Some of the performance enhancements in SQL Server 2022 include:

• All ColumnStore indexes benefit from enhanced segment elimination by data type
• Concurrent updates to global allocation map pages reduce page latch contention
• Improved buffer pool scan operations on large-memory systems via multiple CPU cores for parallel scans
• Clustered ColumnStore Indices now sort existing data in memory before the index builder compresses the data
• Improved TempDB scaling performance
• Enhanced Shrink database low-priority processing minimizes in-memory OLTP performance impacts

Here are some management improvements:

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

SQL Server Enterprise Edition delivers comprehensive database capabilities with end-to-end business intelligence, enabling high service levels for mission-critical workloads. A detailed comparison of SQL editions can be found here.

The low cost and high performance of Lenovo ThinkSystem SR665 V3 offerings make them ideal for modernizing your legacy MS SQL Server applications. These industry standard servers provide cost effective computing and fast high-density local storage.

Lenovo ThinkSystem SR665 V3 servers offer the performance necessary for bare metal or virtualized MS SQL Servers. The Hyper-V and Storage Spaces Direct technologies built into Microsoft Windows® Server further boost performance. Windows Server also includes native support for technologies such as NVMe storage and Remote Direct Memory Access (RDMA) networking to enable the high levels of performance.

The configurations feature the following main components:

• Database Software: Microsoft SQL Server 2022 Enterprise Edition
• Operating System: Microsoft Windows Server 2022 Standard Edition
• Server: Lenovo ThinkSystem SR665 V3
• Processor: 2x AMD EPYC 9334 32-core processors
• Memory: 1.5TB of TRUDDR5 4800 MHz memory
• DB Storage and tempdb: 6x ThinkSystem PM1655 SAS SSDs (RAID0)
• Log Storage: 2x ThinkSystem PM1655 SAS SSDs (RAID1)
• OS Storage: 2x ThinkSystem M.2 5400 480GB SSDs

The following best practices wil help you implement a high-perfomance SQL Server Enterprise deployment:

• Set the BIOS UEFI operating mode to Maximum performance
• Set the Windows Server power profile to High performance
• Use a 64KB NTFS cluster size when formatting the SQL server database and log drives
• Place the MS SQL server database and log files on separate physical drives
• Format the OS and SQL server binary drives with the standard 4KB NTFS cluster size
• TempDB is shared by many processes and users as a temporary working area. The default configuration is suitable for most workloads, but the installation process can help guide your configuration, as described in the Microsoft TempDB Database documentation
• If the server is dedicated to the MS SQL Server workload, then use either the default dynamic memory management model or follow Microsoft SQL documentation guidelines to manually configure memory options if you need more granular control

Figure 1. Lenovo ThinkSystem SR665 V3

HammerDB Configuration

HammerDB is an open-source database transactional and analytics load testing/benchmarking tool for databases. The OLTP workload is based on TPC® Benchmark C (TPC-C), and the Analytics workload is based on TPC Benchmark H (TPC-H). The HammerDB OLTP and Analytics workloads are open source workloads derived from the TPC-C Benchmark Standard and the TPC-H Benchmark Standard, respectively, and as such are not comparable to published TPC-C or TPC-H results, as the results do not comply with the TPC-C and TPC-H Benchmark Standards. The testing described below used HammerDB running on a separate server.

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 ThinkSystem SR665 V3: https://lenovopress.lenovo.com/lp1608-thinksystem-sr665-v3-server

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