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 SCOPED CONFIGURATION (TRANSACT-SQL)For database-level configuration options, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). 若要配置软件 NUMA,请参阅SOFT-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.configurations目录视图。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,系统管理员必须使用 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE 更新正在运行的配置值。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 (中的值sys.configurations.value)。Value to which the configuration option was set using sp_configure (value in sys.configurations.value). 有关这些选项的详细信息,请参阅服务器配置选项(SQL Server) sys.configurations (-)For more information about these options, see Server Configuration Options (SQL Server) and sys.configurations (Transact-SQL).
run_valuerun_value intint 当前正在运行的配置选项的值 (中的值sys.configurations.value_in_use)。Currently running value of the configuration option (value in sys.configurations.value_in_use).

有关详细信息,请参阅sys.configurations (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列中,系统管理员必须运行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。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.

运行 RECONFIGURE 之后配置选项,可以看到是否已动态更新选项通过执行**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. 您还可以检查以查看哪些选项是动态的通过查看is_dynamic的列sys.configurations目录视图。You can also check to see which options are dynamic by looking at the is_dynamic column of the sys.configurations catalog view.

备注

如果指定有关的选项,过高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带两个参数以更改配置选项或运行 RECONFIGURE 语句,您必须被授予 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';  

下面是该消息:"配置选项 '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:

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

设置 Hadoop 连接需要除了运行 sp_configure 的几个步骤。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)