Authors
Published
20 Aug 2023Form Number
LP1740PDF size
9 pages, 117 KBData growth challenge and a solution
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.
Business database solutions with faster time-to-value
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
- Fast time to value with pretested and sized ThinkSystem SR665 V3 hardware configurations
- Simple evaluation, fast and easy deployment, and workload optimized performance
- Database sized solution with optimal compute, memory, storage and networking components
- Optimal TCO through high performance, rapid deployment and advanced hardware
Microsoft SQL Server 2022
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.
Lenovo ThinkSystem SR665 V3
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
Best practices for SQL Server on ThinkSystem SR665 V3
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
Performance Testing Details and Results
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.
Database tested | MS SQL Server 2022 Enterprise Edition |
Processors | 2x 32-core AMD EPYC 9334 |
Hardware Configuration | ThinkSystem SR665 V3, 2x AMD EPYC 9334, 1.5 TB memory, ThinkSystem PM1655 SSDs |
Benchmarks simulated | TPC-C and TPC-H |
Database size: TPC-C | 100GB 800 warehouses, distributed over 8 NVMe drives (6x DB, 2x Log) |
Database size: TPC-H | 1000GB scale, distributed over 8 NVMe drives (6x DB and tempDB, 2x Log) |
OLTP run time parameters: TPC-C | |
Virtual users | 150 |
User delay | 1 ms |
Analytics run time parameters: TPC-H |
|
Virtual users | 7 |
Scale | 1000GB |
OLTP results | |
Transactions Per Minute (TPM) | 4.47M |
Analytics results | |
Queries per Hour (QpH) | 1120 |
Bill of Materials
7D9ACTO1WW | Server: ThinkSystem SR665 V3 - 3yr Warranty | 1 |
BLKK | ThinkSystem V3 2U 24 x 2.5" Chassis | 1 |
BREC | ThinkSystem AMD EPYC 9334 32C 210W 2.7GHz Processor | 2 |
BQ29 | ThinkSystem SR665 V3 2U High Performance Heatsink | 2 |
BQ3D | ThinkSystem 64GB TruDDR5 4800MHz (2Rx4) 10x4 RDIMM-A | 24 |
2212 | Storage devices (custom RAID configuration) | 1 |
BMFT | ThinkSystem RAID 540-8i PCIe Gen4 12Gb Adapter | 1 |
BNW6 | ThinkSystem 2.5" PM1655 3.2TB Mixed Use SAS 24Gb HS SSD | 8 |
B8LU | ThinkSystem 2U 8 x 2.5" SAS/SATA Backplane | 1 |
BT7N | ThinkSystem Raid 5350-8i for M.2/7MM SATA Boot Enablement | 1 |
BM8X | ThinkSystem M.2 SATA/x4 NVMe 2-Bay Enablement Kit | 1 |
BQ1Y | ThinkSystem M.2 5400 PRO 480GB Read Intensive SATA 6Gb NHS SSD | 2 |
AUZW | ThinkSystem I350-T4 PCIe 1Gb 4-Port RJ45 Ethernet Adapter | 1 |
BPQV | ThinkSystem V3 2U x16/x16/E PCIe Gen5 Riser1 or 2 | 1 |
BLKM | ThinkSystem V3 2U x16/x16/E PCIe Gen4 Riser1 or 2 | 1 |
BNFH | ThinkSystem 1100W 230V/115V Platinum Hot-Swap Gen2 Power Supply v3 | 2 |
BLL6 | ThinkSystem 2U V3 Performance Fan Module | 6 |
B8LA | ThinkSystem Toolless Slide Rail Kit v2 | 1 |
BQQ2 | ThinkSystem 2U V3 EIA Latch Standard | 1 |
BQ2B | ThinkSystem SR665 V3 MB | 1 |
2302 | RAID Configuration | 1 |
BQ31 | ThinkSystem MS 2U common Airduct Filler | 2 |
B5WJ | ThinkSystem OCP3 Filler | 1 |
BQ2Z | ThinkSystem 2U MS main Airduct | 1 |
BPKD | ThinkSystem 2U MS 3FH Riser 1&2 Cage w/Label1 | 1 |
BLLD | ThinkSystem 2U MS 3FH Riser 1&2 Cage | 1 |
BQ35 | ThinkSystem SR665 V3 Absolut-RoW RoT Module6 | 1 |
Why Lenovo?
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
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®
ThinkSystem®
The following terms are trademarks of other companies:
Microsoft®, Azure®, Hyper-V®, SQL Server®, Windows Server®, and Windows® are trademarks of Microsoft Corporation in the United States, other countries, or both.
TPC, TPC Benchmark, TPC-C, and TPC-H are trademarks of Transaction Processing Performance Council.
Other company, product, or service names may be trademarks or service marks of others.