There is a Bigger Story for SQL Server on RHEL Users

In my last column (“What’s the Story About SQL Server on Linux?” on the DBTA website), I gave you a broad overview about some of the very cool things happening with SQL Server running on the Linux operating system (OS), pri­marily Red Hat Enterprise Linux (RHEL).

In that article, I talked a bit about overall adop­tion of SQL Server on Linux (once the domain of April Fool’s Day-joke blog posts), about high availability (HA) support and features, and gave a cursory overview of performance improvements to SQL Server on RHEL involving Forced Unit Access (FUA)-capable storage devices and Per­sistent Memory (PMEM) improvements. Now, let’s review in more detail these and other performance-improv­ing features.

RHEL Is Not Your Father’s Oldsmobile

Apologies to readers who were born in the 1990s and don’t get the reference above. But one of the surprising lessons I learned from Sanjay Rao, senior principal performance engineer at Red Hat, is that there are some significant foundational differences between the Linux and Windows OSs that improve performance. Linux fans are probably rolling their eyes because they have known this for years, but it was news to me since I’ve worked primarily on the Windows OS since it was a thing. For example, Linux handles non-uniform memory access (NUMA) nodes somewhat differently than the Windows OS, providing you a bit of a performance boost on servers with hundreds of CPU cores. As a consequence of learning lessons such as this, my confidence in SQL Server on Linux is growing daily. If your IT shop has a good track record with Linux, I say, “Deploy with confidence!”

Same Concepts, Different Terms

Microsoft and Red Hat have collaborated for more than 4 years on SQL Server and have come up with a variety of ways to better manage the server internals for higher performance. For example, the Windows OS has Large Pages and an option called Lock Pages in Memory. In Linux, you have the same capabilities using Transparent Huge Pages via trace flag 834, and even more memory management options by tuning dirty page ratios to pre­vent disk swapping. Read all the details at https://red.ht/2IxlUdK. 

Also More Tunable

I started my career working on mainframes, VAX/VMS mini­computers, and HPE UNIX minicomputers. Those systems were highly tunable. In fact, I was rather happy about how many fewer elements were tunable in the Windows OS when I switched to it in the early 1990s. We still see this very granular tunability in Linux, the natural descendent of UNIX. For an example of this tunability in terms of server memory, check out the discussion at https://red.ht/3ncUKrS. Note that the descriptions in the RHEL tuning guide relate to version 7 but also apply to version 8.

In my last article, I introduced the idea of hard­ware-based PMEM as an alternative to the cheaper but less speedy Non-Volatile Memory Express (NVMe). PMEM also enables SQL Server 2019 on Linux to save a lot of time and effort on IOPs (input/output operations per second) for data and log files stored on DAX-format­ted PMEM volumes. However, that might be too abstract a descrip­tion. What it really means is a 3.6x performance gain on analytical workloads, as shown in this HPE benchmark downloadable PDF (https://bit.ly/3kjJ5pf).   When reading this, skip ahead to page 13 for a great visualization of the before and after benchmarks. Keep in mind that this is an especially strong value proposition in SQL Server Standard Edition, particularly when using columnstore indexes. An example of PMEM is Intel Optane DC Persistent Memory DIMMs (dual in-line memory modules).

Config Files for the Win!

Linux systems have long used config files to define a variety of OS-level configuration systems, most of which can have a very big impact on performance. Linux has had a high-performance con­fig file called “tuned” that is widely available. However, Microsoft and Red Hat have teamed up to create an even better config file for Linux servers running SQL Server that is called “mssql-tuned.” The mssql-tuned config file is a superset of what you’d find in the regular “tuned” config file and defines settings for capabilities such as virtual machines, dynamic memory, swapfile support, vir­tual address space, NUMA settings, and more. Full details are at https://bit.ly/3eOUrAx. 

Learn More