Top
Authors
Published
18 Jun 2024Form Number
LP1975PDF size
8 pages, 140 KBSubscribed to LP1975.
Thank you for your feedback.
Information growth and a solution
In today's environment, information is a valuable asset that requires appropriate tools for storing and managing essential business data. Companies are being overwhelmed by the growing influx of information in their data centers. Placing MX455 V3 servers at remote edge locations can help distribute the data and processing demands. Consolidating SQL Server and other workloads within virtual machines on Azure Stack HCI clusters can improve processing efficiency. The MX455 V3 is edge-optimized and also ideal for AI and Telco workloads.
Lenovo Solutions for Microsoft SQL Server on ThinkAgile MX455 V3 are optimized for Online Transaction Processing (OLTP) such as TPC-C workloads. This technical brief features Microsoft SQL Server 2022 Enterprise Edition running in virtual machines on compact, high-performance Lenovo 2U rack or shelf mounted systems. The server is configured with 4th Generation AMD 8004 Siena processors, TruDDR5 4800MHz memory and NVMe SSD drives. The new processors from AMD support up to 64 cores and 6x 96GB memory DIMMs.
The MX455 V3 server is a storage dense offering for its size. It supports up to 8 2.5" drives including 4 front and 4 internal bays plus an additional 2 internal M.2 drives for the OS. The MX455 V3 servers include Azure Stack HCI pre-loaded and ready to setup in a cluster configuration.
Azure Stack HCI
Microsoft Azure Stack HCI (Hyper-Converged Infrastructure) is a hybrid cloud solution that integrates on-premises infrastructure with Azure cloud services. It enables businesses to run virtualized workloads on industry-standard hardware, combining the benefits of cloud computing with the control and customization of on-premises solutions. Azure Stack HCI supports a range of applications, including virtual desktops, databases, and high-performance workloads, while providing features like software-defined storage and networking, enhanced security, and simplified management through integration with Azure.
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 ThinkAgile MX455 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
Lenovo ThinkAgile MX455 V3
Lenovo ThinkAgile MX455 V3 offerings are ideal for virtualizing your legacy SQL Server applications because of their low cost and high-performance capabilities. They are industry standard servers providing cost effective computing and fast high-density local storage at the edge. With it’s smaller foot print, the MX455 V3 is ideal for deployment in remote office and edge locations.
Each server configuration features the following main components:
- Server: Lenovo ThinkAgile MX455 V3
- Processor: 1x 4th Gen AMD EPYC (Siena) 8324P 32C 180W 2.65GHz
- Memory: 192GB of TRUDDR5 4800 MT/s memory
- Storage pool: 4x 7450 PRO 1.2TB NVMe SSDs
- OS Storage: 2x 7450 PRO 480GB M.2 NVMe SSDs (RAID 1)
- Software:
- Azure Stack HCI OS
- Microsoft Windows Server 2022 VMs
- Microsoft SQL Server 2022 Standard Edition
Best practices for SQL Server on ThinkAgile MX455 V3
For a high-performance SQL Server solution, implement the following best practices:
- Configure UEFI (BIOS) settings for Operating mode to Maximum performance.
- Enable Simultaneous Multithreading.
- Set networking MTU to 9000
- Avoid over-subscribing VM memory or processors beyond physical availability.
- Configure power profile in Windows Server VM 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 ThinkAgile MX455 V3
Performance Testing Details and Results
HammerDB and VMFleet
HammerDB is an open-source load testing / benchmarking tool for databases available at: http:///www.hammerdb.com. It offers tools for testing performance of OLTP and Analytics workloads. The OLTP workload is based on TPC-C benchmark from http://www.tpc.org. HammerDB version 4.7 was used.
VMFleet is a Microsoft tool that automates Diskspd tests on multiple VMs for validating HCI storage I/O. It reports total IOPs achieved.
Bill of Materials
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 ThinkAgile MX Edge servers: https://lenovopress.lenovo.com/ds0112
Microsoft SQL Server 2022: https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16
Azure Stack HCI: Link
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®
ThinkAgile®
ThinkEdge®
ThinkSystem®
The following terms are trademarks of other companies:
AMD and AMD EPYC™ are trademarks of Advanced Micro Devices, Inc.
Intel® is a trademark of Intel Corporation or its subsidiaries.
Microsoft®, Azure®, SQL Server®, Windows Server®, and Windows® are trademarks of Microsoft Corporation in the United States, other countries, or both.
TPC® and TPC-C® are trademarks of Transaction Processing Performance Council.
Other company, product, or service names may be trademarks or service marks of others.
Configure and Buy
Please select a locale
Full Change History
Course Detail
Employees Only Content
The content in this document with a is only visible to employees who are logged in. Logon using your Lenovo ITcode and password via Lenovo single-signon (SSO).
The author of the document has determined that this content is classified as Lenovo Internal and should not be normally be made available to people who are not employees or contractors. This includes partners, customers, and competitors. The reasons may vary and you should reach out to the authors of the document for clarification, if needed. Be cautious about sharing this content with others as it may contain sensitive information.
Any visitor to the Lenovo Press web site who is not logged on will not be able to see this employee-only content. This content is excluded from search engine indexes and will not appear in any search results.
For all users, including logged-in employees, this employee-only content does not appear in the PDF version of this document.
This functionality is cookie based. The web site will normally remember your login state between browser sessions, however, if you clear cookies at the end of a session or work in an Incognito/Private browser window, then you will need to log in each time.
If you have any questions about this feature of the Lenovo Press web, please email David Watts at [email protected].
