Use the following Linux operating system configuration settings to optimize the SQL server database performance:
File system recommendations
- SQL Server supports both EXT4 and XFS file systems to host the database, transaction logs, and additional files such as checkpoint files for in-memory OLTP in SQL Server. Microsoft recommends using XFS file system for hosting the SQL Server data and transaction log files.
- Dell PowerFlex CSI helps to implement the above recommendation by applying a different file system storage class. Both EXT4 and XFS storage class options are available to implement. By defining the correct storage class in the manifest file CSI creates the respective file system based on EXT4 or XFS persistent volume on the SQL server pod.
- Set open file limit to maximum of 60000 on all the worker nodes deployed with RHEL operating system.
Disk settings recommendations
- Modify the max_sector_kb to 1024 for all database volume.
For example, echo 1024 > /sys/block/sdc/queue/max_sectors_kb
- Modify the nr_requests to 1024 for all database volumes.
For example, echo 1024 > /sys/block/sdc/queue/max_sectors_kb/nr_requests
- Set the disk readahead settings to 4096 or more for high throughput storage.
- Microsoft recommends the following sysctl changes to optimize the disk throughput:
kernel.sched_min_granularity_ns = 15000000
kernel.sched_wakeup_granularity_ns = 2000000
vm.dirty_ratio = 80
vm.dirty_background_ratio = 3
vm.swappiness = 1
Kernel settings for high performance
Microsoft recommends tuning a few kernel parameters to boost the database performance as follows:
- Disable NUMA balancing to allow the SQL Server to operate at maximum efficiency on NUMA node.
- Enable transparent huge pages (THP) to get the most consistent performance from the database. This is the default setting with the latest Linux builds.
- Change the virtual address space settings for the SQL server deployment. Change the value to at least 262144 for optimal performance.
For more information, see Microsoft performance best practices.
Using OpenShift node tuning operator
This reference architecture uses the node tuning operator to push the recommended Linux settings to the worker nodes. For more information about the tuning operator, see Using the node tuning operator.
Perform the following high-level steps to create your own tuned CRs to be used with tuning operator:
- Access the default CR set on the cluster in the YAML format with the following command: -
$ oc get Tuned/default -o yaml -n openshift-cluster-node-tuning-operator
- Modify the YAML file with the Kernel and disk settings recommendations in the profile data section. This guide implements the new CR from the default CR and only modifies the OpenShift worker nodes tuned daemons. Control plane node profiles use the default CR.
For information about the custom CR used in this solution, see Appendix B OpenShift Tuned settings.
- Apply the newly modified CR with the following command and wait for OpenShift to rollout the changes on each worker node.
$ oc apply -f custom-cr.yaml
- OpenShift identifies the custom tuned with the kind object that is mentioned in the YAML file and implements the custom changes.
- Run the following command to identify the tuned pod running on the node:
$ oc get pod -n openshift-cluster-node-tuning-operator
- Run the sysctl command against the pod to verify the parameter changes.
$ oc exec $tuned_pod -n openshift-cluster-node-tuning- operator – sysctl vm.max_map_count
Note: The above recommendations need to be applied to the Red Hat Linux container image of the Microsoft SQL Server.