Linux 上 SQL Server 的效能最佳作法和設定方針Performance best practices and configuration guidelines for SQL Server on Linux

適用於: 是SQL Server (僅限 Linux) 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (Linux only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此文章提供最佳作法和建議,以最大化連線至 Linux 上的 SQL Server 之資料庫應用程式的效能。This article provides best practices and recommendations to maximize performance for database applications that connect to SQL Server on Linux. 這些建議僅適用於在 Linux 平台上執行。These recommendations are specific to running on the Linux platform. 所有一般 SQL Server 建議 (例如索引設計) 仍然適用。All normal SQL Server recommendations, such as index design, still apply.

下列方針包含設定 SQL Server 和 Linux 作業系統的建議。The following guidelines contain recommendations for configuring both SQL Server and the Linux operating system.

SQL Server 設定SQL Server configuration

建議您在安裝 Linux 上的 SQL Server 之後執行下列設定工作,以達到應用程式的最佳效能。It is recommended to perform the following configuration tasks after you install SQL Server on Linux to achieve best performance for your application.

最佳做法Best practices

  • 針對節點和/或 CPU 使用處理程序親和性Use PROCESS AFFINITY for Node and/or CPUs

    建議使用 ALTER SERVER CONFIGURATION 為 Linux 作業系統上用於 SQL Server (通常用於所有 NODE 和 CPU) 的所有 NUMANODE 和/或CPU 設定 PROCESS AFFINITYIt 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. 處理程序親和性有助於維護有效率的 Linux 和 SQL 排程行為。Processor affinity helps maintain efficient Linux and SQL Scheduling behavior. 使用 NUMANODE 選項是最簡單的方法。Using the NUMANODE option is the simplest method. 請注意,即使您的電腦上只有一個 NUMA 節點,您還是應該使用處理程序親和性Note, you should use PROCESS AFFINITY even if you have only a single NUMA Node on your computer. 如需如何設定處理程序親和性的詳細資訊,請參閱 ALTER SERVER CONFIGURATIONSee the ALTER SERVER CONFIGURATION documentation for more information on how to set PROCESS AFFINITY.

  • 設定多個 tempdb 資料檔案Configure multiple tempdb data files

    由於 Linux 上的 SQL Server 安裝未提供設定多個 tempdb 檔案的選項,因此建議您在安裝之後,考慮建立多個 tempdb 資料檔案。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. 如需詳細資訊,請參閱文章中的指導方針避免 SQL Server tempdb 資料庫中配置爭用的建議 (機器翻譯)。For more information, see the guidance in the article, Recommendations to reduce allocation contention in SQL Server tempdb database.

進階設定Advanced Configuration

下列建議是選擇性的組態設定,您可以選擇在安裝 Linux 上的 SQL Server 之後執行。The following recommendations are optional configuration settings that you may choose to perform after installation of SQL Server on Linux. 這些選擇是根據您的工作負載和 Linux 作業系統設定的需求而定。These choices are based on the requirements of your workload and configuration of your Linux Operating System.

  • 使用 mssql-conf 設定記憶體限制Set a memory limit with mssql-conf

    為了確保 Linux 作業系統有足夠的可用實體記憶體,SQL Server 處理序預設只會使用 80% 的實體 RAM。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. 對於大量實體 RAM 的某些系統,20% 可能是很大的數字。For some systems which large amount of physical RAM, 20% might be a significant number. 例如,在具有 1 TB RAM 的系統上,預設設定會保留大約 200 GB 的 RAM (未使用)。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. 請參閱有關 mssql-conf 工具的文件,以及控制 SQL Server 可見記憶體的 memory.memorylimitmb 設定 (以 MB 為單位)。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).

    變更此設定時,請小心不要將這個值設得太高。When changing this setting, be careful not to set this value too high. 如果您沒有保留足夠的記憶體,您可能會遇到 Linux 作業系統和其他 Linux 應用程式的問題。If you do not leave enough memory, you could experience problems with the Linux Operating System and other Linux applications.

Linux OS 設定Linux OS Configuration

請考慮使用下列 Linux 作業系統組態設定,以體驗 SQL Server 安裝的最佳效能。Consider using the following Linux Operating System configuration settings to experience the best performance for a SQL Server Installation.

高效能的核心設定Kernel settings for high performance

這些是建議的 Linux 作業系統設定,與 SQL Server 安裝的高效能和輸送量相關。These are the recommended Linux Operating System settings related to high performance and throughput for a SQL Server installation. 如需設定這些設定的程序,請參閱您的 Linux 作業系統文件。See your Linux Operating System documentation for the process to configure these settings.

注意

針對 Red Hat Enterprise Linux (RHEL) 使用者,輸送量-效能設定檔會自動設定這些設定 (除了 C-States 以外)。For Red Hat Enterprise Linux (RHEL) users, the throughput-performance profile will configure these settings automatically (except for C-States).

下表提供 CPU 設定的建議:The following table provides recommendations for CPU settings:

設定Setting ReplTest1Value 詳細資訊More information
CPU 頻率管理員CPU frequency governor 效能performance 請參閱 cpupower 命令See the cpupower command
ENERGY_PERF_BIASENERGY_PERF_BIAS 效能performance 請參閱 x86_energy_perf_policy 命令See the x86_energy_perf_policy command
min_perf_pctmin_perf_pct 100100 請參閱 intel p-state 相關文件See your documentation on intel p-state
C-StatesC-States 僅限 C1C1 only 請參閱您的 Linux 或系統文件,以了解如何確保 C-States 設定為 [僅限 C1]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 ReplTest1Value 詳細資訊More information
磁碟預先讀取disk readahead 40964096 請參閱 blockdev 命令See the blockdev command
sysctl 設定sysctl settings kernel.sched_min_granularity_ns = 10000000kernel.sched_min_granularity_ns = 10000000
kernel.sched_wakeup_granularity_ns = 15000000kernel.sched_wakeup_granularity_ns = 15000000
vm.dirty_ratio = 40vm.dirty_ratio = 40
vm.dirty_background_ratio = 10vm.dirty_background_ratio = 10
vm.swappiness = 10vm.swappiness = 10
請參閱 sysctl 命令See the sysctl command

多節點 NUMA 系統的核心設定自動 NUMA 平衡Kernel setting auto numa balancing for multi-node NUMA systems

如果您將 SQL Server 安裝在多節點的 NUMA 系統上,則預設會啟用下列 kernel.numa_balancing 核心設定。If you install SQL Server on a multi-node NUMA systems, the following kernel.numa_balancing kernel setting is enabled by default. 若要允許 SQL Server 在 NUMA 系統上以最高效率運作,請在多節點 NUMA 系統上停用自動 NUMA 平衡:To allow SQL Server to operate at maximum efficiency on a NUMA system, disable auto numa balancing on a multi-node NUMA system:

sysctl -w kernel.numa_balancing=0

虛擬位址空間的核心設定Kernel settings for Virtual Address Space

vm.max_map_count 的預設設定 (也就是 65536) 可能不足以安裝 SQL Server。The default setting of vm.max_map_count (which is 65536) may not be high enough for a SQL Server installation. 將此值 (這是一個上限) 變更為 256K。Change this value (which is an upper limit) to 256K.

sysctl -w vm.max_map_count=262144

針對 SQL Server 資料和記錄檔,在檔案系統上停用上次存取日期/時間Disable last accessed date/time on file systems for SQL Server data and log files

noatime 屬性與用來儲存 SQL Server 資料和記錄檔的任何檔案系統搭配使用。Use the noatime attribute with any file system that is used to store SQL Server data and log files. 請參閱您的 Linux 文件,以了解如何設定此屬性。Refer to your Linux documentation on how to set this attribute.

讓透明大頁 (THP) 保持啟用Leave Transparent Huge Pages (THP) enabled

大部分的 Linux 安裝預設都應該為開啟此選項。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. 請參閱您的 Linux 文件,以了解如何建立和適當地調整交換檔大小。Consult your Linux documentation for how to create and properly size a swapfile.

虛擬機器和動態記憶體Virtual Machines and Dynamic Memory

如果您是在虛擬機器中執行 Linux 上的 SQL Server,請務必選取選項來修正保留給虛擬機器的記憶體數量。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. 請勿使用 Hyper-V 動態記憶體這類功能。Do not use features like Hyper-V Dynamic Memory.

後續步驟Next steps

若要深入了解可改善效能的 SQL Server 功能,請參閱開始使用效能功能To learn more about SQL Server features that improve performance, see Get started with Performance features.

如需 Linux 上的 SQL Server 詳細資訊,請參閱 Linux 上的 SQL Server 概觀For more information about SQL Server on Linux, see Overview of SQL Server on Linux.