服务器配置选项 (SQL Server)Server Configuration Options (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

您可以使用 SQL ServerSQL Server 或 sp_configure 系统存储过程通过配置选项来管理和优化 SQL Server Management StudioSQL Server Management Studio 资源。You can manage and optimize SQL ServerSQL Server resources through configuration options by using SQL Server Management StudioSQL Server Management Studio or the sp_configure system stored procedure. 大多数常用的服务器配置选项可以通过 SQL Server Management StudioSQL Server Management Studio来使用;而所有配置选项都可通过 sp_configure 来访问。The most commonly used server configuration options are available through SQL Server Management StudioSQL Server Management Studio; all configuration options are accessible through sp_configure. 在设置这些选项之前应该认真考虑这些选项对系统的影响。Consider the effects on your system carefully before setting these options. 有关详细信息,请参阅查看或更改服务器属性 (SQL Server)For more information, see View or Change Server Properties (SQL Server).

重要说明!!IMPORTANT!! 高级选项只能由有经验的数据库管理员或认证的 SQL ServerSQL Server 技术人员更改。Advanced options should be changed only by an experienced database administrator or certified SQL ServerSQL Server technician.

配置选项的分类Categories of Configuration Options

配置选项在下面两种情况下生效:Configuration options take effect either:

  • 在设置选项并发出 RECONFIGURE (在某些情况下为 RECONFIGURE WITH OVERRIDE)语句之后立即生效。Immediately after setting the option and issuing the RECONFIGURE (or in some cases, RECONFIGURE WITH OVERRIDE) statement. 重新配置某些选项可使计划缓存中的计划失效,并编译新计划。Reconfiguring certain options will invalidate plans in the plan cache, causing new plans to be compiled. 有关详细信息,请参阅 DBCC FREEPROCCACHE (Transact-SQL)For more information, see DBCC FREEPROCCACHE (Transact-SQL).

    -或 --or-

  • 执行以上操作并重新启动 SQL ServerSQL Server实例之后生效。After performing the above actions and restarting the instance of SQL ServerSQL Server.

需要重新启动 SQL ServerSQL Server 的选项最初只在 value 列中显示更改后的值。Options that require SQL ServerSQL Server to restart will initially show the changed value only in the value column. 在重新启动后,新值将出现在 value 列和 value_in_use 列中。After restart, the new value will appear in both the value column and the value_in_use column.

但有些选项需要在重新启动服务器后,新的配置值才能生效。Some options require a server restart before the new configuration value takes effect. 如果设置了新值并在没有重新启动服务器的情况下运行 sp_configure,则新值将出现在配置选项的“value”列中,而不是出现在“value_in_use”列中。 If you set the new value and run sp_configure before restarting the server, the new value appears in the configuration options value column, but not in the value_in_use column. 重新启动服务器之后,新值出现在“value_in_use”列中。 After restarting the server, the new value appears in the value_in_use column.

自配置选项是指 SQL ServerSQL Server 根据系统需要进行调整的选项。Self-configuring options are those that SQL ServerSQL Server adjusts according to the needs of the system. 大多数情况下,这使您无需手动设置值。In most cases, this eliminates the need for setting the values manually. 例如,“最大工作线程数” 选项和“用户连接数”选项。Examples include the max worker threads option and the user connections option.

配置选项表Configuration Options Table

下表列出了所有可用的配置选项、可能的设置范围及其默认值。The following table lists all available configuration options, the range of possible settings, and default values. 配置选项按以下字母代码标记:Configuration options are marked with letter codes as follows:

另请参阅See also

sp_configure (Transact-SQL) sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL)RECONFIGURE (Transact-SQL)
DBCC FREEPROCCACHE (Transact-SQL)DBCC FREEPROCCACHE (Transact-SQL)