sql性能优化：MS-SQL（SQL Server）服务器配置选项（sp_configure ）对照表
2019服务器配置选项 (SQL Server) - SQL Server | Microsoft Learn
2012服务器配置选项 | Microsoft Learn
介绍
可以使用 SQL Server Management Studio 或 sp_configure 系统存储过程通过配置选项来管理和优化 SQL Server 资源。 大多数常用的服务器配置选项可以通过 SQL Server Management Studio 来使用；而所有配置选项都可通过 sp_configure 来访问。 在设置这些选项之前应该认真考虑这些选项对系统的影响。 有关详细信息，请参阅查看或更改服务器属性 (SQL Server)。
重要
高级选项只能由有经验的数据库管理员或认证的 SQL Server 技术人员更改。
配置选项的分类
配置选项在下面两种情况下生效：
-
在设置选项并发出 RECONFIGURE（在某些情况下为 RECONFIGURE WITH OVERRIDE）语句之后立即生效。
-或-
-
执行以上操作并重新启动 SQL Server 实例之后生效。
需要重新启动 SQL Server 的选项最初只在 value 列中显示更改后的值。 重新启动之后，新值将出现在 value 列和 value_in_use 列中。
但有些选项需要在重新启动服务器后，新的配置值才能生效。 如果设置了新值并在没有重新启动服务器的情况下运行 sp_configure，则新值将出现在配置选项的 value 列中，而不是出现在 value_in_use 列中。 重新启动服务器之后，新值将出现在 value_in_use 列中。
自配置选项是指 SQL Server 根据系统需要进行调整的选项。 大多数情况下，这使您无需手动设置值。 示例包括 min server memory 和 max server memory 选项，以及 user connections 选项。
备注
sp_configure结果集中的config_value等同于sys.configurations目录视图的value列，run_value等同于value_in_use列。
配置选项表
下表列出了所有可用的配置选项、可能的设置范围及其默认值。 配置选项按以下字母代码标记：
-
A= 高级选项，仅允许有经验的数据库管理员或经过认证的 SQL Server 技术人员执行更改，并且需要将 show advanced options 设置为 1。
-
RR = 需要重新启动数据库引擎的选项。
-
SC = 自配置选项。
-
。
配置选项
最小值
最大值
默认
描述 access check cache bucket count (A)
0
16384
0
Access check cache quota 和 access check cache bucket count 选项用于控制访问检查结果缓存的项数和哈希存储桶数。 access check cache quota (A)
0
2147483647
0
Access check cache quota 和 access check cache bucket count 选项用于控制访问检查结果缓存的项数和哈希存储桶数。 ad hoc distributed queries (A)
0
1
0
即席分布式查询选项。此选项设置为 1 时，SQL Server 允许进行即席访问。如果此选项未设置或设置为 0，则 SQL Server 不允许进行即席访问。 affinity I/O mask（A，RR）
-2147483648
2147483647
0
SQL Server 通过以下两个关联掩码选项来支持处理器关联：affinity mask（也称为 CPU 关联掩码）和 affinity I/O mask。 有关 affinity mask 选项的详细信息，请参阅 affinity mask 服务器配置选项。 对具有 33 到 64 个处理器的服务器的 CPU 和 I/O 关联支持还要求分别使用 affinity64 mask 服务器配置选项和 affinity64 I/O mask 服务器配置选项。 affinity64 I/O mask（A，仅适用于 64 位版本的 SQL Server）
-2147483648
2147483647
0
Affinity64 I/O mask 与 affinity I/O mask 选项类似，用于将 SQL Server 磁盘 I/O 绑定到指定的 CPU 子集。 affinity mask (A)
-2147483648
2147483647
0
后续版本的 Microsoft SQL Server 将删除该功能。请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。 请改用 ALTER SERVER CONFIGURATION (Transact-SQL)。
SQL Server 通过以下两个关联掩码选项来支持处理器关联：affinity mask（也称为 CPU affinity mask）和 affinity I/O mask。 有关 affinity I/O mask 选项的详细信息，请参阅 affinity I/O mask 服务器配置选项。 对具有 33 到 64 个处理器的服务器的 CPU 和 I/O 关联支持要求分别使用 affinity64 mask 服务器配置选项和 affinity64 I/O mask 服务器配置选项。
affinity64 mask (A, RR)，仅适用于 64 位版本的 SQL Server
-2147483648
2147483647
0
Affinity64 mask 选项。affinity64 mask 将处理器绑定到特定的线程，这与 affinity mask 选项相似。 Agent XPs (A)
0
1
0
（当 SQL Server 代理启动时，更改为 1。 如果 SQL Server 代理设置为在安装过程中自动启动，则默认值为 0。）
“代理 XP”选项。使用 Agent XPs 选项可以启用此服务器上的 SQL Server 代理扩展存储过程。如果禁用此选项，则 SQL Server Management Studio 对象资源管理器将不显示 SQL Server 代理节点。 allow updates（已过时。 请勿使用。 将在重新配置期间导致错误。）
0
1
0
已过时。 请勿使用。 将在重新配置期间导致错误。 backup compression default
0
1
0
备份压缩默认选项。仅在 SQL Server 2008 Enterprise 和更高版本中，如果用户不指定 WITH COMPRESSION 或 WITH NO_COMPRESSION，则由 BACKUP Transact-SQL 语句用于选择备份压缩设置。 blocked process threshold (A)
0
86400
0
Blocked process threshold 选项。blocked process threshold 选项用于指定阈值（以秒为单位），超过该阈值将生成阻塞的进程报表。 c2 audit mode（A，RR）
0
1
0
C2 审核模式选项。可以通过 SQL Server Management Studio 或使用 sp_configure 中的 c2 audit mode 选项来配置 C2 审核模式。选择此选项将配置服务器，以记录对语句和对象的失败和成功的访问尝试。 clr enabled
0
1
0
Clr enabled 选项。可以使用 clr enabled 选项指定 SQL Server 是否可以运行用户程序集。 common criteria compliance enabled (A，RR)
0
1
0
common criteria compliance enabled contained database authentication
0
0
contained database authentication cost threshold for parallelism (A)
0
32767
5
Cost threshold for parallelism 选项。使用 cost threshold for parallelism 选项指定 Microsoft SQL Server 创建和运行并行查询计划的阈值。 cross db ownership chaining
0
1
0
Cross db ownership chaining 选项。使用 cross db ownership chaining 选项可以为 Microsoft SQL Server 实例配置跨数据库所有权链接。 cursor threshold (A)
-1
2147483647
-1
Cursor threshold 选项。使用 cursor threshold 选项可以指定游标集中的行数，超过此行数，将异步生成游标键集。 Database Mail XPs (A)
0
1
0
Database Mail XPs 选项。使用 DatabaseMail XPs 选项可以在此服务器上启用数据库邮件。 default full-text language (A)
0
2147483647
1033
Default full-text language 选项。使用 default full-text language 选项可以指定全文索引列的默认语言值。语言分析将对全文索引的所有数据执行，并且取决于数据的语言。该选项的默认值设置为服务器的语言。 default language
0
9999
0
Default language 选项。使用 default language 选项为所有新创建的登录指定默认语言。 default trace enabled (A)
0
1
1
Default trace enabled 选项。使用 default trace enabled 选项可启用或禁用默认跟踪日志文件。默认跟踪功能提供了丰富持久的活动日志，并主要根据配置选项进行更改。 disallow results from triggers (A)
0
1
0
Disallow results from triggers 选项。使用 disallow results from triggers 选项可控制是否让触发器返回结果集。 EKM provider enabled
0
1
0
EKM provider enabled filestream_access_level
0
2
0
文件流访问级别选项。可以使用 filestream_access_level 选项来更改此 SQL Server 实例的 FILESTREAM 访问级别。 fill factor（A，RR）
0
100
0
Fill factor 选项。此选项设置服务器范围内的默认填充因子值。提供填充因子是为了优化索引数据存储和性能。 ft crawl bandwidth (max)，请参阅 ft crawl bandwidth(A)
0
32767
100
Ft crawl bandwidth 选项。使用 ft crawl bandwidth 选项可指定较大内存缓冲池可增长到多大。较大内存缓冲区的大小为 4 MB。max 参数值可指定全文内存管理器应在较大缓冲池中保持的最大缓冲区数。如果 max 的值为零，则较大缓冲池中可以保持的缓冲区数没有上限。 ft crawl bandwidth (min)，请参阅 ft crawl bandwidth(A)
0
32767
0
Ft crawl bandwidth 选项。min 参数可指定较大内存缓冲池中必须保持的最小内存缓冲区数。Microsoft SQL Server 内存管理器发出请求后，将释放所有额外的缓冲池，但将保留该最低数量的缓冲区。不过，如果指定的 min 值为零，则释放所有内存缓冲区。 ft notify bandwidth (max)，请参阅 ft notify bandwidth(A)
0
32767
100
Ft notify bandwidth 选项。使用 ft notify bandwidth 选项可以指定小内存缓冲区的池可以增长到的大小。小内存缓冲区的大小为 64 KB。max 参数值指定全文内存管理器在小缓冲池中应该维护的最大缓冲区数。如果 max 值为零，则可以位于小缓冲池中的缓冲区数没有上限。 ft notify bandwidth (min)，请参阅 ft notify bandwidth(A)
0
32767
0
Ft notify bandwidth 选项。min 参数指定在小内存缓冲区的池中必须维护的最小内存缓冲区数。Microsoft SQL Server 内存管理器发出请求后，将释放所有额外的缓冲池，但将保留该最低数量的缓冲区。不过，如果指定的 min 值为零，则释放所有内存缓冲区。 index create memory（A，SC）
704
2147483647
0
Index create memory 选项。在 SQL Server 中，index create memory 选项控制最初为创建索引分配的最大内存量。如果随后创建索引时需要更多内存，而且有内存可供使用，服务器将使用可用的内存，从而超出此选项的设置。如果没有内存可供使用，则继续使用已分配的内存来创建索引。 in-doubt xact resolution (A)
0
2
0
In-doubt xact resolution 选项。使用 in-doubt xact resolution 选项可以控制 Microsoft 分布式事务处理协调器 (MS DTC) 无法解决的默认事务结果。事务无法解决可能与 MS DTC 停止工作或恢复时的未知事务结果有关。 lightweight pooling (A, RR)
0
1
0
Lightweight pooling 选项。使用 lightweight pooling 选项可以减少有时在对称多处理 (SMP) 环境下遇到的、与过多的上下文切换有关的系统开销。 如果出现过多的上下文切换，轻型池可以通过上下文切换内联化，从而降低用户/内核环的转换频率，达到提高吞吐量的目的。 locks（A，RR，SC）
5000
2147483647
0
Locks 选项。使用 locks 选项可以设置可用的锁的最大个数，从而限制数据库引擎为这些锁所消耗的内存。默认设置为 0，即允许数据库引擎根据不断变化的系统要求动态地分配和释放锁结构。 max degree of parallelism (A)
0
32767
0
Max degree of parallelism 选项。当 SQL Server 在具有多个微处理器或 CPU 的计算机上运行时，它将为每个并行计划执行检测最佳并行度（即运行一个语句所使用的处理器数）。 max full-text crawl range (A)
0
256
4
Max full-text crawl range 选项。使用 max full-text crawl range 选项可以优化 CPU 使用率，从而提高完全爬网时的爬网性能。使用此选项，可以指定索引完全爬网时 Microsoft SQL Server 使用的分区数。例如，如果有许多 CPU 且它们的使用率并非最佳，则可以增加此选项的最大值。除了此选项以外，SQL Server 还使用众多其他因素（如表中的行数和 CPU 数）来确定应该使用的实际分区数。 max server memory（A，SC）
16
2147483647
2147483647
服务器内存选项。使用 min server memory 和 max server memory 这两个服务器内存选项可以重新配置 Microsoft SQL Server 实例所使用的缓冲池的内存量 (MB)。默认情况下，SQL Server 的内存要求会根据可用系统资源的情况动态地变化。min server memory 的默认设置是 0，max server memory 的默认设置是操作系统内存（MB）-520（MB）。可为 max server memory 指定的最小内存量为 16 兆字节 (MB)。如果设定值为default，表示该参数随内存规格变化。 max text repl size
0
2147483647
65536
Max text repl size 选项。使用 max text repl size 选项，可指定使用单个 INSERT、UPDATE、WRITETEXT 或 UPDATETEXT 语句可以向复制列或已捕获列添加的 text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、xml 和 image 数据的最大大小（字节）。默认值为 65536。值为 -1 表示没有限制，而非数据类型指定的限制。此选项适用于事务复制和变更数据捕获。当将服务器配置为具有事务复制功能和变更数据捕获功能时，指定的值将适用于这两项功能。快照复制和合并复制将会忽略此选项。 max worker threads (A)
128
32767
（对于 32 位 SQL Server，建议最大为 1024；对于 64 位 SQL Server，建议最大为 2048。）
0
归零操作将根据处理器的数量自动配置最大工作线程数，可以使用公式 (256+(<processors> -4) * 8) 来计算 32 位 SQL Server 的线程数，64 位 SQL Server 的线程数为 32 位的 2 倍。
Max worker threads 选项。使用 max worker threads 选项可以配置 Microsoft SQL Server 进程可使用的工作线程数。SQL Server 使用 Microsoft Windows 2000 和 Windows Server 2003 操作系统的本机线程服务，从而使一个或多个线程支持 SQL Server 同时支持的每一个网络，另一个线程处理数据库检查点，而线程池则处理所有用户。 media retention（A，RR）
0
365
0
Media retention 选项。使用 media retention 选项，可以为各个备份集的保留时间提供一个系统范围默认值。media retention 选项可以防止在指定的日期前覆盖备份。如果设置了 media retention，则无需在每次进行系统备份时都指定系统备份的保持时间。默认值为 0 天。如果未等设定的天数过去即使用备份媒体，Microsoft SQL Server 将会发出警告消息。除非更改默认值，否则 SQL Server 不会发出警告。此选项可以由 BACKUP 命令的 RETAINDAYS 子句覆盖。 min memory per query (A)
512
2147483647
1024
Min memory per query 选项。使用 min memory per query 选项指定分配给查询执行时所需要的最小内存量 (KB)。例如，如果将 min memory per query 设置为 2,048 KB，则查询保证将至少获取那么多的总内存。可以将 min memory per query 设置为从 512 到 2,147,483,647 字节 (2 GB) 的任何值。默认值为 1,024 KB。 min server memory（A，SC）
0
2147483647
0
服务器内存选项。使用 min server memory 和 max server memory 这两个服务器内存选项可以重新配置 Microsoft SQL Server 实例所使用的缓冲池的内存量 (MB)。默认情况下，SQL Server 的内存要求会根据可用系统资源的情况动态地变化。min server memory 的默认设置是 0，max server memory 的默认设置是 2147483647。可为 max server memory 指定的最小内存量为 16 兆字节 (MB)。 nested triggers
0
1
1
Nested triggers 选项。使用 nested triggers 选项可控制 AFTER 触发器是否可级联；级联是指执行某项操作将初始化另一个触发器，而该触发器又将初始化另外一个，依此类推。如果 nested triggers 设置为 0，AFTER 触发器不能级联。如果 nested triggers 设置为 1（默认值），AFTER 触发器最多能级联 32 级。不管此选项如何设置，INSTEAD OF 触发器都可以嵌套。 network packet size (A)
512
32767
4096
Network packet size 选项。使用 network packet size 选项可以设置整个网络中使用的数据包大小（以字节为单位）。 Ole Automation Procedures (A)
0
1
0
Ole Automation Procedures 选项。使用 Ole Automation Procedures 选项可指定是否可以在 Transact-SQL 批处理中实例化 OLE Automation 对象。还可以使用基于策略的管理或者 sp_configure 存储过程来配置这一选项。 open objects（A，RR，已过时）
0
2147483647
0
open objects已过时 optimize for ad hoc workloads (A)
0
1
0
针对即席工作负荷进行优化。针对即席工作负荷进行优化”选项用于提高包含许多一次性临时批处理的工作负荷计划缓存的效率。如果该选项设置为 1，则数据库引擎将在首次编译批处理时在计划缓存中存储一个编译的小计划存根，而不是存储完全编译的计划。这种情况下不会让未重复使用的编译计划填充计划缓存，从而有助于缓解内存压力。 PH_timeout (A)
1
3600
60
Ph timeout 选项。使用 ph timeout 选项可以指定全文协议处理程序在超时前等待连接到数据库的时间（秒）。默认值为 60 秒。如果连接尝试因临时的网络问题而超时，可以增加 ph timeout 值。 precompute rank (A)
0
1
0
precompute rank priority boost（A，RR）
0
1
0
Priority boost 选项。使用 priority boost 选项可以指定 Microsoft SQL Server 是否应当以比相同计算机上的其他进程更高的 Microsoft Windows 2000、Windows Server 2003、Windows 2008 或 Windows 2008 R2 计划优先级运行。如果将该选项设置为 1，SQL Server 将以优先级基数 13 在 Windows 2000、Windows Server 2003、 Windows 2008 或 Windows 2008 R2 计划程序中运行。默认值为 0，其优先级基数为 7。 query governor cost limit (A)
0
2147483647
0
Query governor cost limit 选项。使用 query governor cost limit 选项指定查询可以运行的时间段上限。估计运行时间超过此限制的查询返回一个错误且不执行。查询开销是指在特定硬件配置中完成查询所需的估计占用时间（以秒为单位）。 query wait (A)
-1
2147483647
-1
Query wait 选项。query wait 选项可以指定一个查询在超时前等待所需资源的时间（以秒为单位，范围从 0 到 2147483647）。如果使用默认值 -1 或指定 -1，则可计算出超时时间是估计查询开销的 25 倍。 recovery interval（A，SC）
0
32767
0
Recovery interval 选项。使用 recovery interval 选项可以设置每个 Microsoft SQL Server 还原数据库所需的最大分钟数。每次当 SQL Server 实例启动时，它就会恢复各个数据库，回滚未提交的事务，并前滚已提交但更改内容在 SQL Server 实例停止时尚未写入磁盘中的事务。此配置选项设置了 SQL Server 在恢复每一个数据库时所应花时间的上限。默认值为 0，指示由 SQL Server 自动配置。实际上，这表示每个数据库的恢复时间不超过 1 分钟，对于每个活动的数据库大约每 1 分钟有一个检查点。 remote access (RR)
0
1
1
Remote access 选项。使用 remote access 选项可以从运行 Microsoft SQL Server 实例的本地或远程服务器上控制存储过程的执行。将 remote access 设置为 1表示允许从远程服务器执行本地存储过程或从本地服务器执行远程存储过程。将此选项设置为 0（默认值）表示阻止本地存储过程在远程服务器上执行或远程存储过程在本地服务器上执行。 remote admin connections
0
1
0
Remote admin connections 选项。SQL Server 提供了专用管理员连接 (DAC)。DAC 允许管理员访问运行的服务器以执行诊断函数或 Transact-SQL 语句，或对服务器上的问题进行故障排除，即使服务器已锁定或在非正常状态下运行以及不响应 SQL Server 数据库引擎 连接。默认情况下，只有服务器上的客户端可以使用 DAC。若要在远程计算机上启用客户端应用程序以使用 DAC，请使用 sp_configure 的远程管理连接选项。 remote login timeout
0
2147483647
10
Remote login timeout 选项。使用 remote login timeout 选项可以指定远程登录失败返回前等待的秒数。例如，如果您尝试登录到一个远程服务器而该服务器已关闭，remote login timeout 可确保您在计算机停止登录尝试前不必无限期地等待下去。 remote proc trans
0
1
0
Remote proc trans 选项。使用 remote proc trans 选项可通过 Microsoft 分布式事务处理协调器 (MS DTC) 事务保护服务器到服务器过程的操作。将 remote proc trans 设置为 1 以提供 MS DTC 协调的分布式事务，该事务能保护事务的 ACID（原子、一致、隔离和持久）属性。将该选项设置为 1 后所有新开始的会话会继承该设置并将它作为默认值。 remote query timeout
0
2147483647
600
Remote query timeout 选项。使用 remote query timeout 选项可指定在 Microsoft SQL Server 超时之前远程操作可以持续的时间（秒）。默认值为 600，表示允许等待 10 分钟。该值将应用到由作为远程查询的数据库引擎初始化的发送连接。该值不会对数据库引擎接收的查询产生任何影响。 Replication XPs 选项 (A)
0
1
0
Replication XPs 选项 scan for startup procs（A，RR）
0
1
0
Scan for startup procs 选项。使用 scan for startup procs 选项扫描在 Microsoft SQL Server 启动时自动执行的存储过程。如果将该选项设置为 1，则 SQL Server 将扫描服务器上定义的所有自动运行的存储过程，并运行这些过程。scan for startup procs 的默认值为 0（不扫描）。 server trigger recursion
0
1
1
Server trigger recursion 选项。使用 server trigger recursion 选项可指定是否允许服务器级触发器递归激发。当此选项设置为 1 (ON) 时，将允许服务器级触发器递归激发。当设置为 0 (OFF) 时，服务器级触发器不能递归激发。当 server trigger recursion 选项设置为 0 (OFF) 时，仅阻止直接递归。（若要禁用间接递归，请将 nested triggers 选项设置为 0。）该选项的默认值为 1 (ON)。该设置更改后立即生效，而不需要重新启动服务器。 set working set size（A，RR，已过时）
0
1
0
Set working set size 选项。 show advanced options
0
1
0
Show advanced options 选项。show advanced options 选项用来显示 sp_configure 系统存储过程高级选项。当 show advanced options 设置为 1 时，可以使用 sp_configure 列出高级选项。默认值为 0。 SMO and DMO XPs (A)
0
1
1
SMO and DMO XPs 选项。SMO and DMO XPs选项用于在此服务器上启用 SQL Server 管理对象 (SMO) 和 SQL 分布式管理对象 (SQL-DMO) 扩展存储过程。 transform noise words (A)
0
1
0
Transform noise words 选项。使用 transform noise words 选项可以取消干扰词或 stopwords 导致全文查询的布尔操作失败或返回零行时产生的错误消息。此选项对于使用其布尔操作包括干扰词的 CONTAINS 谓词的全文查询非常有用。 two digit year cutoff (A)
1753
9999
2049
Two digit year cutoff 选项。two digit year cutoff 选项为了将 2 位数字的年份解释为 4 位数字的年份，从 1753 到 9999 之间选出一个整数来表示世纪截止年份。 user connections（A，RR，SC）
0
32767
0
User connections 选项。使用 user connections 选项可以指定 Microsoft SQL Server 上允许同时建立的最大用户连接数。实际允许的用户连接数还取决于正使用的 SQL Server 版本以及应用程序和硬件的限制。SQL Server 允许的最大用户连接数为 32767。 user options
0
32767
0
User options 选项。使用 user options 选项可以指定适用于所有用户的全局默认值。将针对用户工作会话的持续时间，建立一个默认查询处理选项的列表。user options 选项允许您更改 SET 选项的默认值（如果服务器的默认设置不合适）。用户可以使用 SET 语句覆盖这些默认值。可以为新登录名动态配置 user options。更改 user options 的设置后，新的登录名会话将使用新的设置，当前登录名会话受不影响。 xp_cmdshell (A)
0
1
0
Xp_cmdshell 选项。SQL Server 2005 中引入的 xp_cmdshell 选项是服务器配置选项，使系统管理员能够控制是否可以在系统上执行 xp_cmdshell 扩展存储过程。
