Advertisements
Home > Linux, Microsoft Azure, SQL Server 2017 > SQL Server on Linux – Best practices post installation

SQL Server on Linux – Best practices post installation


 

Setup SQL Server on Linux:

1. Spinning up a new Linux VM on Microsoft Azure

2. Install and Configure SQL Server 2017 on Linux Azure VM

3. Connect SQL Server on Linux with SSMS from a Windows machine
 

Best Practices:

Here are some of the best practices post installing SQL Server on Linux that can help you maximize database performance:

1. To maintain efficient Linux and SQL Scheduling behavior, it’s recommended to use the ALTER SERVER CONFIGURATION command to set PROCESS AFFINITY for all the NUMANODEs and/or CPUs. [Setting Process Affinity]

2. To reduce the risk of tempdb concurrency slowdowns in high performance environments, configure multiple tempdb files by adding additional tempdb files by using the ADD FILE command. [tempdb Contention]

3. Use mssql-conf to configure the memory limit and ensure there’s enough free physical memory for the Linux operating system.

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 1024
sudo systemctl restart mssql-server

4. On multi-node Non-Uniform Memory Access (NUMA) installations, auto NUMA balancing needs to be disabled to allow SQL Server to operate at maximum efficiency on a NUMA system.

sysctl -w kernel.numa_balancing=0

5. You can also change the kernel settings value for virtual address space to 256K, as the default value of 65K may be insufficient for a SQL Server installation.

sysctl -w vm.max_map_count=262144

6. Use the noatime attribute to disable last accessed timestamps with any file system that is used to store SQL Server data and log files.

7. For the most consistent performance experience, you must leave the Transparent Huge Pages (THP) option enabled.

8. Virtual machine (VM) features like Hyper-V Dynamic Memory shouldn’t be used with SQL Server installations. When using VMs, be sure to assign sufficient fixed-memory sizes.

9. Make sure you have a properly configured swapfile to avoid any out of memory issues.


Advertisements
  1. No comments yet.
  1. August 31, 2018 at 5:40 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: