Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Linux Best Practices > Performance Best Practices > 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.
Category | Operating System |
Product | Red Hat Enterprise Linux 8.3 |
Type of best practice | Performance Optimization |
Day and value | Day 2, Moderate Recommendation |
Overview
The TUNED_ADM tool enables the Linux administrator to switch between profiles for the operating system. Predefined profiles enable the application of Linux configuration settings in a downloadable package. In this best practice, we downloaded and installed the TUNED-MSSQL in the Linux operating system. This resulted in the following changes in the tuned SQL Server profile:
Certain configuration parameters 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.
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.
Swap space is used by the Linux operating system as virtual memory. This works by the Linux kernel writing data from RAM to the swap space, which allows for greater memory use on the system. By changing VM.SWAPPINESS to 1 the kernel is instructed to more aggressively reclaim unmapped memory, rather than using swap space.
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.
The DIRTY_RATIO is the percentage of total system memory that contain dirty pages before the pages are written to disk. By setting DIRTY_PAGES to 80, the kernel is instructed to use 80 percent of the total system memory for dirty pages before flushing the data to disk.
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.
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.
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 available memory map areas for the database to use.
In addition to the VM memory settings, 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:
Note: To maintain concice, no descriptions for these updates are included in this best practice.
We performed additional tuning of the MSSQL configuration parameters to see if there was room for further optimization. The changes below yielded a slight performance improvement in the following metrics:
Recommendation
Using the TUNED_ADM tool to apply the TUNED-PROFILES-ORACLE package automates the application of many best practices in the Linux operating system. After applying the tuned oracle settings, we load tested the new configuration and compared it to the prior performance test. The load OLTP load test showed moderate performance improvements in the following metrics:
The TCP-C NOPM and TPM metrics produced a moderate performance increase, indicating the changes to the Linux operating system improved performance. PowerMax IOPS, overall CPU utilization, and SQL Server BRPS showed slight improvements.
Overall, the configuration changes made to the Linux operating system fine tuned memory, network, and kernel configurations. These changes resulted in more efficient usage of system resources, providing moderate performance improvements.
Implementation Steps
To update the tuned parameters and create a new file, navigate to /usr/lib/tuned and create a directory to store a new tuned.conf file. In our configuration, the parameters captured below showed performance improvements
Once the file is created to enable this profile, execute the following commands:
#chmod +x /usr/lib/tuned/dellemc-amd/tuned.conf#tuned-adm profile dellemc-amd
Verify that it’s enabled using the following command:
# tuned-adm active
Additional Resources
Performance best practices and configuration guidelines for SQL Server on Linux