服务器配置选项 (SQL Server)

适用于: SQL Server(所有受支持的版本)

可以使用 SQL Server Management Studio 或 sp_configure 系统存储过程通过配置选项来管理和优化 SQL Server 资源。 大多数常用的服务器配置选项可以通过 SQL Server Management Studio 来使用;而所有配置选项都可通过 sp_configure 来访问。 在设置这些选项之前应该认真考虑这些选项对系统的影响。 有关详细信息,请参阅查看或更改服务器属性 (SQL Server)

重要

高级选项只能由有经验的数据库管理员或认证的 SQL Server 技术人员更改。

配置选项的分类

如果看不到配置更改的效果,则可能没有安装。 检查配置选项的 run_value 是否已更改。

配置选项在下面两种情况下生效:

  • 在设置选项并发出 RECONFIGURE (在某些情况下为 RECONFIGURE WITH OVERRIDE)语句之后立即生效。 重新配置某些选项可使计划缓存中的计划失效,并编译新计划。 有关详细信息,请参阅 DBCC FREEPROCCACHE (Transact-SQL)

    - 或 -

  • 执行上述操作并重启 SQL Server 实例后。

可以使用 sys.configurations 目录视图来确定 config_valuevalue 列)、run_valuevalue_in_use 列)以及配置选项是否需要重启数据库引擎(is_dynamic 列)。

如果需要重启 SQL Server,选项最初只在 value 列中显示更改后的值。 重启之后,新值将出现在 value 列和 value_in_use 列中。

但有些选项需要在重新启动服务器后,新的配置值才能生效。 如果设置了新值并在重启服务器之前运行 sp_configure,则新值将出现在 sys.configurations 目录视图的 value 列中,而不是出现在 value_in_use 列中。 重启服务器之后,新值将出现在 value_in_use 列中。

备注

sp_configure 结果集中的 config_value 等同于 sys.configurations 目录视图的 value 列,run_value 等同于 value_in_use 列。

自配置选项是指 SQL Server 根据系统需要进行调整的选项。 大多数情况下,这使您无需手动设置值。 例如,“最大工作线程数”选项和“用户连接数”选项。

以下查询可用于确定是否尚未安装任何配置的值:

SELECT *
FROM sys.configurations
WHERE [value] <> [value_in_use];

如果该值是对配置选项所做的更改,但 value_in_use 不相同,则说明 RECONFIGURE 命令未运行或失败,或者必须重启数据库引擎。

有两个配置选项,其中 valuevalue_in_use 可能不相同,以下为预期行为:

  • 最大服务器内存 (MB) - 0 的默认配置的值将在 value_in_use 列中显示为 2147483647

  • 最小服务器内存 (MB) - 0 的默认配置的值可能显示为 value_in_use 列中的 8(在 32 位系统上)或 16(在 64 位系统上)。 在某些情况下,如果 value_in_use 显示为 0,则实际的 value_in_use8(32 位)或 16(64 位)。

is_dynamic 列可用于确定配置选项是否需要重启。 is_dynamic 列中的 1 值表示,运行 RECONFIGURE 命令时,新值将立即生效。 在某些情况下,数据库引擎可能不会立即评估新值,但在正常的执行过程中会执行此操作。 is_dynamic 列中的 0 值表示,除非重启数据库引擎,否则即使运行 RECONFIGURE 命令,更改的配置值也不会生效。

对于非动态配置选项,无法判断是否已运行 RECONFIGURE 命令来应用配置更改。 在重启 SQL Server 以应用配置更改之前,请运行以下 RECONFIGURE 命令,以确保下次重启 SQL Server 时,所有配置更改都将生效。

配置选项

下表列出了所有可用的配置选项、可能的设置范围及其默认值。 配置选项按以下字母代码标记:

  • A = 高级选项,仅应由有经验的数据库管理员或认证的 SQL Server 专业人员更改,并且需要将 show advanced options 设置为 1

  • RR = 需要重启数据库引擎的选项。

  • RP = 需要重启 PolyBase 引擎的选项。

  • SC = 自配置选项。

配置选项 最小值 最大值 默认
访问检查缓存桶计数 (A) 0 16384 0
访问检查缓存配额 (A) 0 2147483647 0
即席分布式查询 (A) 0 1 0
ADR 清除程序重试超时(分钟)

适用于:SQL Server 2019 (15.x) 及更高版本。
0 32767 15
ADR 预先分配因素

适用于:SQL Server 2019 (15.x) 及更高版本。
0 32767 4
affinity I/O mask (A,RR) -2147483648 2147483647 0
affinity mask (A) -2147483648 2147483647 0
affinity64 I/O mask(A,仅适用于 64 位版本的 SQL Server) -2147483648 2147483647 0
affinity64 mask(A,RR)仅适用于 64 位版本的 SQL Server -2147483648 2147483647 0
代理 XP (A) 0 1 0

(启动 SQL Server 代理时更改为 1。如果在安装期间将 SQL Server 代理设置为自动启动,则默认值为 0。)
允许 PolyBase 导出

适用于: SQL Server 2016 (13.x) 和更高版本。
0 1 0
允许更新(已过时。请勿使用。在重新配置期间会导致错误。) 0 1 0
自动 soft-NUMA 已禁用 0 1 0
备份校验和默认值 0 1 0
backup compression default 0 1 - SQL Server 2022 (16.x) 预览版之前的版本

2 - SQL Server 2022 (16.x) 预览版及更高版本
0
备份压缩算法 (A)

适用于:SQL Server 2022 (16.x) 预览版及更高版本。
0 1 0
blocked process threshold (A) 5 86400 0
c2 审核模式 (A,RR) 0 1 0
clr enabled 0 1 0
clr 严格安全性 (A)

适用于:SQL Server 2017 (14.x) 及更高版本。
0 1 0
列加密 enclave 类型(A、RR) 0 2 0
common criteria compliance enabled (A,RR) 0 1 0
contained database authentication 0 1 0
并行的开销阈值 (A) 0 32767 5
cross db ownership chaining 0 1 0
cursor threshold (A) -1 2147483647 -1
Database Mail XPs (A) 0 1 0
default full-text language (A) 0 2147483647 2052
default language 0 9999 0
default trace enabled (A) 0 1 1
disallow results from triggers (A) 0 1 0
EKM provider enabled 0 1 0
外部脚本已启用 (SC) (RR)

适用于: SQL Server 2016 (13.x) 和更高版本。
0 1 0
文件流访问级别 0 2 0
填充因子 (A,RR) 0 100 0
FT 爬网带宽(最大值)(A) 0 32767 100
FT 爬网带宽(最小值) (A) 0 32767 0
FT 通知带宽(最大值)(A) 0 32767 100
FT 通知带宽(最小值)(A) 0 32767 0
Hadoop 连接 (RP)

适用于: SQL Server 2016 (13.x) 和更高版本。
0 7 0
in-doubt xact resolution (A) 0 2 0
index create memory (A,SC) 704 2147483647 0
lightweight pooling (A,RR) 0 1 0
locks (A,RR,SC) 5000 2147483647 0
max degree of parallelism (A) 0 32767 0
max full-text crawl range (A) 0 256 4
max server memory (A,SC) 16 2147483647 2147483647
max text repl size 0 2147483647 65536
max worker threads (A) 128 32767

对于 32 位 SQL Server,建议最大为 1024;对于 64 位 SQL Server,建议最大为 2048。

注意:SQL Server 2014 (12.x) 是 32 位操作系统支持的最新版本。
0

归零操作会根据逻辑处理器的数量自动配置最大工作线程数,可以使用公式(256 + (<逻辑处理器数> - 4) * 8)来计算 32 位 SQL Server 的线程数,使用公式(512 + (<逻辑处理器数> - 4) * 8)来计算 64 位 SQL Server 的线程数。

注意:SQL Server 2014 (12.x) 是 32 位操作系统支持的最新版本。
media retention (A,RR) 0 365 0
min memory per query (A) 512 2147483647 1024
min server memory (A,SC) 0 2147483647 0
嵌套触发器 0 1 1
network packet size (A) 512 32767 4096
Ole Automation Procedures (A) 0 1 0
open objects (A,RR,已过时) 0 2147483647 0
针对即席工作负荷进行优化 (A) 0 1 0
PH_timeout (A) 1 3600 60
PolyBase 已启用 (RR)

适用于:SQL Server 2019 (15.x) 及更高版本。
0 1 0
polybase 网络加密 0 1 1
precompute rank (A) 0 1 0
priority boost (A,RR) 0 1 0
query governor cost limit (A) 0 2147483647 0
查询等待 (A) -1 2147483647 -1
恢复间隔 (A,SC) 0 32767 0
远程访问 (RR) 0 1 1
remote admin connections 0 1 0
远程数据存档 0 1 0
remote login timeout 0 2147483647 10
remote proc trans 0 1 0
remote query timeout 0 2147483647 600
Replication XPs 选项 (A) 0 1 0
scan for startup procs (A,RR) 0 1 0
server trigger recursion 0 1 1
set working set size (A,RR,已过时) 0 1 0
show advanced options 0 1 0
SMO 和 DMO XP (A) 0 1 1
取消显示恢复模型错误 (A)

适用于:Azure SQL 托管实例。
0 1 0
tempdb 元数据内存优化 (A)

适用于:SQL Server 2019 (15.x) 及更高版本。
0 1 0
transform noise words (A) 0 1 0
两位数年份截止 (A) 1753 9999 2049
user connections (A,RR,SC) 0 32767 0
user options 0 32767 0
xp_cmdshell (A) 0 1 0

另请参阅