Optimizing performance with Microsoft SQL Server 2019 on RHEL 8

By: learn2skills Date: 19/04/2020 Categories: azure

When choosing an operating system for running database workloads like Microsoft SQL Server, Red Hat Enterprise Linux (RHEL) offers several unique advantages. But why is this the case?

When it comes to picking a database for your mission-critical application, performance is a crucial decision factor. In fact, delivering low latency, and high-throughput application responses comes down to tuning your database to run in a consistent and reliable operating system environment—one that can provide a solid foundation for performance, and run on bare metal, virtual, private and public cloud environments.

Red Hat and Microsoft have been collaborating for more than four years to deliver interoperable high-performance data solutions for enterprise customers. Now, with SQL Server 2019 on RHEL 8, you can get improved performance for your most mission-critical transactional systems and data warehouses, along with integrated business intelligence and advanced analytics solutions for building intelligent applications. This can provide a solid foundation for a trusted data estate modernization strategy that can maximize your return on investment.

One example of how we’re working upstream with Microsoft is the changes made to the XFS file system to better optimize working with Forced-Unit-Access (FUA)-capable enterprise storage devices.

In earlier versions of RHEL (before RHEL 8), SQL Server relied on FUA I/O subsystem capabilities to provide durable data writes. This forced-flushing behavior impacted I/O caching, read-ahead, and other critical storage subsystem behaviors.

Starting with SQL Server 2019 on RHEL 8, you can turn on SQL Server traceflag 3979 so that forced flushing is no longer needed for the XFS file system, and thus the performance of writes is significantly improved. In fact, Microsoft’s testing has shown that for a write-intensive SQL Server workload, the I/O traffic can be reduced by close to 50%, and that time and resources saved can now be spent on other mission-critical processing tasks.

In addition to the XFS enhancement discussed above, SQL Server running on RHEL 8 benefits from a number of other performance enhancements including:

Updates to the mssql-tuned profile, which optimized tuning for decision support workloads. This is in addition to tuning done inside SQL Server to improve performance of TPCC, TPCH and TPCE workloads. For additional information about this profile and SQL Server performance guidance, please refer also to the online Microsoft SQL Server Performance best practices and configuration guidelines for SQL Server on Linux.
New TCP/IP stack enhancements that increase performance and provide Bottleneck Bandwidth and Round-trip time (BBR) congestion control.
Multi-queue scheduling which allows storage block devices to make the best use of the bandwidth available from modern NVME-enabled solid-state devices.
Persistent Memory (pMEM) support alongside Microsoft’s DAX-enablement in SQL Server 2019 for Linux, enables SQL Server to bypass the file system and accelerate access to data and log files stored on the DAX formatted pMEM volume. To configure persistent memory support refer to the settings presented here.

Testing SQL Server 2019 on RHEL 8

With the general availability of SQL Server 2019 for RHEL 8, our recent, unofficial, testing of Microsoft SQL Server 2019 at the Red Hat performance lab indicates that the work is paying performance dividends. In the world of relational databases, the TPC benchmarks are typically the gold standard when it comes to performance measurements.

We wanted to share with customers the performance gains you can get running RHEL 8 and how it compares with RHEL 7. For our experiment, we used the open source tool called HammerDB to drive up an OLTP workload.

These tests were run on an Intel Xeon CPU E5-2690 v2 @ 3.00GHz class system with 64 cores and 128 GB RAM. The storage used for the test was an Intel PCI – NVMe drive. The kernels used for testing are shown in the legend of the graph.

Our testing showed a throughput improvement of up to 19% on RHEL 8, due to changes to improve operating system performance, and the close engineering collaboration between Red Hat and Microsoft. Since the results were not audited, the transactions / minute numbers (which are in multiple millions of transactions / minute) are not published on the graph.

We repeated the test in a virtual machine environment, and noticed similar gains. These tests were run using Microsoft SQL Server 2019 in a RHEL 8 virtual machine running on the Red Hat Virtualization platform version 4.3 compared to a RHEL 7 virtual machine.

Microsoft SQL Server 2019 performance in VMs, RHEL 7 vs. RHEL 8

These tests were run on an Intel Xeon CPU E5-2690 v2 @ 3.00GHz class System with 64 cores and 128 GB mem. The storage used for the test was an Intel PCI – NVMe drive. The Kernels used in the Virtual Machine are in the legend of the graphs.

Testing SQL Server 2019 on RHEL 8 at Cisco and HPE

The collaboration between Microsoft and Red Hat includes a number of the major hardware vendors to provide a sound ecosystem that provides stability, scalability, and performance. Towards that end, SQL Server 2019 on RHEL 8 was put to the test at Cisco and HPE using the TPC-H benchmark on respectively — a 30TB dataset for Cisco, and a 3TB dataset for HPE, and the results were published. TPC-H measures the capability of a system to examine large volumes of data, process queries with a high degree of complexity, and return answers to critical business questions.

SQL Server 2019 on RHEL 8 using Cisco’s hardware consisting of a 4-socket Intel Xeon Platinum 8280M 2.70 GHz processors and 112 cores was able to deliver 1,278,277 TPC-H Composite Query-per-Hour performance at 0.94 $/QphH. More information can be found here.

Similarly, SQL Server 2019 on RHEL 8 using a HPE DL325 Gen10 Plus server, consisting of a single AMD EPYC 7742 processor socket, achieved for the first time with a single processor socket over 1 million QphH. More information can be found here.

Conclusion

Whether you’re running on bare metal, in a VM, in a container environment, or the cloud – Microsoft SQL Server on Red Hat Enterprise Linux together offers a scalable foundation that provides a consistent application experience for the growing operational and analytical use-cases.

This post showcases the power of SQL Server 2019 on RHEL 8 from a performance aspect, but there’s no better way to experience it than to try it out for yourself.

To learn more about SQL Server on RHEL visit the Microsoft SQL Server on Red Hat Enterprise Linux resource center.

Additional References

Post Views: 706