Joint engineering with AMD for SQL Server 2022
Mon, 14 Nov 2022 13:49:15 -0000
|Read Time: 0 minutes
In preparation for the PASS Data Community Summit, Dell Technologies has been heads down in our engineering labs testing some of the new features of SQL Server 2022. In this blog we will highlight a number of use cases, leveraging AMD EPYC™ 7003 Series Processors with 3D V-Cache. 3D V-Cache processors utilize AMD’s ground-breaking 3D Chiplet architecture with up to 768MBs of L3 cache per socket while providing socket compatibility with existing AMD EPYC™ 7003 platforms. AMD EPYC is optimized for performance with up to 64 cores and 4TB of memory per CPU.
SQL Server 2022 is the most Azure-enabled release of SQL Server, with continued innovation across performance, security, and availability. SQL Server 2022 is part of the Microsoft Intelligent Data Platform, which unifies operational databases, analytics, and data governance. The reference architecture below shows how an Azure Arc-enabled Azure Stack HCI platform helps to consolidate virtualized SQL Server workloads and get up and running quickly on SQL Server 2022.
SQL Server 2022 on Azure Stack HCI
Dell Azure Stack HCI provides a flexible, highly available, and cost-effective platform to host Online Transaction Processing (OLTP) workloads such as SQL Server 2022. This document provides customers with best practice recommendations on how to deploy SQL Server 2022 on a Dell Integrated System (DIS) for Azure Stack HCI. These best practices consider both performance and high availability.
We also evaluated some performance results and design best practices for a cluster of AMD EPYC™ 7003 Series Processors with 3D V-Cache based DIS for Azure Stack HCI. This cost-effective solution offers strong performance results, a low data center footprint, and a highly flexible configuration in terms of compute and storage.
Only Azure Stack HCI from Dell Technologies leverages Dell OpenManage Integration for Windows Admin Center to orchestrate full stack lifecycle management, enabling complex tasks to be completed in a fraction of the time.
Read the full paper here.
If you are joining Dell Technologies and AMD at the PASS Data Community Summit (November 15th-18th, please join us for breakfast session and get all the details on this solution and ask the experts all of the questions you may have.
Attending the PASS Community Data Summit? Register for the breakfast here.
SQL Server 2022 Data Analytics with Dell PowerEdge and Dell ECS
Data virtualization has become popular among large enterprises because unstructured and semi-structured data is everywhere and using this data is challenging. SQL Server 2022 PolyBase makes data virtualization possible for data scientists to use T-SQL for analytic workloads by querying data directly from S3-compatible object storage without separately installing client connection software.
Data analytic workloads can be CPU intensive and selecting the optimal CPUs for the data analytic servers can be challenging, time consuming, and expensive. Because running T-SQL queries for data analytics require quick response time, as in the previous document, here we leveraged the new Milan-X AMD EPYC processor with 3D V-Cache technology. The AMD 3D V-Cache technology is the first implementation of the AMD 3D Chiplet Architecture. 3D V-Cache product offers three times the L3 cache compared to standard 3rd Gen EPYC processors and keeps memory intensive compute closer to the core speeding up performance for database and analytics workloads. AMD 3rd Gen EPYC™ Processors with 3D V-Cache helps customers optimize core usage, license costs and total cost-of-ownership.
ECS, a modern object storage platform designed for both traditional and next-generation workloads, provides organizations with an on-premises alternative to public cloud solutions. Dell Technologies has been named a Leader in the 2022 Gartner® Magic Quadrant™ for Distributed File Systems and Object Storage for the seventh year in a row – a Leader every year since the commencement of this report. According to the Gartner report, Dell Technologies has also once again received the highest overall position for its Ability to Execute in the Leaders quadrant of the report (based on Bellcore component reliability modeling across all AX-nodes).
As organizations, both large and small, seek to gain an edge with their intelligent data estate, access to all types of datasets must be made available. SQL Server 2022 and Dell ECS is the preferred technology for querying the Data Lake using a T-SQL surface area. This combination of products and tools yields modern opportunities to store and manage different types of data on-premises and at public cloud scale.
Read the full paper here.
SQL Server 2022 Backup & Restore with Dell ECS S3 Object Storage
In the same paper above, we leveraged the same environment to showcase the new SQL Server 2022 capabilities to run backup and restore of databases leveraging object storage with Dell ECS. One of the benefits of being able to leverage object storage is the ability to move larger read-only tables outside of the SQL database. This reduced the footprint of the database and decreases the time it takes to backup and restore.
In this document we provide the configuration steps for creating the required credential inside the SQL Server Instance for connection to ECS as well as show the correct syntax for backing up and restoring a database.
Take the next step…
Microsoft Azure Stack HCI | Dell USA
Dell Technologies Solutions for Microsoft Data Platform | Dell USA
Dell Technologies Solutions for Microsoft Azure Arc | Dell USA
Related Blog Posts
Microsoft SQL Server 2019 TPC-H Performance on Dell EMC PowerEdge R7515
Mon, 14 Jun 2021 14:44:40 -0000
|Read Time: 0 minutes
Dell EMC PowerEdge R7515 servers with AMD 75F3 processors deliver impressive performance and price/performance for SQL Server 2019.
While modernizing your data center there are two very important considerations every organization must make. The first is the price of new hardware and the other is how will it perform.
The latest powerful AMD 75F3 processors change the economics of the datacenter for the better. The Dell EMC PowerEdge R7515(2U) server, with the AMD processors, delivers the balanced I/O, memory, and computing capacity needed for large-scale analytical and business intelligence applications.
The latest generation PowerEdge R7515 servers mean organizations will not have to compromise neither on performance nor cost and instead focus on realizing their IT and digital business potentials. Independent tpc.org auditing has demonstrated that these servers are top rated in performance and price/performance for @ 1,000GB scale factor Microsoft SQL Server 2019 Enterprise database with Red Hat Enterprise Linux 8 operating system in a non-clustered environment.[1] Powered by 3rd generation AMD® EPYC™ processors, these servers are capable of handling demanding workloads and applications, such as data warehouses, ecommerce, databases. For more details about Dell servers please visit the Dell website.
Results (Source: Tpc.org as of May 03,2021)
The Dell PowerEdge R7515 server achieved a result of 9,79,335.3 QphH@1000GB and $269.23 $/kQphH@1000GB and with system availability as of Apr 29, 2021. Results were officially published on the TPC org website on May 3,2021. *[2]
Check out the TPC-H V3 Result Highlights for additional information on the benchmark configuration. The detailed official benchmark disclosure report at TPC Results Page.
AMD EPYC 75F3 Processor:
The AMD EPYC™ 7003 Series Processors are built with leading-edge Zen 3 core, and AMD Infinity Architecture. The AMD EPYC™ SoC offers a consistent set of features across 8 to 64 cores. Each 3rd Gen AMD EPYC processor consists of up to eight Core Complex Die CCD) and an I/O Die (IOD).
Benchmarking SQL Server 2019 with Dell EMC PowerEdge R7515 Server:
Microsoft SQL Server was configured on PowerEdge R7515 server with the following configuration. The PowerEdge R7515 server was equipped with single EPYC 75F3 3.3GHz, 32C/64T, 256M Cache (280W) DDR4-3200 and 1 TB of memory (up to 2 TB supported). Storage for this system was eight 1.6TB NVME Gen3 Mixed use Express Flash Drives plus four 480GB SSD SAS mixed use drives and three 480GB SATA read intensive SSD drives. The system ran Microsoft SQL Server 2019 Enterprise Edition and Red Hat Enterprise Linux 8. For technical information, see the R7515 rack server page for specifications, customizations, and details.
You can visit the FDR report for additional information on the benchmark configuration.
Performance:
The performance test consists of two runs:
A run consists of one execution of the Power test followed by one execution of the Throughput test.
RUN 1 is the first run following the load test. Run 2 is the run following Run 1. Below are the RUN1 and RUN2 results. For more detailed information please go through the FDR report.
Dell Technologies solutions for Microsoft SQL Server:
Dell Technologies solutions simplify the deployment, integration and management of Microsoft data platform environments and accelerate time-to-value for better service delivery and business innovation. With a broad infrastructure portfolio and a long-standing partnership with Microsoft, we provide the innovative solutions that reduce complexity and enable you to solve today’s challenges, no matter where you are in your transformation journey.
We have already released 10 TB TPC-H benchmarks on 4-S R940xa server. For More information about this the testing config and results please visit Dell EMC PowerEdge R940xa Full Disclosure Report.
In addition, Dell Technologies offers a portfolio of other rack servers, tower servers, and modular infrastructure that can be configured to accelerate most any business workload. For more details about Dell Technologies Solutions for Microsoft SQL please visit https://www.delltechnologies.com/sql
[1] *Based on TPC Benchmark H (TPC-H), May 2021, at 1,000 GB the Dell EMC PowerEdge R7515 server, priced at $263,658.00 USD, has a TPC-H (V3) Composite Query-per-Hour metric of 979,335.3 yielding a TPC-H Price/Performance of $269.23 USD / kQphH@1,000 GB with Microsoft SQL Server 2019 Enterprise Edition database and Red Hat Enterprise Linux 8 operating system, in a non-clustered environment. Actual results may vary. Full results on tpc.org.
[2] Based on TPC Benchmark H (TPC-H), May 2021, at 1,000 GB the Dell EMC PowerEdge R7515 server, priced at $263,658.00 USD, has a TPC-H (V3) Composite Query-per-Hour metric of 979,335.3 yielding a TPC-H Price/Performance of $269.23 USD / kQphH@1,000 GB with Microsoft SQL Server 2019 Enterprise Edition database and Red Hat Enterprise Linux 8 operating system, in a non-clustered environment. Actual results may vary. Full results on tpc.org.
AMD EPYC Zen3 Delivers 20% More SQL Server Performance
Mon, 03 May 2021 14:04:47 -0000
|Read Time: 0 minutes
It’s a common question: “How much database performance will we gain when upgrading to the newest server technology?” The person asking the question wants to justify the investment based on measured benefit. An engineering team here at Dell Technologies ran a load test comparing the prior generation of AMD EPYC processors to the new EPYC Zen3 processors. These test findings show a double-digit gain in performance for a typical write-heavy transactional workload using the new AMD EPYC Zen3 processors.
We used two Dell EMC PowerEdge R7525 servers with different generations of AMD EPYC processors. One server was configured with two 32-core AMD EPYC 75F3 processors running with a base clock speed of 2.95 GHz that can boost up to 4 GHz.
The table below compares the AMD EPYC Zen3 processors to the Zen2 processors used in the other server. We see that the new Zen3 processors have a higher boost clock speed and double the L3 cache. These new processors should accelerate database workloads by providing greater performance.
Component | PowerEdge R7525 with AMD EPYC Zen3 | PowerEdge R7525 with AMD EPYC Zen2 |
AMD EPYC CPU | 75F3 | 7542 |
Base clock speed | 2.95 GHz | 2.9 GHz |
Boost clock speed | 4.0 GHz | 3.4 GHz |
L1 cache | 96K per core | 96K per core |
L2 cache | 512K per core | 512K per core |
L3 cache | 256 MB shared | 128 MB shared |
Both generations of processors support 8 memory channels, with each memory channel supporting up to 2 DIMMS. We believe the faster boost clock speed combined with doubling the L3 cache for the new generation AMD EPYC Zen3 processors will drive greater database performance; however, there are many more features that we haven’t covered. This AMD webpage covers the EPYC 75F3 processors. For a deep technical dive into performance tuning database workloads we recommend this RDBMS tuning guide.
The table below shows a comparison summary of two PowerEdge servers used in the testing.
Component | PowerEdge Milan (Zen3) | PowerEdge ROME (Zen2) |
Processor | 2 x AMD EPYC 75F3 32 core processor | 2 x AMD EPYC 7542 32 core processor |
Memory | 2,048 GB 3.2 GHz | 2,048 GB 3.2 GHz |
Disk Storage | 8 x Dell Express Flash NVMe P4610 1.6 TB | 8 x Dell Express Flash NVMe P4610 1.6 TB |
Embedded NIC | 1 x Broadcom Gigabit Ethernet BCM5720 | 1 x Broadcom Gigabit Ethernet BCM5720 |
Integrated NIC | 1 x Broadcom Adv. Dual port 25 GB Ethernet | 1 x Broadcom Adv. Dual port 25 GB Ethernet |
Microsoft SQL Server Enterprise Edition 2019 was virtualized to reflect the most common configuration we see at customer sites. The performance differences between bare-metal and VMware virtualization are rarely a consideration for customers since AMD and VMware vSphere 7.0 CU2 are continuously optimizing performance. The paper, “Performance Optimizations in VMware vSphere 7.0 U2 CPU Scheduler for AMD EPYC Processors” shows that the VMware CPU scheduler archives up to 50% better performance than 7.0 U1. Virtualized data management systems have also enabled Database-as-a-Service offerings on-premise for many enterprises. The capability to quickly provision database copies can significantly benefit many IT priorities and programs.
The Dell Engineering team used VMware virtualization to create a SQL Server virtual machine template for this testing. That template allowed the team to quickly provision four copies of the exact same virtualized database across the two PowerEdge servers. We hosted two virtualized SQL Server databases on each PowerEdge server. The figure below shows the infrastructure configuration for this performance test.
The virtual machine configuration for the four SQL Servers databases and the memory allocations for SQL Server are detailed below.
Component | Virtual Machine Configuration and Memory of SQL Server |
vCPU | 28 |
Memory | 886 GB |
Disk Storage | 2.7 TB |
Memory for SQL Server | 758 GB |
Both databases used VMware vSphere’s Virtual Machine File System (VMFS) on Direct Attached Storage (DAS). In the table below shows the sizes of each storage volume used for the virtual machine. There are two data volumes (data 1 and data 2) to increase I/O bandwidth to the disk storage. By having two volumes, reads and writes are split between the volumes increasing storage performance.
Storage Group | Size (GB) |
Operating System | 100 |
Data 1 | 600 |
Data 2 | 600 |
TempDB and TempDB Log | 200 |
Lob | 200 |
Backup | 1,000 |
In summary, the entire SQL Server software stack consisted of:
- Microsoft SQL Server Enterprise Edition 2019 CU9
- Red Hat Enterprise Linux 8.3
- VMware vSphere ESXi 7.0 Update 2
To create an Online Transaction Processing (OLTP) workload on the two SQL Server databases, the team used HammerDB. HammerDB is a leading benchmark tool used with databases like Microsoft SQL Server, Oracle, and others. We used HammerDB to generate a TPC-C workload that simulates terminal operators executing transactions, which is characteristic of an OLTP workload. A typical OLTP workload sends thousands of small read and write requests per minute to that database that must get committed to storage. New Orders Per Minute (NOPM) indicates the number of orders that were fully processed in one minute and is a metric we can use to compare two database systems. Below are the HammerDB settings used for the TPC-C workload test.
Setting | Value |
Time Driver Script | Yes |
Total Transactions per user | 1,000,000 |
Minutes of Ramp Up Time | 10 |
Minutes of Test Duration | 20 |
Use All Warehouses | Yes |
Number of Virtual Users | 100 |
The new AMD EPYC Zen3 processors delivered a 20% increase in New Orders Per Minute performance over the prior generation processor! That is a substantial improvement across the two virtualized SQL Server databases running on the PowerEdge R7525 server. See the comparison chart below.
It’s very likely that the boost memory speed of 4.0 GHz and the larger L3 cache in the AMD EPYC Zen3 processors contributed significantly to the 20% performance gain. Every database is different, and results will vary. However, this performance test provides value in terms of understanding the potential gains in moving to AMDs new Zen3 processors. For enterprises considering migrating their databases to a new server platform, serious consideration should be given to using the PowerEdge R7525 servers with the new generation of AMD processors.