System Level Performance Tuning for SQL Server 2022 on Linux

Introduction

Running SQL Server on Linux offers many advantages, including cost savings and robust management tools. To fully realize these benefits, certain Linux-specific configurations must be tuned. This post focuses on three specific areas: I/O scheduler, swapfiles, and swappiness. So let’s jump in and start tinkering!

Configuring the I/O Scheduler

The I/O scheduler is a method used by the Linux kernel to decide the order in which block I/O operations are submitted to storage volumes. The scheduler can affect the performance of applications, especially I/O-intensive ones like SQL Server. Different I/O schedulers are designed for different types of workloads.

For databases, minimizing I/O latency is a priority. The deadline scheduler can be beneficial because it prioritizes I/O requests to minimize wait times, while the noop scheduler is often preferred in SSD and SAN environments as it assumes that the underlying hardware handles I/O optimization.

How to Change the I/O Scheduler:

1. Identify available schedulers and the current scheduler:

   cat /sys/block/[device]/queue/scheduler

   Replace [device] with your actual device name, such as sda.

2. Change the scheduler to deadline for the device:

   echo deadline | sudo tee /sys/block/[device]/queue/scheduler

   Verify the change by rechecking the scheduler file as shown in step 1.

Swappiness

Swappiness is a Linux kernel setting that specifies how much the system favors swapping out memory, as opposed to dropping portions of the page cache. The swappiness parameter can be set anywhere from 0 to 100. A setting of 0 tells the kernel to avoid swapping as much as possible, whereas a value of 100 tells it to aggressively swap.

SQL Server is designed to work best when its data is in memory, as disk access is significantly slower. If the server starts swapping out SQL Server data to disk, performance will take a hit due to the increased latency when the data is needed again. For database servers, including SQL Server on Linux, a lower swappiness value is preferred. A common recommendation is a value of 10, which strikes a balance by allowing some swapping without it being overly aggressive. However Microsoft’s official recommendation is a swappiness value of 1 for SQL Server hosts.

How to Adjust Swappiness:

1. Check the current swappiness value:

   cat /proc/sys/vm/swappiness

2. Set the swappiness value to 1:

   echo 10 | sudo tee /proc/sys/vm/swappiness

3. To make the setting persistent, add it to /etc/sysctl.conf:

   vm.swappiness = 1

4. Apply the changes:

   sudo sysctl -p

Configuring a Swapfile

For SQL Server on Linux, configuring a swap file is essential for managing memory more efficiently and ensuring system stability. A swap file acts as a virtual memory extension, providing a fallback for the physical memory (RAM) when it’s under heavy load. It’s especially crucial during unexpected spikes in memory demand or when running large queries that exceed the available RAM.

To check if your system has swap configured, you can use the command:

swapon --show

This command lists all active swap areas. If no swap areas are listed, it indicates that swap is not currently configured on your system. Configuring swap ensures that SQL Server has the necessary memory resources to maintain performance and stability, even under heavy load conditions.

Word of Caution

When optimizing SQL Server on Linux by adjusting system-level settings like the I/O scheduler, swapfile, and swappiness, it’s always important to proceed with caution. These changes can significantly impact system behavior at the kernel level. Engage in discussions with a senior Linux administrator or a systems engineer before implementing such modifications in a production environment. Their expertise can help mitigate risks and ensure that the adjustments align with your overall system and application strategy.

Conclusion

By understanding and configuring these three Linux-specific settings, you can enhance SQL Server performance and ensure a more consistent and reliable database operation. Always remember to benchmark and monitor your system both before and after making changes to confirm improvements. We have just scratched the surface here on Linux optimizations for SQL Server. This is a topic we will likely be revisiting soon.

Further Reading: Performance best practices for SQL Server on Linux – SQL Server | Microsoft Learn