Lenovo ThinkSystem SR650 V3 Microsoft SQL Server Instruction Manual
- June 12, 2024
- Lenovo
Table of Contents
- Lenovo ThinkSystem SR650 V3 Microsoft SQL Server
- Product Information
- Product Usage Instructions
- Technical Brief for Microsoft SQL Server 2022 on Lenovo ThinkSystem SR650
- Enable and Configure QAT
- Performance Testing Details and Results
- Trademarks
- References
- Read User Manual Online (PDF format)
- Download This Manual (PDF format)
Lenovo ThinkSystem SR650 V3 Microsoft SQL Server
Product Information
The Lenovo ThinkSystem SR650 V3 is a storage dense server offering designed for modernizing legacy SQL Server applications. It features up to 40 2.5 drive bays in the front, middle, and rear of the server, as well as 5 different slot configurations at the rear. The server also includes onboard NVMe PCIe ports for direct connections to 16 NVMe SSDs, reducing PCIe slot usage and lowering NVMe solution acquisition costs.
The SR650 V3 is methodically tested and tuned to save time on configuration, setup, testing, and tuning. It offers several advantages, including 40% better performance for workloads running on 4th generation Intel Xeon Scalable processors compared to servers with previous generation processors. It also improves the performance of SQL Server solutions with higher core counts, memory bandwidth, and PCIe Gen 5 devices, allowing for improved density and support for more and larger databases per host.
Lenovo ThinkSystem SR650 V3 servers come with pretested and sized hardware configurations, simplifying evaluation and enabling fast and easy deployment. They are optimized for database workloads and offer optimal compute, memory, storage, and networking components. By utilizing these servers, users can reduce total cost of ownership (TCO) through better performance, rapid deployment, and advanced hardware.
SQL Server 2022 is the included database software. It comes with updates to existing features such as Intelligent Query Processing and offers improved management capabilities. Starting with SQL 2022, runtimes for R, Python, and Java are no longer installed with SQL Setup. Instead, users can install any desired custom runtime(s) and packages.
The Lenovo ThinkSystem SR650 V3 servers are compatible with Windows Server and support technologies like Hyper-V and Storage Spaces Direct for high performance. They also natively support NVMe storage and Remote Direct Memory Access (RDMA) networking in Windows Server to enable the highest levels of performance.
Product Usage Instructions
To use the Lenovo ThinkSystem SR650 V3 server with Microsoft SQL Server 2022, follow these steps:
- Ensure that the server is properly installed and connected to power and network.
- Install Windows Server on the server according to the provided instructions.
- Once Windows Server is installed, install Microsoft SQL Server 2022 using the provided setup instructions.
- During the SQL Server installation, choose the desired custom runtimes and packages for R, Python, and Java, as they are no longer installed by default.
- After the SQL Server installation is complete, configure the server for optimal performance by following the recommended hardware configurations provided by Lenovo.
- If desired, utilize Hyper-V and Storage Spaces Direct technologies in Windows Server to achieve high performance for bare metal or virtualized SQL Servers.
- Take advantage of the native support for NVMe storage and RDMA networking in Windows Server to further optimize performance.
By following these steps, you can effectively utilize the Lenovo ThinkSystem SR650 V3 server with Microsoft SQL Server 2022 for your database workloads, benefiting from its low cost, high-performance capabilities, and optimized hardware configurations.
Technical Brief for Microsoft SQL Server 2022 on Lenovo ThinkSystem SR650
V3
Solution Brief
Data growth problem and a solution
The rapid growth of technology means the amount of available data and the
ability to collect that data continues to increase exponentially. As the
volume and velocity of data increase, however, extracting meaningful insight
in a timely manner becomes more complex. This results in missed opportunities
and wasted effort for businesses of all sizes. To compete, companies in the
21st century are demanding the tools to derive true value from their data.
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 is configured with 4th Generation
Intel® Xeon® Scalable processors, TruDDR5 4800MHz 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 up to 60 cores
and 16x 4800 MHz DDR5 DIMMs per socket.
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
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 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
- 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 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.
This configuration features the following main components:
- Server: Lenovo ThinkSystem SR650 V3
- Processor: 2x 4th Gen Intel Xeon Scalable, 8480+ 64 core
- Memory: 4TB of TRUDDR5 4800 MHz memory
- DB Storage: 6x Intel P5620 1.6TB NVMe SSDs
- Log Storage: 2x Intel P5620 1.6TB NVMe SSDs Raid1
- OS Storage: 2x 480GB M.2 SATA SSDs for the operating system (RAID 1) Software:
- 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 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.
The benefits of 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.
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 | 919 | 542 | 357 GB |
Restore | 447 | 1112 |
Table 1. Comparison of MS_XPRESS and Intel QuickAssist Technology (QAT) Compression
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 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
Performance Testing Details and Results
HammerDB Configuration and Intel Gen 3 – Gen 4 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 was run on a separate load server.
Below are details of the testing and results.
Processor Generation| SR650 V2 – 3rd Gen Intel Xeon SP| SR650 V3 – 4th
Gen Intel Xeon SP
---|---|---
Hardware Configuration| ThinkSystem SR650 V2, 2x Intel Xeon 8380
processors, 2TB memory, Intel P5600 NVMe drives| ThinkSystem SR650 V3, 2x
Intel Xeon 8480+ processors, 4TB memory, Intel P5620 NVMe drives
| |
Database tested| MS SQL Server 2022 Enterprise Edition| MS SQL Server
2022 Enterprise Edition
Benchmarks simulated| 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)
Database size: TPC-H| 1000 Scale Factor| 1000 Scale Factor
| |
Run time parameters: TPC-C| |
Virtual users| 150| 150
User delay| 1 ms| 1 ms
Run time parameters: TPC-H| |
Virtual users| 7| 7
Scale| 1000| 1000
| |
TPC-C results| |
NOPM
(million)
| 1.95| 2.48
| |
TPC-H results| |
Query Time (minutes)| 7.2| 6.4
Table 2. TPC-C and TPC-H performance testing details and results
Bill of Materials
7D76CTO1WW | Server: ThinkSystem SR650 V3 – 3yr Warranty | 1 |
---|---|---|
BLKK | ThinkSystem V3 2U 24 x 2.5″ Chassis | 1 |
BNOM | Intel Xeon Platinum 8480+ 64C 350W 2.0GHz Processor | 2 |
BNFC | ThinkSystem 128GB TruDDR5 4800 MHz (4Rx4) 3DS RDIMM | 32 |
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/td> | 1 | |
BETS | ThinkSystem V3 2U SFF C0 (RAID) to Front 8×2.5″ BP1 | 1 |
BPE3 | ThinkSystem SR650 V3 MCIO8x to SL8x CBL, PCIe4, 8×2.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 |
**** Table 3. Bill of Materials
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:
- Microsoft Alliance
- Microsoft SQL Server
- ThinkSystem SR650 V3 Server
Notices
Lenovo may not offer the products, services, or features discussed in this
document in all countries. Consult your local Lenovo representative for
information on the products and services currently available in your area. Any
reference to a Lenovo product, program, or service is not intended to state or
imply that only that Lenovo product, program, or service may be used. Any
functionally equivalent product, program, or service that does not infringe
any Lenovo intellectual property right may be used instead. However, it is the
user’s responsibility to evaluate and verify the operation of any other
product, program, or service. Lenovo may have patents or pending patent
applications covering subject matter described in this document. The
furnishing of this document does not give you any license to these patents.
You can send license inquiries, in writing, to:
- Lenovo (United States), Inc.
- 8001 Development Drive
- Morrisville, NC 27560
- U.S.A.
- Attention: Lenovo Director of Licensing
LENOVO PROVIDES THIS PUBLICATION ”AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some
jurisdictions do not allow disclaimer of express or implied warranties in
certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors.
Changes are periodically made to the information herein; these changes will be
incorporated in new editions of the publication. Lenovo may make improvements
and/or changes in the product(s) and/or the program(s) described in this
publication at any time without notice.
The products described in this document are not intended for use in
implantation or other life support applications where malfunction may result
in injury or death to persons. The information contained in this document does
not affect or change Lenovo product specifications or warranties. Nothing in
this document shall operate as an express or implied license or indemnity
under the intellectual property rights of Lenovo or third parties. All
information contained in this document was obtained in specific environments
and is presented as an illustration. The result obtained in other operating
environments may vary. Lenovo may use or distribute any of the information you
supply in any way it believes appropriate without incurring any obligation to
you.
Any references in this publication to non-Lenovo Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those Web
sites. The materials at those Web sites are not part of the materials for this
Lenovo product, and use of those Web sites is at your own risk. Any
performance data contained herein was determined in a controlled environment.
Therefore, the result obtained in other operating environments may vary
significantly. Some measurements may have been made on development-level
systems and there is no guarantee that these measurements will be the same on
generally available systems. Furthermore, some measurements may have been
estimated through extrapolation. Actual results may vary. Users of this
document should verify the applicable data for their specific environment.
© Copyright Lenovo 2023. All rights reserved.
This document, LP1750, was created or updated on June 13, 2023.
Send us your comments in one of the following ways:
- Use the online Contact us review form found at: https://lenovopress.lenovo.com/LP1750
- Send your comments in an e-mail to: comments@lenovopress.com
This document is available online at https://lenovopress.lenovo.com/LP1750.
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.
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
References
- TPC-Homepage
- TPC-Homepage
- tempdb database - SQL Server | Microsoft Learn
- What's new in SQL Server 2022 - SQL Server | Microsoft Learn
- What's new in SQL Server 2022 - SQL Server | Microsoft Learn
- Lenovo ThinkSystem SR650 V3 Server Product Guide > Lenovo Press
- Lenovo ThinkSystem SR650 V3 Server Product Guide > Lenovo Press
- Optimizing Microsoft SQL Server 2022 on Lenovo ThinkSystem SR650 V3 > Lenovo Press
- ThinkSystem SR650 V3 Server > Lenovo Press
- Microsoft SQL Server > Lenovo Press
- Optimizing Microsoft SQL Server 2022 on Lenovo ThinkSystem SR650 V3 > Lenovo Press
- Copyright and Trademark Information | Lenovo US | Lenovo US
- Database Solutions on Lenovo Servers | Lenovo US
Read User Manual Online (PDF format)
Read User Manual Online (PDF format) >>