SQL Server on LINUX | Performance best practices and configuration guidelines

Below article from Microsoft provides best practices and recommendations to maximize performance for database applications that connect to SQL Server on Linux. These recommendations are specific to running on the Linux platform. All normal SQL Server recommendations, such as index design, still apply.

The following guidelines contain recommendations for configuring both SQL Server and the Linux operating system

SQL Server configuration

1. Use PROCESS AFFINITY for Node and/or CPUs

It is recommended to use ALTER SERVER CONFIGURATION to set PROCESS AFFINITY for all the NUMANODEs and/or CPUs you are using for SQL Server (which is typically for all NODEs and CPUs) on a Linux Operating System. Processor affinity helps maintain efficient Linux and SQL Scheduling behavior. Using the NUMANODE option is the simplest method. Note, you should use PROCESS AFFINITY even if you have only a single NUMA Node on your computer. See the ALTER SERVER CONFIGURATION documentation for more information on how to set PROCESS AFFINITY.

Refer below for more-
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql?view=sql-server-linux-2017

2. Configure multiple tempdb data files

Because a SQL Server on Linux installation does not offer an option to configure multiple tempdb files, we recommend that you consider creating multiple tempdb data files after installation. For more information, see the guidance in the article, Recommendations to reduce allocation contention in SQL Server tempdb database.

Refer below for more-
https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d

3. Set a memory limit with mssql-conf

Note – The above is an optional configuration setting. It is based on the requirements of your workload and configuration of your Linux Operating System.

In order to ensure there is enough free physical memory for the Linux Operating System, the SQL Server process uses only 80% of the physical RAM by default. For some systems which large amount of physical RAM, 20% might be a significant number. For example, on a system with 1 TB of RAM, the default setting would leave around 200 GB of RAM unused. In this situation, you might want to configure the memory limit to a higher value. See the documentation on the mssql-conf tool and the memory.memorylimitmb setting that controls the memory visible to SQL Server (in units of MB).

Refer-
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-linux-2017#memorylimit

When changing this setting, be careful not to set this value too high. If you do not leave enough memory, you could experience problems with the Linux Operating System and other Linux applications.

Linux OS Configuration

Consider using the following Linux Operating System configuration settings to experience the best performance for a SQL Server.

Kernel settings for high performance

The following table provides recommendations for CPU settings

Setting	Value	More information
CPU frequency governor	performance	See the cpupower command
ENERGY_PERF_BIAS	performance	See the x86_energy_perf_policy command
min_perf_pct	100	See your documentation on intel p-state
C-States	C1 only	See your Linux or system documentation on how to ensure C-States is set to C1 only

The following table provides recommendations for disk settings-

Setting	Value	More information
disk readahead	4096	See the blockdev command
sysctl settings	kernel.sched_min_granularity_ns = 10000000 kernel.sched_wakeup_granularity_ns = 15000000 vm.dirty_ratio = 40 vm.dirty_background_ratio = 10 vm.swappiness=10	See the sysctl command

Kernel setting auto numa balancing for multi-node NUMA systems

If you install SQL Server on a multi-node NUMA systems, the following kernel.numa_balancing kernel setting is enabled by default. To allow SQL Server to operate at maximum efficiency on a NUMA system, disable auto numa balancing on a multi-node NUMA system

bash
sysctl -w kernel.numa_balancing=0

Kernel settings for Virtual Address Space

The default setting of vm.max_map_count (which is 65536) may not be high enough for a SQL Server installation. Change this value (which is an upper limit) to 256K.

bash
sysctl -w vm.max_map_count=262144

Disable last accessed date/time on file systems for SQL Server data and log files

Use the noatime attribute with any file system that is used to store SQL Server data and log files. Refer to your Linux documentation on how to set this attribute.

Leave Transparent Huge Pages (THP) enabled

Most Linux installations should have this option on by default. We recommend for the most consistent performance experience to leave this configuration option enabled.

swapfile

Ensure you have a properly configured swapfile to avoid any out of memory issues. Consult your Linux documentation for how to create and properly size a swapfile.

Virtual Machines and Dynamic Memory

If you are running SQL Server on Linux in a virtual machine, ensure you select options to fix the amount of memory reserved for the virtual machine. Do not use features like Hyper-V Dynamic Memory.

You can read more from below.
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-performance-best-practices?view=sql-server-linux-2017
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-performance-get-started?view=sql-server-linux-2017

Read about SQL Server on Linux: High availability and security from below –
https://cloudblogs.microsoft.com/sqlserver/2016/11/30/sql-server-on-linux-high-availability-and-security/

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com