Authors
Published
15 Dec 2023Form Number
LP1750PDF size
12 pages, 253 KBChange History
Changes in the August 1 update:
- Added solution information and configuration for SQL Server 2022 Standard Edition
Changes in the December 15 update:
- Added information and performance results from Lenovo ThinkSystem SR650 V3 servers leveraging 5th Generation Intel Xeon Scalable processors
Data growth problem and a solution
In today’s world information is a valuable asset that demands the right tools to collect and manage critical business data. As the volume and velocity of data increase, however, extracting meaningful insight in a timely manner becomes more complex. It’s important for businesses of all sizes to choose a database solution that matches the requirements of the company, resulting in the most efficient outcome.
Lenovo Solutions for Microsoft SQL Server on ThinkSystem SR650 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 Lenovo dual-socket 2U rack mount enterprise server. The server can support both 4th and 5th 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. The Intel 5th generation processors support up to 64 cores, 385 watts and 96GB 5600 speed memory (The performance test section provides a comparison between 4th and 5th generation Intel processors).
The SR650 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.
Enterprise database solutions with faster time-to-value
Lenovo SR650 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
- 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 SR650 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
SQL Server Enterprise Edition delivers comprehensive database capabilities with end-to-end business intelligence, enabling high service levels for mission-critical workloads. SQL Server Standard Edition includes basic database and business intelligence for smaller organizations that need less features, benefiting from the lower cost compared to SQL Server Enterprise Edition. A detailed comparison of SQL editions can be found here.
Lenovo ThinkSystem SR650 V3
Lenovo ThinkSystem SR650 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 SR650 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.
The configurations feature the following main components:
Database | Microsoft SQL Server 2022 Enterprise Edition | Microsoft SQL Server 2022 Standard Edition |
OS | Microsoft Windows Server 2022 | Microsoft Windows Server 2022 |
Server | Lenovo ThinkSystem SR650 V3 | Lenovo ThinkSystem SR650 V3 |
Processor | 2x 4th Gen Intel Xeon Scalable, 6448Y 2.1 GHz 32 core | 2x 4th Gen Intel Xeon Scalable, 8444H 2.9 GHz 16 core |
Memory | 1TB of TRUDDR5 4800 MT/s memory | 2TB of TRUDDR5 4800 MT/s memory |
DB Storage | 6x Intel P5620 3.2TB NVMe SSDs | 6x Intel P5620 1.6TB NVMe SSDs |
Log Storage | 2x Intel P5620 3.2TB NVMe SSDs Raid1 | 2x Intel P5620 1.6TB NVMe SSDs Raid1 |
OS Storage | 2x 480GB M.2 SATA SSDs for the operating system (RAID 1) | 2x 480GB M.2 SATA SSDs for the operating system (RAID 1) |
These high-performance database solutions with Microsoft SQL Server 2022 Enterprise and Standard Editions feature the latest Intel Optane NVMe SSDs. These SSDs help build a low latency solution for mission critical SQL Server applications.
Backup Compression and Off-Loading
SQL Server 2022 introduces backup performance improvements with a new compression algorithm and hardware offloading and acceleration with Intel QuickAssist Technology (QAT).
Improvements can be seen with either software only compression or by using Intel hardware that supports QAT offloading and acceleration. Intel offers on-chip QAT hardware offloading with the latest Intel Xeon Scalable processors. SQL Server 2022 Enterprise Edition supports hardware or software QAT while SQL Server 2022 Standard Edition only supports software QAT.
The benefits of hardware QAT include:
- Reduced backup capacity
- Minimal CPU impact
- Minimal workload impact
- Faster backups
- Faster restores
In software only mode, it still utilizes the Intel QAT algorithm to improve backup times.
The table below shows Lenovo test results of hardware mode QAT backups. We saw considerable performance improvement over the standard MS_XPRESS compression method and additional improvement with Intel Gen5 processors.
Backup and Restore Tests
The tests were run with the server under load, at 98% CPU usage, backing up a 1000 scale TPCH database. This is where the most benefit can be seen with QAT as it performs a hardware offload to improve backup performance. There is significant improvement over the standard SQL compression during heavy loads.
Compression Type | Time (sec) | MB/sec | Backup File Size | |
---|---|---|---|---|
MS_XPRESS | ||||
Backup | 2299 | 216 | 390 GB | |
Restore | 994 | 500 | ||
QAT HW Offloaded | ||||
Backup | ||||
- with 4th Gen 8480+ processors | 919 | 542 | 357 GB | |
- with 5th Gen 8592V processors | 810 | 594 | 357 GB | |
Restore | ||||
- with 4th Gen 8480+ processors | 447 | 1112 | ||
- with 5th Gen 8592V processors | 405 | 1203 |
Enable and Configure QAT
Enable QAT:
sp_configure 'show advanced options', 1
GO
RECONFIGURE with override
GO
sp_configure 'hardware offload enabled', 1
GO
RECONFIGURE with override
GO
Restart SQL to apply
Enable QAT hardware mode
ALTER SERVER CONFIGURATION
SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT)
Restart SQL to apply
Verify QAT status:
SELECT * FROM
sys.dm_server_accelerator_status;
GO
Restart the SQL instance to apply changes
Run Backups
No compression
BACKUP DATABASE [TPC-H1000] TO DISK = 'D:\backups\MSSQL1.bak' WITH FORMAT, NO_COMPRESSION
MS_XPRESS compression
BACKUP DATABASE [TPC-H1000] TO DISK = 'D:\backups\MS-XPRESS.bak' WITH FORMAT, COMPRESSION (ALGORITHM = MS_XPRESS)
GO
QAT compression
BACKUP DATABASE [TPC-H1000] TO DISK = 'D:\backups\QAT-DEFLATE.bak' WITH FORMAT, COMPRESSION (ALGORITHM = QAT_DEFLATE)
GO
Best practices for running SQL Server Enterprise Edition on ThinkSystem SR650 V3
For a high-performance SQL Server solution, implement the following best practices:
- Configure UEFI (Bios) settings to set Operating mode to Maximum performance.
- 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 ThinkSystem SR650 V3
Best practices for running SQL Server Standard Edition on ThinkSystem SR650 V3
For a high-performance SQL Server Standard Edition solution, implement the following best practices:
- Configure UEFI (BIOS) settings to set Operating mode to Maximum performance.
- Enable Hyper-threading in the BIOS
- In the Bios, set the core count per processor to 12 because SQL Standard Edition is limited to 48 processors. With 2 sockets, and Hyper-threading enabled this equals 48.
- 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 Details and Results
HammerDB Configuration and Intel Gen 3 - Gen 4 - Gen 5 Comparison
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 run on a separate load server. Below are details of the testing and results.
Database tested | MS SQL Server 2022 Enterprise Edition | MS SQL Server 2022 Enterprise Edition | MS SQL Server 2022 Enterprise Edition | MS SQL Server 2022 Standard Edition |
Processor Generation | SR650 V3 - 5th Gen Intel Xeon SP | SR650 V3 - 4th Gen Intel Xeon SP | SR650 V2 - 3rd Gen Intel Xeon SP | SR650 V3 - 4th Gen Intel Xeon SP |
Hardware Configuration | ThinkSystem SR650 V3, 2x Intel Xeon 8562Y+ processors, 1TB 5600MT/s memory, Intel P5620 NVMe drives | ThinkSystem SR650 V3, 2x Intel Xeon 6448Y 32 core processors, 1TB memory, Intel P5620 NVMe drives | ThinkSystem SR650 V2, 2x Intel Xeon 8380 processors, 2TB memory, Intel P5600 NVMe drives | ThinkSystem SR650 V3, 2x Intel Xeon 8444H 16 core 2.9 Ghz processors, 2TB memory, Intel P5620 NVMe drives |
Benchmarks simulated | TPC-C and TPC-H | TPC-C and TPC-H | TPC-C and TPC-H | TPC-C and TPC-H |
Database size: TPC-C | 100 GB, 800 warehouse, distributed over 8 NVMe drives (6 DB, 2 Log) | 100 GB, 800 warehouse, distributed over 8 NVMe drives (6 DB, 2 Log) | 100 GB, 800 warehouse, distributed over 8 NVMe drives (6 DB, 2 Log) | 100 GB, 800 warehouse, distributed over 8 NVMe drives (6 DB, 2 Log) |
Database size: TPC-H | 1000 Scale Factor | 1000 Scale Factor | 1000 Scale Factor | 1000 Scale Factor |
Run time parameters: TPC-C | ||||
Virtual users | 150 | 150 | 150 | 150 |
User delay | 1 ms | 1 ms | 1 ms | 1 ms |
Run time parameters: TPC-H | ||||
Virtual users | 7 | 7 | 7 | 7 |
Scale | 1000 | 1000 | 1000 | 1000 |
TPC-C results | ||||
TPM (million) | 6.64 | 5.63 | 4.49 | 3.67 |
TPC-H results | ||||
QpH | 2438 | 1739 | 1251 | 95 |
TPM = Transactions per minute; QpH = Queries per hour
Bill of Materials (Enterprise Edition solution)
7D76CTO1WW | Server: ThinkSystem SR650 V3 - 3yr Warranty | 1 |
BLKK | ThinkSystem V3 2U 24 x 2.5" Chassis | 1 |
BPQD | Intel Xeon Gold 6448Y 32C 225W 2.1GHz Processor | 2 |
BKTN | ThinkSystem 64GB TruDDR5 4800 MHz (4Rx4) 3DS RDIMM | 16 |
BNEH | ThinkSystem 2.5" U.2 P5620 3.2TB 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 |
BQ20 | ThinkSystem M.2 960GB 5400 Pro SATA 6Gbps Non-Hot Swap SSD | 2 |
BCD6 | ThinkSystem Intel E810-C-Q2 2 port Ethernet Adapter 10/25 GbE | 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 | 1 |
BETS | ThinkSystem V3 2U SFF C0 (RAID) to Front 8x2.5" BP1 | 1 |
BPE3 | ThinkSystem SR650 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 | SR650 V3 3Y STANDARD | 1 |
Bill of Materials (Standard Edition solution)
7D76CTO1WW | Server: ThinkSystem SR650 V3 - 3yr Warranty | 1 |
BLKK | ThinkSystem V3 2U 24 x 2.5" Chassis | 1 |
BPPH | Intel Xeon Platinum 8444H 16C 270W 2.9GHz Processor | 2 |
BNFC | ThinkSystem 128GB TruDDR5 4800 MHz (4Rx4) 3DS RDIMM | 16 |
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 | 1 |
BETS | ThinkSystem V3 2U SFF C0 (RAID) to Front 8x2.5" BP1 | 1 |
BPE3 | ThinkSystem SR650 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 | SR650 V3 3Y STANDARD | 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 ThinkSystem SR650 V3: https://lenovopress.lenovo.com/lp1601
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®
ThinkSystem®
XClarity®
The following terms are trademarks of other companies:
Intel®, Intel Optane™, 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, 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.