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

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否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-sqlFor 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 is varchar(35) ,預設值為 NULL。option_name is varchar(35), with a default of NULL. SQL Server Database EngineSQL Server Database Engine 會識別任何屬於組態名稱一部分的唯一字串。The SQL Server Database EngineSQL 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 ServerFor information about the available configuration options and their settings, see Server Configuration Options (SQL Server).

[ @configvalue = ] 'value' 是新的設定。[ @configvalue = ] 'value' Is the new configuration setting. valueint,預設值是 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.
minimumminimum intint 組態選項的最小值。Minimum value of the configuration option.
maximummaximum 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 Serversys.databases ( )transact-sqlFor more information about these options, see Server Configuration Options (SQL Server) and sys.configurations (Transact-SQL).
run_valuerun_value intint 目前正在執行設定選項的值(sys.databases 中的值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 EngineDatabase Engine中動態更新。因此,您可以變更它們,而不需要重新開機伺服器。For example, the min server memory and max server memory server memory options are updated dynamically in the Database EngineDatabase Engine; therefore, you can change them without restarting the server. 相反地,重新設定 [填滿因數] 選項的執行值時,需要重新開機 Database EngineDatabase EngineBy contrast, reconfiguring the running value of the fill factor option requires restarting the Database EngineDatabase 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_value ] 和 [ config_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 EngineDatabase Engine 已預設為動態記憶體,而不是使用不正確設定的事實。If a specified value is too high for an option, the run_value column reflects the fact that the Database EngineDatabase Engine has defaulted to dynamic memory rather than use a setting that is not valid.

如需詳細資訊,請參閱重新(設定)transact-sqlFor more information, see RECONFIGURE (Transact-SQL).

[進階選項]Advanced Options

某些設定選項,例如親和性遮罩和復原間隔,會被指定為 [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. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。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. 如需完整的程式,請參閱建立外部(資料來源 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)