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

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure SQL 数据仓库 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse 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

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

参数Arguments

[ @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
namename 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 (值为 "") 设置配置选项的值。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).

备注Remarks

使用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.

更新运行的配置值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).

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 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.

示例Examples

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;  
GO  
EXEC sp_configure 'show advanced option', '1';  

消息如下:"配置选项" 显示高级选项 "从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:

RECONFIGURE;  
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;  
GO  
EXEC sp_configure 'recovery interval', '3';  
RECONFIGURE WITH OVERRIDE;  

示例:并行数据仓库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

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