Microsoft recommends the following best practices to optimize the performance:
- Store the database into four or more datafiles and store it on the separate database volumes.
- Store the database transaction logs on different volumes.
- Move the tempdb files from its default location to its own PowerFlex volume to use the PowerFlex capacity and power.
- Default values of tempdb files are set to 8. Create additional tempdb files as per the number of cores assigned to the SQL pod.
- Set 85 percent of the SQL Server memory limit with mssql-conf tool.
- Ensure to set the resource limits in SQL server deployment YAML file to get the guaranteed QOS (Quality of Service) from the OpenShift cluster. For more information, see Configure Quality of Service for Pods | Kubernetes.
Apart from the above recommended practices, following best practices are recommended by HammerDB to run the OLTP benchmark.
- Configure max degree of parallelism to its default value. SQL uses all core efficiently.
- Configure max worker threads to 800.
- Keep the initial datafile size sufficiently large to ensure that the files are not continuously growing at the expense of performance.
- Auto growth settings should be configured correctly so that the file grows sufficiently without any performance impact.
For more information about the HammerDB best practices, see HammerDB documentation.