伺服器組態選項 (SQL Server)Server Configuration Options (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否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)