sp_configure (Transact-SQL)sp_configure (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

显示或更改当前服务器的全局配置设置。Displays or changes global configuration settings for the current server.


有关数据库级配置选项,请参阅ALTER DATABASE 作用域(配置 transact-sql)For database-level configuration options, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). 若要配置软件 NUMA,请参阅软 numa (SQL Server)To configure Soft-NUMA, see Soft-NUMA (SQL Server).

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


-- Syntax for SQL Server  
sp_configure [ [ @configname = ] 'option_name'   
    [ , [ @configvalue = ] 'value' ] ]  
-- Syntax for Parallel Data Warehouse  
-- List all of the configuration options  
-- Configure Hadoop connectivity  
sp_configure [ @configname= ] 'hadoop connectivity',  
             [ @configvalue = ] { 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 }  


[ @configname = ] 'option_name' 是配置选项的名称。[ @configname = ] 'option_name' Is the name of a configuration option. option_name 的数据类型为 varchar(35) ,默认值为 NULL。option_name is varchar(35), with a default of NULL. SQL Server 数据库引擎SQL Server Database Engine能够识别构成配置名称的任何唯一字符串。The SQL Server 数据库引擎SQL Server Database Engine recognizes any unique string that is part of the configuration name. 如果未指定该参数,则返回选项的完整列表。If not specified, the complete list of options is returned.

有关可用配置选项及其设置的信息,请参阅服务器配置选项(SQL Server)For information about the available configuration options and their settings, see Server Configuration Options (SQL Server).

[ @configvalue = ] 'value' 是新的配置设置。[ @configvalue = ] 'value' Is the new configuration setting. value 的数据类型为 int,默认值为 NULL。value is int, with a default of NULL. 最大值取决于各个选项。The maximum value depends on the individual option.

若要查看每个选项的最大值,请参阅sys.databases目录视图的最大值列。To see the maximum value for each option, see the maximum column of the sys.configurations catalog view.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

在不带参数的情况下执行时, sp_configure将返回包含五列的结果集,并按字母顺序升序排列选项,如下表所示。When executed with no parameters, sp_configure returns a result set with five columns and orders the options alphabetically in ascending order, as shown in the following table.

Config_valuerun_value的值不是自动等效的。The values for config_value and run_value are not automatically equivalent. 使用sp_configure更新配置设置后,系统管理员必须使用 "重新配置" 或 "使用替代重新配置" 更新正在运行的配置值。After updating a configuration setting by using sp_configure, the system administrator must update the running configuration value by using either RECONFIGURE or RECONFIGURE WITH OVERRIDE. 有关详细信息,请参见“备注”部分。For more information, see the Remarks section.

列名Column name 数据类型Data type 描述Description
名称name nvarchar(35)nvarchar(35) 配置选项的名称。Name of the configuration option.
最小值minimum intint 配置选项的最小值。Minimum value of the configuration option.
最大值maximum intint 配置选项的最大值。Maximum value of the configuration option.
config_valueconfig_value intint 配置选项使用sp_configure设置的值(值在sys.databases中)。Value to which the configuration option was set using sp_configure (value in sys.configurations.value). 有关这些选项的详细信息,请参阅服务器配置(选项) SQL Server (sys.databases transact-sql)For more information about these options, see Server Configuration Options (SQL Server) and sys.configurations (Transact-SQL).
run_valuerun_value intint 配置选项的当前运行值( value_in_use中的值)。Currently running value of the configuration option (value in sys.configurations.value_in_use).

有关详细信息,请参阅Sys.databases (transact-sql)For more information, see sys.configurations (Transact-SQL).


使用sp_configure显示或更改服务器级设置。Use sp_configure to display or change server-level settings. 若要更改数据库级别设置,请使用 ALTER DATABASE。To change database-level settings, use ALTER DATABASE. 若要更改仅影响当前用户会话的设置,请使用 SET 语句。To change settings that affect only the current user session, use the SET statement.

SQL Server 大数据群集SQL Server Big Data Clusters

某些操作(包括配置服务器(实例级)设置或手动将数据库添加到可用性组)需要连接到 SQL Server 实例。Certain operations, including configuring server (instance level) settings, or manually adding a database to an availability group, require a connection to the SQL Server Instance. 某些操作(例如 sp_configureRESTORE DATABASE 或属于可用性组的数据库中的任何 DDL 命令)需要连接到 SQL Server 实例。Operations like sp_configure, RESTORE DATABASE, or any DDL command in a database belonging to an availability group require a connection to the SQL Server instance. 默认情况下,大数据群集不包含用于连接到实例的终结点。By default, a big data cluster does not include an endpoint that enables a connection to the instance. 必须手动公开此终结点。You must expose this endpoint manually.

有关说明,请参阅连接到主副本上的数据库For instructions, see Connect to databases on the primary replica.

更新运行的配置值Updating the Running Configuration Value

为某个选项指定新时,结果集将在 " config_value " 列中显示此值。When you specify a new value for an option, the result set shows this value in the config_value column. 此值最初与run_value列中的值不同,后者显示当前正在运行的配置值。This value initially differs from the value in the run_value column, which shows the currently running configuration value. 若要更新run_value列中的运行配置值,系统管理员必须运行 "重新配置" 或 "重新配置替代"。To update the running configuration value in the run_value column, the system administrator must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE.

RECONFIGURE 和 RECONFIGURE WITH OVERRIDE 对每个配置选项都有效。Both RECONFIGURE and RECONFIGURE WITH OVERRIDE work with every configuration option. 但是,基本 RECONFIGURE 语句会拒绝处于合理范围之外或可能导致选项冲突的任何选项值。However, the basic RECONFIGURE statement rejects any option value that is outside a reasonable range or that may cause conflicts among options. 例如,如果 "恢复间隔" 值大于60分钟或关联掩码值与关联 i/o 掩码值重叠,则重新配置会生成错误。For example, RECONFIGURE generates an error if the recovery interval value is larger than 60 minutes or if the affinity mask value overlaps with the affinity I/O mask value. 与此相反,RECONFIGURE WITH OVERRIDE 则接受具有正确数据类型的任何选项值,并使用指定的值强制进行重新配置。RECONFIGURE WITH OVERRIDE, in contrast, accepts any option value with the correct data type and forces reconfiguration with the specified value.


不合适的选项值会给服务器实例的配置造成不利影响。An inappropriate option value can adversely affect the configuration of the server instance. 请谨慎使用 RECONFIGURE WITH OVERRIDE。Use RECONFIGURE WITH OVERRIDE cautiously.

RECONFIGURE 语句可以动态更新某些选项,而其他选项的更新则需要停止服务器再重新启动才能实现。The RECONFIGURE statement updates some options dynamically; other options require a server stop and restart. 例如,"最小服务器内存" 和 "最大服务器内存" 服务器内存选项在 数据库引擎Database Engine中动态更新;因此,你可以更改它们而无需重新启动服务器。For example, the min server memory and max server memory server memory options are updated dynamically in the 数据库引擎Database Engine; therefore, you can change them without restarting the server. 与此相反,重新配置填充因子选项的运行值需要重新启动 数据库引擎Database EngineBy contrast, reconfiguring the running value of the fill factor option requires restarting the 数据库引擎Database Engine.

在配置选项上运行重新配置后,可以通过执行sp_configure "option_name" 来查看是否已动态更新了选项。After running RECONFIGURE on a configuration option, you can see whether the option has been updated dynamically by executing sp_configure'option_name'. 对于动态更新的选项, run_valueconfig_value列中的值应匹配。The values in the run_value and config_value columns should match for a dynamically updated option. 还可以查看sys.databases目录视图的is_dynamic列,查看哪些选项是动态的。You can also check to see which options are dynamic by looking at the is_dynamic column of the sys.configurations catalog view.

此更改也会写入 SQL Server 错误日志。The change is also written to the SQL Server error log.


如果某个选项的指定过高,则run_value列将反映 数据库引擎Database Engine 已默认为动态内存,而不是使用无效的设置。If a specified value is too high for an option, the run_value column reflects the fact that the 数据库引擎Database Engine has defaulted to dynamic memory rather than use a setting that is not valid.

有关详细信息,请参阅(重新配置 transact-sql)For more information, see RECONFIGURE (Transact-SQL).

高级选项Advanced Options

某些配置选项(如关联掩码恢复间隔)被指定为高级选项。Some configuration options, such as affinity mask and recovery interval, are designated as advanced options. 默认情况下,无法查看和更改这些选项。By default, these options are not available for viewing and changing. 若要使其可用,请将ShowAdvancedOptions配置选项设置为1。To make them available, set the ShowAdvancedOptions configuration option to 1.

有关配置选项及其设置的详细信息,请参阅服务器配置选项(SQL Server)For more information about the configuration options and their settings, see Server Configuration Options (SQL Server).


默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行sp_configure同时使用两个参数来更改配置选项或运行重新配置语句,您必须被授予 ALTER SETTINGS 服务器级别权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.


A.A. 列出高级配置选项Listing the advanced configuration options

以下示例显示如何设置并列出所有的配置选项。The following example shows how to set and list all configuration options. 先将 show advanced option 设置为 1,便可显示高级配置选项。Advanced configuration options are displayed by first setting show advanced option to 1. 更改该选项后,不带参数执行 sp_configure 将会显示全部配置选项。After this option has been changed, executing sp_configure with no parameters displays all configuration options.

USE master;  
EXEC sp_configure 'show advanced option', '1';  

以下是显示的消息:“配置选项 'show advanced options' 已从 0 改为 1。Here is the message: "Configuration option 'show advanced options' changed from 0 to 1. 请运行 RECONFIGURE 语句进行安装。”Run the RECONFIGURE statement to install."

运行 RECONFIGURE 并显示全部配置选项:Run RECONFIGURE and show all configuration options:

EXEC sp_configure;  

B.B. 更改配置选项Changing a configuration option

以下示例将系统 recovery interval 设置为 3 分钟。The following example sets the system recovery interval to 3 minutes.

USE master;  
EXEC sp_configure 'recovery interval', '3';  

示例:并行数据仓库Parallel Data WarehouseExamples: 并行数据仓库Parallel Data Warehouse

C.C. 列出所有可用的配置设置List all available configuration settings

以下示例显示如何列出所有的配置选项。The following example shows how to list all configuration options.

EXEC sp_configure;  

结果返回选项名称,后跟该选项的最小值和最大值。The result returns the option name followed by the minimum and maximum values for the option. Config_value是重新配置完成后 SQL 数据仓库SQL Data Warehouse 将使用的值。The config_value is the value that SQL 数据仓库SQL Data Warehouse will use when reconfiguration is complete. run_value 是当前正在使用的值。The run_value is the value that is currently being used. config_valuerun_value 通常是相同的,除非该值正在进行更改。The config_value and run_value are usually the same unless the value is in the process of being changed.

D.D. 列出一个配置名称的配置设置List the configuration settings for one configuration name

EXEC sp_configure @configname='hadoop connectivity';  

E.E. 设置 hadoop 连接Set Hadoop connectivity

除了运行 sp_configure 以外,设置 Hadoop 连接还需要执行一些步骤。Setting Hadoop connectivity requires a few more steps in addition to running sp_configure. 有关完整过程,请参阅CREATE EXTERNAL DATA SOURCE (transact-sql)For the full procedure, see CREATE EXTERNAL DATA SOURCE (Transact-SQL).

另请参阅See Also

SET 语句 (Transact-SQL) SET Statements (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.configurations (Transact-SQL) sys.configurations (Transact-SQL)
软件 NUMA (SQL Server)Soft-NUMA (SQL Server)