Home > Workload Solutions > SQL Server > Guides > Implementation Guide—SQL Server 2019 Best Practices on PowerStore > Red Hat Enterprise Linux: SQL Server Tuned Profile
The “tuned-adm” command line tool enables switching between different tuning profiles in Linux. In this best practice, we used the “tuned-adm” tool to switch to an optimized Linux profile for SQL Server.
Overview
The Linux operating system provides a system tuning tool called “tuned.” This tool is a profile-based tool that uses udev device manager. The “tuned-adm” tool enables the Linux administrator to switch between profiles for the operating system. Pre-defined profiles enable the application of Linux configuration settings in a downloadable package. In this best practice the TUNED-PROFILES-MSSQL was downloaded and installed in the Linux OS. This resulted in the following changes in the tuned SQL Server profile:
FORCE_LATENCY = 5
Certain configuration parameters that adjust the PM QoS CPU DA latency according to the CPU load. For example, the force latency parameter enables the user or in this case the SQL Server profile to force a specific value. For more information on this CPU configuration parameter and similar parameters, see the Red Hat Performance Tuning Guide.
VM.TRANSPARENT_HUGEPAGES set to ALWAYS
Transparent HugePage Support (THP) automates the promotion and demotion of memory pages. Promotion is the automation from small memory pages to larger memory page sizes and demotion is the automation from larger memory pages to smaller memory pages. SQL Server uses transparent HugePages to optimize performance thus, this parameter is set to ALWAYS.
VM.SWAPPINESS was set to 1
Swap space is used by the Linux kernel as virtual memory. The swappiness setting defines how aggressively the kernel will swap memory pages to disk. By changing VM.SWAPPINESS to 1, the kernel is instructed to swap as little as possible to disk and keep more memory pages in RAM.
VM.DIRTY_BACKGROUND_RATIO was set to 3
The DIRTY_BACKGROUND_RATIO is the percentage of memory that contains free pages and reclaimable pages, that can be filled with dirty pages before the pages are written to disk. Databases can aggressively use the kernel page cache which lowers the background ratio forcing the kernel to flush the dirty pages to disk more frequently.
VM.DIRTY_RATIO set to 80
The DIRTY_RATIO is the percentage of total system memory that contain dirty pages before the pages are written to disk. In setting dirty_ratio to 80, the kernel is instructed to use 80 percent of total system memory for dirty pages before having to flush the data to disk.
VM.DIRTY_EXPIRE_CENTISECS is set to 500
The DIRTY_EXPIRE_CENTISECS define in centisecs (one hundredth of a second) when data is old enough to be written to disk. By setting DIRTY_EXPIRE_CENTISECS to 500 a dirty page can stay in memory for 500 centisecs before it is written to disk.
VM.DIRTY_WRITEBACK_CENTISECS is set to 100
The DIRTY_WRITEBACK_CENTISECS parameter defines the frequency the kernel will check to write dirty pages to disk. In this case a value of 100 centisecs instructs the kernel to check every 100 centisecs to write dirty pages to disk.
VM.MAX_MAP_COUNT is set to 1600000
The MAX_MAP_COUNT defines the number of memory map areas a process may include. For example, databases use memory extensively which means increasing this value results in more memory map areas available for the database to use.
This best practice also included network and kernel settings updated by the MSSQL tuned profile. The following list describes the network and kernel settings that were updated by the MSSQL tuned profile:
NET.CORE.RMEM_DEFAULT = 262144
NET.CORE.RMEM_MAX = 4194304
NET.CORE.WMEM_DEFAULT = 262144
NET.CORE.WMEM_MAX = 1048576
KERNEL.NUMA_BALANCING = 0
The configuration changes made to the Linux operating system fine-tuned memory, network, and kernel configurations resulted in moderate performance improvements including more efficient use of system resources.
Implementation Steps
In this test case, we are going to create and enable Microsoft and Red Hat tuned-profiles-mssql profiles, which is recommended and offers finer Linux performance-related tunings for SQL Server workloads.
# yum install tuned-profiles-mssql -y
The tuned-profiles for MS SQL will be created under /usr/lib/tuned/mssql directory.
#
# A Tuned configuration for SQL Server on Linux
#
[main]
summary=Optimize for Microsoft SQL Server
include=throughput-performance
[cpu]
force_latency=5
[sysctl]
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.transparent_hugepages=always
# For multi-instance SQL deployments, use
# vm.transparent_hugepages=madvise
vm.max_map_count=1600000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.numa_balancing=0
Note: If you are using Linux distributions with kernel version newer than 4.18, comment out the below kernel scheduler options, otherwise, uncomment them if you are using distributions with kernel version older than 4.18.
# kernel.sched_latency_ns = 60000000
# kernel.sched_migration_cost_ns = 500000
# kernel.sched_min_granularity_ns = 15000000
# kernel.sched_wakeup_granularity_ns = 2000000
# chmod +x /usr/lib/tuned/mssql/tuned.conf
# tuned-adm profile mssql
# tuned-adm active
Additional Resources
Performance best practices and configuration guidelines for SQL Server on Linux