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

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

您可以使用 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:

  • A= 進階選項,只能由有經驗的資料庫管理員或通過認證的 SQL ServerSQL Server 專業人員變更,而且必須將 [顯示進階選項選項] 設定為 1。A= Advanced options, which should be changed only by an experienced database administrator or a certified SQL ServerSQL Server professional, and which require setting show advanced options to 1.

  • RR = 需要重新啟動 Database EngineDatabase Engine的選項。RR = Options requiring a restart of the Database EngineDatabase Engine.

  • RP = 需要重新啟動 PolyBase 引擎的選項。RP = Options that require a restart of the PolyBase Engine.

  • SC = 自我設定的選項。SC = Self-configuring options.

組態選項Configuration option 最小值Minimum value 最大值Maximum value 預設Default
access check cache bucket count (A)access check cache bucket count (A) 00 1638416384 00
access check cache quota (A)access check cache quota (A) 00 21474836472147483647 00
ad hoc distributed queries (A)ad hoc distributed queries (A) 00 11 00
ADR cleaner retry timeout (分鐘)ADR cleaner retry timeout (min)

在 SQL Server 2019 中引進Introduced in SQL Server 2019
00 3276732767 1515
ADR Preallocation FactorADR Preallocation Factor

在 SQL Server 2019 中引進Introduced in SQL Server 2019
00 3276732767 44
affinity I/O mask (A、RR)affinity I/O mask (A, RR) -2147483648-2147483648 21474836472147483647 00
affinity mask (A)affinity mask (A) -2147483648-2147483648 21474836472147483647 00
affinity64 I/O mask (A,只能用於 64 位元版本的 SQL ServerSQL Server)affinity64 I/O mask (A, only available on 64-bit version of SQL ServerSQL Server) -2147483648-2147483648 21474836472147483647 00
affinity64 mask (A、RR),只能用於 64 位元版本的 SQL ServerSQL Serveraffinity64 mask (A, RR), only available on 64-bit version of SQL ServerSQL Server -2147483648-2147483648 21474836472147483647 00
Agent XPs (A)Agent XPs (A) 00 11 00

( SQL ServerSQL Server Agent 啟動時將變成 1。(Changes to 1 when SQL ServerSQL Server Agent is started. 如果在安裝期間將 SQL ServerSQL Server Agent 設定為自動啟動,預設值就是 0)。Default value is 0 if SQL ServerSQL Server Agent is set to automatic start during Setup.)
允許 polybase 匯出allow polybase export

第 1 課:建立 Windows Azure 儲存體物件適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
00 11 00
allow updates (已過時。allow updates (Obsolete. 請勿使用。Do not use. 否則會在重新設定期間導致錯誤)。Will cause an error during reconfigure.) 00 11 00
停用自動軟體 NUMAautomatic soft-NUMA disabled 00 11 00
備份總和檢查碼預設backup checksum default 00 11 00
backup compression defaultbackup compression default 00 11 00
已封鎖的處理序臨界值 (A)blocked process threshold (A) 55 8640086400 00
c2 audit mode (A、RR)c2 audit mode (A, RR) 00 11 00
clr enabledclr enabled 00 11 00
CLR 嚴格安全性 (A)clr strict security (A)
適用範圍:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更新版本適用範圍:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) and later.適用範圍:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更新版本適用範圍:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) and later.
00 11 00
column encryption enclave type (A、RR)column encryption enclave type (A, RR) 00 11 00
common criteria compliance enabled (A、RR)common criteria compliance enabled (A, RR) 00 11 00
自主資料庫驗證contained database authentication 00 11 00
平行處理原則的成本臨界值 (A)cost threshold for parallelism (A) 00 3276732767 55
cross db ownership chainingcross db ownership chaining 00 11 00
資料指標臨界值 (A)cursor threshold (A) -1-1 21474836472147483647 -1-1
Database Mail XPs (A)Database Mail XPs (A) 00 11 00
預設全文檢索語言 (A)default full-text language (A) 00 21474836472147483647 10331033
default languagedefault language 00 99999999 00
預設追蹤已啟用 (A)default trace enabled (A) 00 11 11
不允許來自觸發程序的結果 (A)disallow results from triggers (A) 00 11 00
EKM provider enabledEKM provider enabled 00 11 00
啟用外部指令碼 (SC) (RR)external scripts enabled (SC) (RR)

第 1 課:建立 Windows Azure 儲存體物件適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
00 11 00
filestream_access_levelfilestream_access_level 00 22 00
fill factor (A、RR)fill factor (A, RR) 00 100100 00
全文檢索耙梳頻寬 (最大) (A)ft crawl bandwidth (max)(A) 00 3276732767 100100
全文檢索耙梳頻寬 (最小) (A)ft crawl bandwidth (min)(A) 00 3276732767 00
全文檢索通知頻寬 (最大) (A)ft notify bandwidth (max)(A) 00 3276732767 100100
全文檢索通知頻寬 (最小) (A)ft notify bandwidth (min)(A) 00 3276732767 00
Hadoop 連線能力 (RP)hadoop connectivity (RP)

第 1 課:建立 Windows Azure 儲存體物件適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
00 77 00
in-doubt xact resolution (A)in-doubt xact resolution (A) 00 22 00
index create memory (A、SC)index create memory (A, SC) 704704 21474836472147483647 00
lightweight pooling (A、RR)lightweight pooling (A, RR) 00 11 00
locks (A、RR、SC)locks (A, RR, SC) 50005000 21474836472147483647 00
max degree of parallelism (A)max degree of parallelism (A) 00 3276732767 00
max full-text crawl range (A)max full-text crawl range (A) 00 256256 44
max server memory (A、SC)max server memory (A, SC) 1616 21474836472147483647 21474836472147483647
max text repl sizemax text repl size 00 21474836472147483647 6553665536
max worker threads (A)max worker threads (A) 128128 3276732767

1024 是 32 位元 SQL ServerSQL Server 的最大建議值,而 64 位元 SQL ServerSQL Server 則為 2048。1024 is the maximum recommended for 32-bit SQL ServerSQL Server, and 2048 for 64-bit SQL ServerSQL Server. 注意: SQL Server 2014 (12.x)SQL Server 2014 (12.x) 是 32 位元作業系統上可用的最後一個版本。Note: SQL Server 2014 (12.x)SQL Server 2014 (12.x) was the last version available on 32-bit operating system.
00

零表示自動設定 max worker threads 數目,而這個數目是根據處理器數目,透過用於 32 位元 SQL ServerSQL Server 的公式 (256 + ( <processors> -4) * 8) 來決定,而 64 位元 SQL ServerSQL Server 則為 (512 + ( <processors> - 4) * 8)。Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256 + (<processors> -4) * 8) for 32-bit SQL ServerSQL Server and (512 + (<processors> - 4) * 8) for 64-bit SQL ServerSQL Server. 注意: SQL Server 2014 (12.x)SQL Server 2014 (12.x) 是 32 位元作業系統上可用的最後一個版本。Note: SQL Server 2014 (12.x)SQL Server 2014 (12.x) was the last version available on 32-bit operating system.
media retention (A、RR)media retention (A, RR) 00 365365 00
min memory per query (A)min memory per query (A) 512512 21474836472147483647 10241024
min server memory (A、SC)min server memory (A, SC) 00 21474836472147483647 00
巢狀觸發程序nested triggers 00 11 11
network packet size (A)network packet size (A) 512512 3276732767 40964096
Ole Automation Procedures (A)Ole Automation Procedures (A) 00 11 00
open objects (A、RR,已過時)open objects (A, RR, obsolete) 00 21474836472147483647 00
optimize for ad hoc workloads (A)optimize for ad hoc workloads (A) 00 11 00
PH_timeout (A)PH_timeout (A) 11 36003600 6060
啟用 Polybase (RR)polybase enabled (RR)

適用範圍:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x)適用範圍:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x)
00 11 00
Polybase 網路加密polybase network encryption 00 11 11
precompute rank (A)precompute rank (A) 00 11 00
priority boost (A、RR)priority boost (A, RR) 00 11 00
query governor cost limit (A)query governor cost limit (A) 00 21474836472147483647 00
query wait (A)query wait (A) -1-1 21474836472147483647 -1-1
recovery interval (A、SC)recovery interval (A, SC) 00 3276732767 00
remote access (RR)remote access (RR) 00 11 11
remote admin connectionsremote admin connections 00 11 00
遠端資料封存remote data archive 00 11 00
remote login timeoutremote login timeout 00 21474836472147483647 1010
remote proc transremote proc trans 00 11 00
remote query timeoutremote query timeout 00 21474836472147483647 600600
Replication XPs Option (A)Replication XPs Option (A) 00 11 00
scan for startup procs (A、RR)scan for startup procs (A, RR) 00 11 00
server trigger recursionserver trigger recursion 00 11 11
set working set size (A、RR,已過時)set working set size (A, RR, obsolete) 00 11 00
show advanced optionsshow advanced options 00 11 00
SMO and DMO XPs (A)SMO and DMO XPs (A) 00 11 11
隱藏復原模式錯誤 (A)suppress recovery model errors (A)

是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance
00 11 00
經記憶體最佳化的 TempDB 中繼資料 (A)tempdb metadata memory-optimized (A)

適用範圍:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x)適用範圍:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x).適用範圍:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x)適用範圍:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x).
00 11 00
transform noise words (A)transform noise words (A) 00 11 00
two digit year cutoff (A)two digit year cutoff (A) 17531753 99999999 20492049
user connections (A、RR、SC)user connections (A, RR, SC) 00 3276732767 00
user optionsuser options 00 3276732767 00
xp_cmdshell (A)xp_cmdshell (A) 00 11 00

另請參閱See also

sp_configure (Transact-SQL) RECONFIGURE (Transact-SQL) DBCC FREEPROCCACHE (Transact-SQL)sp_configure (Transact-SQL) RECONFIGURE (Transact-SQL) DBCC FREEPROCCACHE (Transact-SQL)