Data growth problem and a solution
The rapid growth of technology means the amount of available data and the ability to collect that data increased to a level unthinkable as little as five years ago. As the volume and velocity of data increased, however, extracting meaningful insight in a timely manner became more complex. Therefore, opportunities are being missed and effort is being wasted. To compete, businesses in the 21st century are demanding the tools to derive true value from their data.
Lenovo Solutions for Microsoft SQL Server on ThinkSystem SR650 V2 are optimized for both Online Transaction Processing (OLTP) and Data Warehouse (DW) or Decision Support Systems (DSS). This solution guide features Microsoft SQL Server 2022 running on the high-performance Lenovo dual-socket 2U rack mount enterprise server. The server is configured with 3rd Generation Intel® Xeon® Scalable processors, TruDDR4 3200MHz memory and Samsung NVMe among a variety of storage options, including support for the PCIe 4.0 standard devices for I/O. These new processors from Intel offer up to 40 cores and 16x 3200 MHz DDR4 DIMMs per socket.
The SR650 V2 server is a storage dense offering, with 28 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 12x NVMe SSDs, which frees up PCIe slots and lowers NVMe solution acquisition costs.
Enterprise database solutions with faster time-to-value
Lenovo SR650 V2 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 3rd 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 4 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 V2 hardware configurations
Microsoft SQL Server 2022
SQL Server 2022 is now GA and its database engine 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
- 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 ThinkSystem SR650 V2 offerings are ideal for modernizing your legacy SQL Server applications because of their low cost and high-performance capabilities. This solutions uses industry standard x86 servers to provide cost effective computing and fast high-density local storage.
Lenovo ThinkSystem SR650 V2 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, Remote Direct Memory Access (RDMA) networking are natively supported in Windows Server to enable the highest levels of performance.
Lenovo has measured over 5.4 million transactions per minute (TPM) with TPC-C workload.
This configuration features the following main components:
- Server: Lenovo ThinkSystem SR650 V2
- Processor: 2x 3rd Gen Intel Xeon Scalable, 8380 40 core
- Memory: 1TB of TRUDDR4 3200 MHz memory
- DB Storage: 8x Samsung PM983 1.92TB NVMe SSDs
- Log Storage: 2x Samsung PM983 1.92TB NVMe SSDs Raid1
- OS Storage: 2x 480GB M.2 SATA SSDs for the operating system (RAID 1)
- Microsoft Windows Server 2022
- Microsoft SQL Server 2022 Enterprise Edition
This high-performance database solution with Microsoft SQL Server 2022 Enterprise Edition features the latest Samsung NVMe SSDs. These SSDs help build a low latency solution for mission critical SQL Server applications.
Backup Compression and Off-Load
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 PCIe accelerator cards that support QAT hardware offloading.
The benefits of QAT include:
- Reduced backup capacity
- Minimal CPU impact
- Minimal workload impact
- Faster backups
- Faster restores
This document shows performance benefits of software mode. In software only mode, it still utilizes the Intel QAT algorithm to improve backup times. The table below shows Lenovo test results of software only mode QAT backups. We saw nearly 3x the performance over the previous compression method (MS_XPRESS) and minimal additional CPU impact.
|Backup Type||Time (sec)||CPU %||MB/sec||Backup Size|
|No Compression||120||1.3||4222||516 GB|
|MS_XPRESS Compression||601||12.5||839||189 GB|
|QAT SW Compression||247||13||2042||167 GB|
Table 1. Intel QuickAssist Technology (QAT) Backup Compression
Enable and Configure QAT
Enable QAT (required for both hardware and software)
sp_configure 'hardware offload enabled', 1
Enable QAT hardware mode (if your system has accelerator cards)
ALTER SERVER CONFIGURATION
SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT)
Enable QAT software mode
ALTER SERVER CONFIGURATION
SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT, MODE = SOFTWARE)
Verify QAT is enabled and the type
SELECT * FROM sys.dm_server_accelerator_status
Restart the SQL instance to apply changes
BACKUP DATABASE [TPC-H1000] TO DISK = 'D:\backups\MSSQL1.bak' WITH FORMAT, NO_COMPRESSION
BACKUP DATABASE [TPC-H1000] TO DISK = 'D:\backups\MS-XPRESS.bak' WITH FORMAT, COMPRESSION (ALGORITHM = MS_XPRESS)
BACKUP DATABASE [TPC-H1000] TO DISK = 'D:\backups\QAT-DEFLATE.bak' WITH FORMAT, COMPRESSION (ALGORITHM = QAT_DEFLATE)
Best practices for running SQL Server on ThinkSystem SR650 V2
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.
Performance Testing Details and Results
HammerDB Configuration and Parameters
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. OLTP workload is based on TPC-C benchmark from http://www.tpc.org and Analytics workload is based on TPC-H benchmark from tpc.org. Hammerdb was run on a separate load server. Below are details of the testing and results.
|Hardware Configuration||ThinkSystem SR650 V2, 2x Intel Xeon 8380 40 core processors, 2TB GB memory, Samsung PM983|
|Database tested||MS SQL Server 2022 Enterprise Edition|
|Benchmarks simulated||TPC-C and TPC-H|
|TPC-C database size||100 GB 800 warehouse, distributed over 8 NVMe drives|
|TPC-C run time parameters|
|User delay||1 ms|
|TPC-H database size||1000GB Scale Factor|
|TPC-H run time parameters|
|Query Time||7.92 minutes|
|Average query time per user||475 seconds|
Table 2. TPC-C and TPC-H performance testing details and results
Bill of Materials
|7Z73CTO1WW||Sql Config : ThinkSystem SR650 V2-3yr Warranty||1|
|BH8H||ThinkSystem 2U 2.5" Chassis with 8, 16 or 24 Bays||1|
|BFYE||Operating mode selection for: "Efficiency - Favoring Performance Mode"||1|
|BB3G||Intel Xeon Platinum 8380 40C 270W 2.3GHz Processor||2|
|B964||ThinkSystem 32GB TruDDR4 3200 MHz (2Rx4 1.2V) RDIMM||32|
|2212||Storage devices - Custom RAID Configuration||1|
|B8P6||ThinkSystem 48 port 12Gb Internal Expander||1|
|B34N||ThinkSystem U.2 PM983 1.92TB Entry NVMe PCIe 3.0 x4 Hot Swap SSD||10|
|B5XJ||ThinkSystem M.2 SATA/NVMe 2-Bay Enablement Kit||1|
|BKSR||ThinkSystem M.2 7450 PRO 960GB Read Intensive NVMe PCIe 4.0 x4 NHS SSD||2|
|BH8D||ThinkSystem 2U/4U 8x2.5" NVMe Backplane||2|
|BE4U||ThinkSystem Mellanox ConnectX-6 Lx 10/25GbE SFP28 2-Port PCIe Ethernet Adapter||2|
|BQ0W||ThinkSystem V2 1100W (230Vac/115Vac) Platinum Hot Swap Power Supply||1|
|6400||2.8m, 13A/100-250V, C13 to C14 Jumper Cord||1|
|BH8E||ThinkSystem 2U Performance Fan||6|
|B8LA||ThinkSystem Toolless Slide Rail Kit v2||1|
|BMJ7||ThinkSystem 2U EIA Latch Standard (Left) v2||1|
|B0MK||Enable TPM 2.0||1|
|B97L||ThinkSystem SR650 V2 MB||1|
|B0ML||Feature Enable TPM on MB||1|
|BHS7||UEFI Operating Modes Support||1|
|B97A||ThinkSystem SR650 V2 Service Label for LI||1|
|AWF9||ThinkSystem Response time Service Label LI||1|
|B97E||ThinkSystem SR650 V2 Model Number Label||1|
|B97C||ThinkSystem SR650 V2 Agency Label||1|
|B8K8||ThinkSystem 2U MS 24x2.5" NVMe HDD Type Label1||2|
|AUTQ||ThinkSystem small Lenovo Label for 24x2.5"/12x3.5"/10x2.5"||1|
|B8JY||ThinkSystem 1100W Pt Power Rating Label WW||1|
|BMPF||ThinkSystem SR665/SR650 V2 Power Cable from MB to Front 2.5" BP v2||2|
|BMJS||ThinkSystem 2U M.2 Cable v2||1|
|BMP2||ThinkSystem SR665/SR650 V2 Power Cable from MB to CFF / Exp v2||1|
|BA77||ThinkSystem V2 PSU Filler||1|
|B955||ThinkSystem 4R ICX CPU HS Clip||2|
|AVWK||ThinkSystem EIA Plate with Lenovo Logo||1|
|B986||ThinkSystem V2 2U Package||1|
|BHJN||2U MB PSU Airduct||1|
|BC4X||MS 2FH Riser Filler||1|
|AVEQ||ThinkSystem 8x1 2.5" HDD Filler||1|
|BMJ8||ThinkSystem 2U EIA Latch with FIO (right) v2||1|
|B8MP||ThinkSystem 2U MS Air Duct Filler(For 2U Gap)||2|
|B5WJ||ThinkSystem OCP3 Filler||1|
|B8MM||ThinkSystem 2U MS 3FH Riser Filler||2|
|B977||ThinkSystem SR650 V2 Performance Heatsink||2|
|B8KY||Thinksystem WW Lenovo LPK||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|
Table 3. Bill of Materials
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/
Lenovo ThinkSystem SR650 V2: https://lenovopress.lenovo.com/lp1392-thinksystem-sr650-v2-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:
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:
The following terms are trademarks of other companies:
Intel® and Xeon® are trademarks of Intel Corporation or its subsidiaries.
Azure®, Hyper-V®, Microsoft®, 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.