Linux 执行sql 参数,Linux 上的 SQL Server 的性能最佳做法 - SQL Server | Microsoft Docs

Linux 上的 SQL Server 的性能最佳做法和配置指南Performance best practices and configuration guidelines for SQL Server on Linux

01/19/2021

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux

本文提供了最佳做法和建议，以最大程度地提高连接到 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 操作系统 (OS) 的建议。The following guidelines contain recommendations for configuring both SQL Server and the Linux Operating System (OS).

Linux OS 配置Linux OS Configuration

请考虑使用以下 Linux OS 配置设置，以体验 SQL Server 安装的最佳性能。Consider using the following Linux OS configuration settings to experience the best performance for a SQL Server Installation.

存储配置建议Storage configuration recommendation

使用具有适当 IOPS、吞吐量和冗余的存储子系统Use storage subsystem with appropriate IOPS, throughput, and redundancy

托管数据、事务日志和其他关联文件(如内存中 OLTP 的检查点文件)的存储子系统应能够正常管理平均和峰值工作负载。The storage subsystem hosting data, transaction logs, and other associated files (such as checkpoint files for in-memory OLTP) should be capable of managing both average and peak workload gracefully. 通常情况下，在本地环境中，存储供应商支持在多个磁盘之间进行条带化的适当硬件 RAID 配置，以确保适当的 IOPS、吞吐量和冗余。Normally, in on-premise environments, the storage vendor support appropriate hardware RAID configuration with striping across multiple disks to ensure appropriate IOPS, throughput, and redundancy. 不过，在不同存储供应商和具有不同体系结构的不同存储产品之间，这可能有所不同。Though, this can differ across different storage vendors and different storage offerings with varying architectures.

对于在 Azure 虚拟机上部署的 Linux 上的 SQL Server，请考虑使用软件 RAID 来确保满足相应的 IOPS 和吞吐量要求。For SQL Server on Linux deployed on Azure Virtual Machines, consider using software RAID to ensure appropriate IOPS and throughput requirements are achieved. 在 Azure 虚拟机上配置 SQL Server 时，请参阅以下文章，了解类似的存储注意事项：SQL Server VM 的存储配置Refer to following article when configuring SQL Server on Azure virtual machines for similar storage considerations: Storage configuration for SQL Server VMs

以下示例介绍如何在 Azure 虚拟机上的 Linux 中创建软件 raid。The following is an example of how to create software raid in Linux on Azure Virtual Machines. 下面提供了一个示例，但你应该根据数据、事务日志和 tempdb IO 要求，使用适当数量的数据磁盘，为卷提供所需的吞吐量和 IOPS。An example is provided below, but you should use the appropriate number of data disks for the required throughput and IOPS for volumes based on the data, transaction log, and tempdb IO requirements. 在此示例中，已向 Azure 虚拟机附加 8 个数据磁盘；4 个用于托管数据文件，2 个用于事务日志，2 个用于 tempdb 工作负载。In this example, eight data disks were attached to the Azure Virtual Machine; 4 to host data files, 2 for transaction logs, and 2 for tempdb workload.

# To locate the devices (for example /dev/sdc) for RAID creation, use the lsblk command

# For Data volume, using 4 devices, in RAID 5 configuration with 8KB stripes

mdadm --create --verbose /dev/md0 --level=raid5 --chunk=8K --raid-devices=4 /dev/sdc /dev/sdd /dev/sde /dev/sdf

# For Log volume, using 2 devices in RAID 10 configuration with 64KB stripes

mdadm --create --verbose /dev/md1 --level=raid10 --chunk=64K --raid-devices=2 /dev/sdg /dev/sdh

# For tempdb volume, using 2 devices in RAID 0 configuration with 64KB stripes

mdadm --create --verbose /dev/md2 --level=raid0 --chunk=64K --raid-devices=2 /dev/sdi /dev/sdj

磁盘分区和配置建议Disk partitioning and configuration recommendations

对于 SQL Server，建议使用 RAID 配置。For SQL Server, it is recommended to use RAID configurations. 部署的文件系统条带单元 (sunit) 和条带宽度应与 RAID 几何匹配。The deployed filesystem stripe unit (sunit) and stripe width should match the RAID geometry. 下面是一个针对日志卷的基于 XFS 文件系统的示例。Here is an XFS filesystem-based example for a log volume.

# Creating a log volume, using 6 devices, in RAID 10 configuration with 64KB stripes

mdadm --create --verbose /dev/md3 --level=raid10 --chunk=64K --raid-devices=6 /dev/sda /dev/sdb /dev/sdc /dev/sdd /dev/sde /dev/sdf

mkfs.xfs /dev/sda1 -f -L log

meta-data=/dev/sda1 isize=512 agcount=32, agsize=18287648 blks

= sectsz=4096 attr=2, projid32bit=1

= crc=1 finobt=1, sparse=1, rmapbt=0

= reflink=1

data = bsize=4096 blocks=585204384, imaxpct=5

= sunit=16 swidth=48 blks

naming =version 2 bsize=4096 ascii-ci=0, ftype=1

log =internal log bsize=4096 blocks=285744, version=2

= sectsz=4096 sunit=1 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

日志阵列是具有 64k 条带、6 个驱动器的 RAID-10。The log array is a 6-drive RAID-10 with a 64k stripe. 正如你所见：As you can see:

“sunit=16 blks”，16*4096 blk size= 64k，与条带大小匹配。The "sunit=16 blks", 16*4096 blk size= 64k, matches the stripe size.

“swidth = 48 blks”，swidth/sunit = 3，这是阵列中的数据驱动器数量，不包括奇偶校验驱动器。The "swidth = 48 blks", swidth/sunit = 3, which is the number of data drives in the array, excluding parity drives.

文件系统配置建议File System Configuration recommendation

SQL Server 支持使用 EXT4 和 XFS 文件系统承载数据库、事务日志和其他文件，例如 SQL Server 中的内存中 OLTP 的检查点文件。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 建议使用 XFS 文件系统来托管 SQL Server 数据和事务日志文件。Microsoft recommends using XFS file system for hosting the SQL Server data and transaction log files.

# Formatting the volume with XFS filesystem

mkfs.xfs /dev/md0 -f -L datavolume

mkfs.xfs /dev/md1 -f -L logvolume

mkfs.xfs /dev/md2 -f -L tempdb

备注

创建 XFS 卷并设置其格式时，可以将 XFS 文件系统配置为不区分大小写。It's possible to configure the XFS file system to be case insensitive when creating and formatting the XFS volume. 这不是 Linux 生态系统中经常使用的配置，但可出于兼容性原因而使用。It is not the frequently used configuration in Linux ecosystem but can be used for compatibility reasons.

示例：mkfs.xfs /dev/md0 -f -n version=ci -L datavolumeExample: mkfs.xfs /dev/md0 -f -n version=ci -L datavolume

在此示例中，使用参数 -n version=ci 将 XFS 文件系统配置为不区分大小写。In the example, parameters -n version=ci are used to configure the XFS filesystem to be case insensitive.

PMEM 文件系统建议Persistent Memory filesystem recommendation

对于 PMEM 设备上的文件系统配置，基础文件系统的块分配应为 2 MB。For the filesystem configuration on Persistent Memory devices, the block allocation for the underlying filesystem should be 2 MB. 有关本主题的详细信息，请参阅技术注意事项一文。For more information on this topic, review the article Technical considerations.

在文件系统上禁用 SQL Server 数据和日志文件的上次访问日期/时间Disable last accessed date/time on file systems for SQL Server data and log files

为确保在重启后自动重新装载附加到系统的驱动器，必须将其添加到 /etc/fstab 文件。To ensure that the drive(s) attached to the system are remounted automatically after a reboot, they must be added to the /etc/fstab file. 此外，强烈建议在 /etc/fstab 中使用 UUID(全局唯一标识符)来引用驱动器，而不是只使用设备名称(例如 /dev/sdc1)。It's also highly recommended that the UUID (Universally Unique Identifier) is used in /etc/fstab to refer to the drive rather than just the device name (such as /dev/sdc1).

对用于存储 SQL Server 数据和日志文件的任何文件系统，强烈建议使用 noatime 属性。Use of noatime attribute with any file system that is used to store SQL Server data and log files is highly recommended. 有关如何设置此属性的说明，请参阅 Linux 文档。Refer to your Linux documentation on how to set this attribute. 以下示例介绍如何为 Azure 虚拟机中装载的卷启用 noatime 选项。An example of how to enable noatime option for a volume mounted in Azure Virtual Machine is below.

/etc/fstab 中的装入点条目The mount point entry in /etc/fstab

UUID="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" /data1 xfs,rw,attr2,noatime 0 0

在上面的示例中，UUID 表示可使用 blkid 命令找到的设备。In the example above, UUID represents the device that you can find using the blkid command.

SQL Server 和强制单元访问 (FUA) I/O 子系统功能SQL Server and Forced Unit Access (FUA) I/O subsystem capability

某些版本的受支持 Linux 分发版支持 FUA I/O 子系统功能，以提供数据持久性。There are certain versions of supported Linux distributions that provide support for FUA I/O subsystem capability to provide data durability. SQL Server 使用 FUA 功能为 SQL Server 工作负载提供高效且可靠的 I/O。SQL Server uses FUA capability to provide highly efficient and reliable I/O for SQL server workload. 有关 Linux 分发版的 FUA 支持的额外信息及其对 SQL Server 的影响，请阅读以下博客：Linux 上的 SQL Server：强制单元访问 (FUA) 内部结构For additional information on FUA support by Linux distribution and its impact for SQL Server, read following blog: SQL Server On Linux: Forced Unit Access (FUA) Internals

SUSE Linux Enterprise Server 12 SP5 和 Red Hat Enterprise Linux 8.0 以上的版本支持 I/O 子系统中的 FUA 功能。SUSE Linux Enterprise Server 12 SP5 and Red Hat Enterprise Linux 8.0 onwards support FUA capability in the I/O subsystem. 如果使用 SQL Server 2017 CU6 及更高版本或 SQL Server 2019，则应使用以下配置，以便通过 SQL Server 使用 FUA 进行高性能、高效的 I/O 实现。If you're using SQL Server 2017 CU6 and above or SQL Server 2019, you should use following configuration for high performing and efficient I/O implementation with FUA by SQL Server.

如果满足以下条件，请使用下面列出的推荐配置。Use the recommended configuration listed below if the following conditions are met.

使用 SQL Server 2017 CU6 或更高版本，或 SQL Server 2019Using SQL Server 2017 CU6 or newer, or SQL Server 2019

使用支持 FUA 功能的 Linux 分发和版本(Red Hat Enterprise Linux 8.0 或更高版本，或 SUSE Linux Enterprise Server 12 SP5)Using a Linux distribution and version that supports FUA capability (Red Hat Enterprise Linux 8.0 or higher, or SUSE Linux Enterprise Server 12 SP5)

在支持 FUA 功能并已进行相应配置的存储子系统和/或硬件上On storage subsystem and/or hardware that supports and is configured for FUA capability

推荐配置：Recommended configuration:

启用跟踪标志 3979 作为启动参数Enable trace flag 3979 as a startup parameter

使用 mssql-conf 配置 control.writethrough = 1 和 control.alternatewritethrough = 0Use mssql-conf to configure control.writethrough = 1 and control.alternatewritethrough = 0

对于不符合上述条件的几乎所有其他配置，建议的配置如下所示：For almost all other configuration that doesn't meet the previous conditions, the recommended configuration is as follows:

启用跟踪标志 3982 作为启动参数(这是 Linux 生态系统中 SQL Server 的默认值)，同时确保未启用跟踪标志 3979 作为启动参数Enable trace flag 3982 as a startup parameter (which is default for SQL Server in the Linux ecosystem), while ensuring trace flag 3979 isn't enabled as a startup parameter

使用 mssql-conf 配置 control.writethrough = 1 和 control.alternatewritethrough = 1Use mssql-conf to configure control.writethrough = 1 and control.alternatewritethrough = 1

高性能内核和 CPU 设置Kernel and CPU settings for high performance

下一部分介绍是与 SQL Server 安装的高性能和吞吐量相关的推荐 Linux OS 设置。The following section describes the recommended Linux OS settings related to high performance and throughput for a SQL Server installation. 请参阅 Linux OS 文档，以了解如何配置这些设置。See your Linux OS documentation for the process to configure these settings. 根据说明，使用 Tuned 帮助配置下述的许多 CPU 和内核配置。Using Tuned as described helps configure many CPUs and kernel configurations described below.

使用 Tuned 来配置内核设置Using Tuned to configure Kernel settings

对于 Red Hat Enterprise Linux (RHEL) 用户，优化的吞吐量-性能配置文件将自动配置某些内核和 CPU 设置(C 状态除外)。For Red Hat Enterprise Linux (RHEL) users, the Tuned throughput-performance profile configures some kernel and CPU settings automatically (except for C-States). 自 RHEL 8.0 起，与 Red Hat 共同开发了名为 _ mssql* 的 *Tuned _ 配置文件，它可为 SQL Server 工作负载提供更精细的 Linux 性能相关优化。Starting with RHEL 8.0, a Tuned _ profile named _ mssql was codeveloped with Red Hat and offers finer Linux performance-related tunings for SQL Server workloads. 此配置文件包含 RHEL 吞吐量-性能配置文件，我们在下面提供了它的定义，供你查看其他 Linux 发行版和不带此配置文件的 RHEL 版本。This profile includes the RHEL throughput-performance profile and we present its definitions below for your review with other Linux distros and RHEL releases without this profile.

对于 SUSE Linux Enterprise Server 12 SP5、Ubuntu 18.04 和 Red Hat Enterprise Linux 7.x，可以手动安装 *Tuned _ 包。For SUSE Linux Enterprise Server 12 SP5, Ubuntu 18.04, and Red Hat Enterprise Linux 7.x, the *Tuned _ package can be installed manually. 它可用于创建和配置 _ mssql* 配置文件，如下所述。It can be used to create and configure the _ mssql* profile as described below.

使用优化的 mssql 配置文件的建议 Linux 设置Proposed Linux settings using a Tuned mssql profile

#

# A Tuned configuration for SQL Server on Linux

#

[main]

summary=Optimize for Microsoft SQL Server

include=throughput-performance

[cpu]

force_latency=5

[sysctl]

vm.swappiness = 1

vm.dirty_background_ratio = 3

vm.dirty_ratio = 80

vm.dirty_expire_centisecs = 500

vm.dirty_writeback_centisecs = 100

vm.transparent_hugepages=always

# For multi-instance SQL deployments, use

# vm.transparent_hugepages=madvise

vm.max_map_count=1600000

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

kernel.numa_balancing=0

kernel.sched_latency_ns = 60000000

kernel.sched_migration_cost_ns = 500000

kernel.sched_min_granularity_ns = 15000000

kernel.sched_wakeup_granularity_ns = 2000000

若要启用此优化的配置文件，请将这些定义保存在 /usr/lib/tuned/mssql 文件夹下的 tuned.conf 文件中，并使用以下命令启用配置文件：To enable this Tuned profile, save these definitions in a tuned.conf file under a /usr/lib/tuned/mssql folder, and enable the profile using the following commands:

chmod +x /usr/lib/tuned/mssql/tuned.conf

tuned-adm profile mssql

通过以下命令验证是否已启用它：Verify it's enabled with the following command:

tuned-adm active

或or

tuned-adm list

CPU 设置建议CPU settings recommendation

下表提供了 CPU 设置的建议：The following table provides recommendations for CPU settings:

设置Setting

值Value

详细信息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 状态的文档See your documentation on intel p-state

C 状态C-States

仅限 C1C1 only

请参阅 Linux 或系统文档，了解如何确保将 C 状态设置为仅限 C1See your Linux or system documentation on how to ensure C-States is set to C1 only

如上文所述，使用 Tuned _ 将自动配置 CPU 频率调控器、ENERGY_PERF_BIAS 和 min_perf_pct 设置，因为使用吞吐量-性能配置文件作为 _ mssql 配置文件的基础。Using Tuned _ as described earlier automatically configures CPU frequency governor, ENERGY_PERF_BIAS, and min_perf_pct settings appropriately due to the throughput-performance profile being used as base for the _ mssql profile. 必须根据 Linux 或系统分销商提供的文档手动配置 C 状态参数。C-States parameter must be configured manually according to the documentation provided by Linux or the system distributor.

磁盘设置建议Disk settings recommendations

下表提供了磁盘设置的建议：The following table provides recommendations for disk settings:

设置Setting

值Value

详细信息More information

disk readaheaddisk readahead

40964096

请参阅 blockdev 命令See the blockdev command

sysctl 设置sysctl settings

kernel.sched_min_granularity_ns = 15000000kernel.sched_min_granularity_ns = 15000000

kernel.sched_wakeup_granularity_ns = 2000000kernel.sched_wakeup_granularity_ns = 2000000

vm.dirty_ratio = 80vm.dirty_ratio = 80

vm.dirty_background_ratio = 3vm.dirty_background_ratio = 3

vm.swappiness = 1vm.swappiness = 1

请参阅 sysctl 命令See the sysctl command

描述：Description:

vm.swappiness：与文件系统缓存相比，此参数控制用于换出运行时进程内存的相对权重。vm.swappiness: This parameter controls relative weight given to swapping out runtime process memory as compared to filesystem cache. 此参数的默认值为 60，表示交换运行时进程内存页与删除文件系统缓存页的比率为 60:140。The default value for this parameter is 60, which indicates swapping runtime process memory pages as compared to removing filesystem cache pages at ratio of 60:140. 将值设置为 1 表示首选将运行时进程内存保留在物理内存中，代价是牺牲文件系统缓存。Setting the value 1 indicates strong preference for keeping runtime process memory in physical memory at expense of filesystem cache. 由于 SQL Server 使用缓冲池作为数据页面缓存，并且强烈倾向于绕过文件系统缓存直接写入物理硬件，从而实现可靠的恢复，因此，积极的交换配置对于实现高性能和专用 SQL Server 非常有利。Since SQL Server uses buffer pool as a data page cache and strongly prefers to write through to physical hardware bypassing filesystem cache for reliable recovery, aggressive swappiness configuration can be beneficial for high performing and dedicated SQL Server.

vm.dirty_* ：不会缓存 SQL Server 文件写入访问，这样做满足其数据完整性要求。vm.dirty_*: SQL Server file write accesses are uncached, satisfying its data integrity requirements. 通过分配足够大的缓存并限制刷新，这些参数可以实现 Linux 缓存写入的有效异步写入性能，并降低其存储 IO 影响。These parameters allow efficient asynchronous write performance and lower the storage IO impact of Linux caching writes by allowing large enough caching while throttling flushing.

kernel.sched_* ：这些参数值代表着最新建议，用于在 Linux 内核中调整完全公平计划 (CFS) 算法，以便在线程的进程内抢占和恢复方面提高网络和存储 IO 调用的吞吐量。kernel.sched_*: These parameter values represent the current recommendation for tweaking the Completely Fair Scheduling (CFS) algorithm in the Linux Kernel, to improve throughput of network and storage IO calls with respect to inter-process preemption and resumption of threads.

使用 mssql Tuned _ 配置文件来配置 _ vm.swappiness vm.dirty_* _ 和 _ kernel.sched_* 设置。Using the mssql Tuned_ profile configures the * vm.swappiness**, **vm.dirty*_ and _ kernel.sched_** settings. 使用 blockdev 命令的磁盘 readahead 配置以单个设备为基础，必须手动执行。The disk readahead configuration using blockdev command is per device and must be performed manually.

多节点 NUMA 系统的内核设置自动 numa 平衡Kernel setting auto numa balancing for multi-node NUMA systems

如果在多节点 NUMA 系统上安装 SQL Server，则默认会启用以下 kernel.numa_balancing 内核设置。If you install SQL Server on a multi-node NUMA system, 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

使用 mssql Tuned _ 配置文件来配置 _ kernel.numa_balancing 选项。Using the mssql **Tuned_ profile configures the _* kernel.numa_balancing* option.

虚拟地址空间的内核设置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. 出于此原因，请在 SQL Server 部署中，将 vm.max_map_count 值更改为至少 262144，并参阅使用优化的 mssql 配置文件的建议 Linux 设置部分，了解如何进一步优化这些内核参数。For this reason, change the vm.max_map_count value to at least 262144 for a SQL Server deployment, and refer to the Proposed Linux settings using a Tuned mssql profile section for further tunings of these kernel parameters. vm.max_map_count 的最大值为 2147483647。The max value for vm.max_map_count is 2147483647.

sysctl -w vm.max_map_count=1600000

使用 mssql Tuned _ 配置文件来配置 _ vm.max_map_count 选项。Using the mssql **Tuned_ profile configures the _* vm.max_map_count* option.

启用透明大页 (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. 但是，如果在具有多个实例的 SQL Server 部署中发生大量内存分页活动，或者在服务器上与其他内存需求较高的应用程序一起执行 SQL Server 的情况下，建议在执行以下命令后测试应用程序的性能：However, if there is high memory paging activity in SQL Server deployments with multiple instances, for example, or SQL Server execution with other memory demanding applications on the server, we suggest testing your applications performance after executing the following command:

echo madvise > /sys/kernel/mm/transparent_hugepage/enabled

或使用以下行修改 mssql Tuned 配置文件：Or modify the mssql Tuned profile with the line:

vm.transparent_hugepages=madvise

并在修改后使 mssql 配置文件处于活动状态：And make the mssql profile is active after the modification:

tuned-adm off

tuned-adm profile mssql

使用 mssql Tuned _ 配置文件来配置 _ transparent_hugepage 选项。Using the mssql **Tuned_ profile configures the _* transparent_hugepage* option.

网络设置建议Network setting recommendations

与存储和 CPU 建议一样，下面也列出了特定于网络的建议，以供参考。Like there are storage and CPU recommendations, there are Network specific recommendations as well listed below for reference. 并非下面提到的所有设置都可在不同的 NIC 上使用。Not all settings mentioned below are available across different NICs. 有关每个选项的指导，请参阅文档并咨询 NIC 供应商。Refer and consult with NIC vendors for guidance for each of these options. 在开发环境中对它们进行测试和配置，然后将其应用于生产环境。Test and configure this on development environments before applying them on production environments. 下面所述的选项使用示例说明，所用的命令特定于 NIC 类型和供应商。The options mentioned below are explained with examples, and the commands used are specific to NIC type and vendor.

配置网络端口缓冲区大小：在下面的示例中，NIC 的名称为“eth0”，它是基于 Intel 的 NIC。Configuring network port buffer size: In the example below, the NIC is named 'eth0', which is an Intel-based NIC. 对于基于 Intel 的 NIC，推荐的缓冲区大小为 4KB (4096)。For Intel based NIC, the recommended buffer size is 4KB (4096). 验证预先设置的最大值，然后使用下面所示的示例命令进行配置：Verify the pre-set maximums and then configure it using the sample commands shown below:

#To check the pre-set maximums please run the command, example NIC name used here is:"eth0"

ethtool -g eth0

#command to set both the rx(recieve) and tx (transmit) buffer size to 4 KB.

ethtool -G eth0 rx 4096 tx 4096

#command to check the value is properly configured is:

ethtool -g eth0

启用 jumbo 帧：启用 jumbo 帧之前，验证客户端和 SQL Server 之间的所有网络交换机、路由器以及网络数据包路径所需的任何其他内容是否都支持 Jumbo 帧。Enable jumbo frames: Before enabling jumbo frames, verify that all the network switch(es), routers, and anything else essential in the network packet path between the clients and the SQL server support Jumbo Frames. 只有这样，启用 jumbo 帧才可以提高性能。Only then, enabling jumbo frames can improve performance. 启用 jumbo 帧后，连接到 SQL Server 并使用 sp_configure 将网络数据包大小更改为 8060，如下所示：After jumbo frames are enabled, connect to SQL Server and change the network packet size to 8060 using sp_configure as shown below:

#command to set jumbo frame to 9014 for a Intel NIC named eth0 is

ifconfig eth0 mtu 9014

#verify the setting using the command:

ip addr | grep 9014

sp_configure 'network packet size' , '8060'

go

reconfigure with override

go

默认情况下，我们建议设置端口以进行自适应 RX/TX IRQ 合并，这意味着将会调整中断传递，以在数据包速率较低时降低延迟，在数据包速率较高时提高吞吐量。By default, we recommend setting the port for adaptive RX/TX IRQ coalescing, meaning interrupt delivery will be adjusted to improve latency when packet rate is low and improve throughput when packet rate is high. 请注意，此设置可能不适用于所有不同的网络基础结构，因此请查看现有的网络基础结构并确认此设置受支持。Note that this setting might not be available across all the different network infrastructure, so review the existing network infrastructure and confirm that this is supported. 下面的示例针对名为“eth0”的 NIC，它是基于 Intel 的 NIC：The example below is for the NIC named 'eth0', which is an intel-based NIC:

#command to set the port for adaptive RX/TX IRQ coalescing

echtool -C eth0 adaptive-rx on

echtool -C eth0 adaptive-tx on

#confirm the setting using the command:

ethtool -c eth0

备注

为实现高性能环境(如用于基准测试的环境)的可预测行为，请禁用自适应 RX/TX IRQ 合并，然后专门设置 RX/TX 中断合并。For a predictable behavior for high-performance environments, like environments for benchmarking, disable the adaptive RX/TX IRQ coalescing and then set specifically the RX/TX interrupt coalescing. 请参阅示例命令以禁用 RX/TX IRQ 合并，然后专门设置以下值：See the example commands to disable the RX/TX IRQ coalescing and then specifically set the values:

#commands to disable adaptive RX/TX IRQ coalescing

echtool -C eth0 adaptive-rx off

echtool -C eth0 adaptive-tx off

#confirm the setting using the command:

ethtool -c eth0

#Let us set the rx-usecs parameter which specify how many microseconds after at least 1 packet is received before generating an interrupt, and the [irq] parameters are the corresponding delays in updating the #status when the interrupt is disabled. For Intel bases NICs below are good values to start with:

ethtool -C eth0 rx-usecs 100 tx-frames-irq 512

#confirm the setting using the command:

ethtool -c eth0

我们还建议启用 RSS(接收方缩放)，并在默认情况下，将 RSS 队列的 rx 和 tx 端组合在一起。We also recommend RSS (Receive-Side Scaling) enabled and by default, combining the rx and tx side of RSS queues. 在某些特定情况下，与 Microsoft 支持部门合作时，禁用 RSS 还会提高性能。There have been specific scenarios where when working with Microsoft Support, disabling RSS has improved the performance as well. 在生产环境中应用此设置之前，请先在测试环境中进行测试。Test this setting in test environments before applying it on production environments. 下面显示的示例命令适用于 Intel NIC。The example command shown below is for Intel NICs.

#command to get pre-set maximums

ethtool -l eth0

#note the pre-set "Combined" maximum value. let's consider for this example, it is 8.

#command to combine the queues with the value reported in the pre-set "Combined" maximum value:

ethtool -L eth0 combined 8

#you can verify the setting using the command below

ethtool -l eth0

使用 NIC 端口 IRQ 关联。Working with NIC port IRQ affinity. 若要通过调整 IRQ 关联来实现预期的性能，请考虑几个重要参数，如 Linux 对服务器拓扑的处理、NIC 驱动程序堆栈、默认设置和 irqbalance 设置。To achieve expected performance by tweaking the IRQ affinity, consider few important parameters like Linux handling of the server topology, NIC driver stack, default settings, and irqbalance setting. NIC 端口 IRQ 关联设置的优化是通过了解服务器拓扑，禁用 irqbalance，以及使用特定于 NIC 供应商的设置来实现的。Optimizations of the NIC port IRQ affinities settings are done with the knowledge of server topology, disabling the irqbalance, and using the NIC vendor-specific settings. 以下是特定于 Mellanox 的网络基础结构示例，用于帮助解释该配置。Below is an example of Mellanox specific network infrastructure to help explain the configuration. 请注意，这些命令将根据环境而改变。Note that the commands will change based on the environment. 请联系 NIC 供应商以获取进一步指导：Contact the NIC vendor for further guidance:

#disable irqbalance or get a snapshot of the IRQ settings and force the daemon to exit

systemctl disable irqbalance.service

#or

irqbalance --oneshot

#download the Mellanox mlnx_tuning_scripts tarball, https://www.mellanox.com/sites/default/files/downloads/tools/mlnx_tuning_scripts.tar.gz and extract it

tar -xvf mlnx_tuning_scripts.tar.gz

# be sure, common_irq_affinity.sh is executable. if not,

# chmod +x common_irq_affinity.sh

#display IRQ affinity for Mellanox NIC port; e.g eth0

./show_irq_affinity.sh eth0

#optimize for best throughput performance

./mlnx_tune -p HIGH_THROUGHPUT

#set hardware affinity to the NUMA node hosting physically the NIC and its port

./set_irq_affinity_bynode.sh `\cat /sys/class/net/eth0/device/numa_node` eth0

#verify IRQ affinity

./show_irq_affinity.sh eth0

#add IRQ coalescing optimizations

ethtool -C eth0 adaptive-rx off

ethtool -C eth0 adaptive-tx off

ethtool -C eth0 rx-usecs 750 tx-frames-irq 2048

#verify the settings

ethtool -c eth0

完成上述更改后，请使用以下命令验证 NIC 的速度，以确保其符合预期：After the above changes are done, verify the speed of the NIC to ensure it matches the expectation using the following command:

ethtool eth0 | grep -i Speed

其他高级内核/OS 配置Additional advanced Kernel/OS configuration

为获得最佳存储 IO 性能，建议使用适用于块设备的 Linux multiqueue 计划。For best storage IO performance, the use of Linux multiqueue scheduling for block devices is recommended. 这使得块层性能可以很好地随高速固态硬盘 (SSD) 和多核系统一起缩放。This enables the block layer performance to scale well with fast solid-state drives (SSDs) and multi-core systems. 如果你的 Linux 发行版中默认启用了文档，请查看该文档。Check the documentation if it is enabled by default in your Linux distributions. 在其他大多数情况下，使用 scsi_mod.use_blk_mq=y 启动内核将会启用此功能，即使正在使用的 Linux 分发版的文档对其提供了其他指导。In most other cases, booting the kernel with scsi_mod.use_blk_mq=y enables it, though documentation of the Linux distribution in use may have additional guidance on it. 这与上游 Linux 内核一致。This is consistent to the upstream Linux kernel.

因为 SQL Server 部署中常使用多路径 IO，所以还应启用 dm_mod 内核启动选项，将设备映射器 (DM) 多路径目标配置为使用 blk-mq 基础结构。As multipath IO is often used for SQL Server deployments, the device mapper (DM) multipath target should also be configured to use the blk-mq infrastructure by enabling the dm_mod.use_blk_mq=y kernel boot option. 默认值为 n(已禁用)。The default value is n (disabled). 当底层 SCSI 设备使用 blk-mq 时，此设置会在 DM 层降低锁定开销。This setting, when the underlying SCSI devices are using blk-mq, reduces locking overhead at the DM layer. 请参阅使用中 Linux 发行版的文档，以获取有关如何配置它的其他指导。Refer to the documentation of the Linux distribution in use for additional guidance on how to configure it.

配置交换文件Configure 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're running SQL Server on Linux in a virtual machine, make sure you select options to fix the amount of memory reserved for the virtual machine. 请勿使用 Hyper-V 动态内存等功能。Don't use features like Hyper-V Dynamic Memory.

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 使用 PROCESS AFFINITYUse PROCESS AFFINITY for Node and/or CPUs

对于所有用于 Linux OS 上的 SQL Server 的 NUMANODE 和/或 CPU(通常是所有节点和 CPU)，建议使用 ALTER SERVER CONFIGURATION 设置 PROCESS AFFINITY。It's recommended to use ALTER SERVER CONFIGURATION to set PROCESS AFFINITY for all the NUMANODEs and/or CPUs you're using for SQL Server (which is typically for all NODEs and CPUs) on a Linux OS. 处理器关联有助于保持高效的 Linux 和 SQL 计划行为。Processor affinity helps maintain efficient Linux and SQL Scheduling behavior. 使用 NUMANODE 选项是最简单的方法。Using the NUMANODE option is the simplest method. 即使你的计算机上只有一个 NUMA 节点，也请使用 PROCESS AFFINITY。Use PROCESS AFFINITY even if you have only a single NUMA Node on your computer. 有关如何设置 PROCESS AFFINITY 的详细信息，请参阅更改服务器配置文档。For more information on how to set PROCESS AFFINITY, see the ALTER SERVER CONFIGURATION article.

配置多个 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.

高级配置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 OS 配置的要求。These choices are based on the requirements of your workload and configuration of your Linux OS.

使用 mssql-conf 设置内存限制Set a memory limit with mssql-conf

为了确保 Linux OS 有足够的可用物理内存，默认情况下 SQL Server 进程只使用 80% 的物理 RAM。In order to ensure There's enough free physical memory for the Linux OS, the SQL Server process uses only 80% of the physical RAM by default. 对于某些系统，如果物理 RAM 很大，20% 可能是一个很大的数字。For some systems with 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 工具的文档和 memory.memorylimitmb 设置，该设置可控制对 SQL Server 可见的内存(以 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 OS 和其他 Linux 应用程序的问题。If you don't leave enough memory, you could experience problems with the Linux OS and other Linux applications.

后续步骤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.