ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此陳述式可啟用數個個別資料庫層級的資料庫組態設定。This statement enables several database configuration settings at the individual database level. Azure SQL DatabaseAzure SQL Database 及從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始的 SQL ServerSQL Server 中都有提供此陳述式。This statement is available in Azure SQL DatabaseAzure SQL Database and in SQL ServerSQL Server beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x). 這些設定包括:Those settings are:

  • 清除程序快取。Clear procedure cache.
  • 針對主要資料庫將 MAXDOP 參數設定為任意值 (1、2、...,以最適用於該特定資料庫的值為準),並且針對所使用的所有次要資料庫 (例如,用於報表查詢) 設定不同的值 (例如 0)。Set the MAXDOP parameter to an arbitrary value (1,2, ...) for the primary database based on what works best for that particular database and set a different value (such as 0) for all secondary database used (such as for reporting queries).
  • 將與資料庫無關的查詢最佳化工具基數估計模型設定為相容性層級。Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  • 在資料庫層級啟用或停用參數探測。Enable or disable parameter sniffing at the database level.
  • 在資料庫層級啟用或停用查詢最佳化。Enable or disable query optimization hotfixes at the database level.
  • 在資料庫層級啟用或停用識別快取。Enable or disable the identity cache at the database level.
  • 允許或不允許在第一次編譯批次時,將已編譯的計劃虛設常式儲存在快取中。Enable or disable a compiled plan stub to be stored in cache when a batch is compiled for the first time.
  • 啟用或停用原生編譯 T-SQL 模組的執行統計資料收集。Enable or disable collection of execution statistics for natively compiled T-SQL modules.
  • 為支援 ONLINE = 語法的 DDL 陳述式啟用或停用預設為線上的選項。Enable or disable online by default options for DDL statements that support the ONLINE = syntax.
  • 為支援 RESUMABLE = 語法的 DDL 陳述式啟用或停用預設為可繼續的選項。Enable or disable resumable by default options for DDL statements that support the RESUMABLE = syntax.
  • 啟用或停用全域暫存資料表的自動卸除功能。Enable or disable the auto-drop functionality of global temporary tables.
  • 啟用或停用智慧查詢處理功能。Enable or disable Intelligent query processing features.
  • 啟用或停用輕量型查詢分析基礎結構Enable or disable the lightweight query profiling infrastructure.
  • 啟用或停用新的 String or binary data would be truncated 錯誤訊息。Enable or disable the new String or binary data would be truncated error message.
  • 啟用或停用 sys.dm_exec_query_plan_stats 中最後一個實際執行計畫的集合。Enable or disable collection of last actual execution plan in sys.dm_exec_query_plan_stats.

連結圖示 Transact-SQL 語法慣例link icon Transact-SQL Syntax Conventions

語法Syntax

ALTER DATABASE SCOPED CONFIGURATION
{
     {  [ FOR SECONDARY] SET <set_options>}
}
| CLEAR PROCEDURE_CACHE  [plan_handle]
| SET < set_options >
[;]

< set_options > ::=
{
    MAXDOP = { <value> | PRIMARY}
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
    | IDENTITY_CACHE = { ON | OFF }
    | INTERLEAVED_EXECUTION_TVF = { ON | OFF }
    | BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }
    | TSQL_SCALAR_UDF_INLINING = { ON | OFF }
    | ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
    | XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }
    | XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }
    | ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ON_ROWSTORE = { ON | OFF }
    | DEFERRED_COMPILATION_TV = { ON | OFF }
    | GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }
    | LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }
    | VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }
    | LAST_QUERY_PLAN_STATS = { ON | OFF }
}

引數Arguments

FOR SECONDARYFOR SECONDARY

指定次要資料庫的設定 (所有次要資料庫都必須具有相同的值)。Specifies the settings for secondary databases (all secondary databases must have the identical values).

CLEAR PROCEDURE_CACHE [plan_handle]CLEAR PROCEDURE_CACHE [plan_handle]

清除資料庫的程序 (計劃) 快取,並且可在主要端和次要端上執行。Clears the procedure (plan) cache for the database, and can be executed both on the primary and the secondaries.

指定查詢計劃控制代碼來將單一查詢計劃從計畫快取清除。Specify a query plan handle to clear a single query plan from the plan cache.

注意

指定查詢的計畫控制代碼適用於 Azure SQL Database 和 SQL Server 2019 或更高版本。Specifying a query plan handle is available in Azure SQL Database and SQL Server 2019 or higher.

MAXDOP = {<值> | PRIMARY } <值>MAXDOP = {<value> | PRIMARY } <value>

指定應該用於陳述式的預設 MAXDOP 設定。Specifies the default MAXDOP setting that should be used for statements. 0 是預設值,表示將改用伺服器組態。0 is the default value and indicates that the server configuration will be used instead. 資料庫範圍的 MAXDOP 會覆寫 (設定為 0 時除外) sp_configure 在伺服器層級設定的平行處理原則的最大程度The MAXDOP at the database scope overrides (unless it is set to 0) the max degree of parallelism set at the server level by sp_configure. 查詢提示仍然可以覆寫 DB 範圍的 MAXDOP 來調整需要不同設定的特定查詢。Query hints can still override the DB scoped MAXDOP in order to tune specific queries that need different setting. 所有這些設定都會受到針對「工作負載群組」設定的 MAXDOP 限制。All these settings are limited by the MAXDOP set for the Workload Group.

您可以使用 max degree of parallelism 選項來限制要用於平行計畫執行的處理器數目。You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. SQL Server 會針對查詢、索引資料定義語言 (DDL) 作業、平行插入、線上改變資料行、平行統計資料收集,以及靜態和索引鍵集驅動資料指標填入,考慮進行平行執行計劃。SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.

若要在執行個體層級設定此選項,請參閱設定 max degree of parallelism 伺服器組態選項To set this option at the instance level, see Configure the max degree of parallelism Server Configuration Option.

提示

若要在查詢層級完成此操作,請新增 MAXDOP 查詢提示To accomplish this at the query level, add the MAXDOP query hint.

PRIMARYPRIMARY

只能在資料庫位於主要端上時,針對次要端進行此設定,並且表示將採用針對主要端設定的組態。Can only be set for the secondaries, while the database in on the primary, and indicates that the configuration will be the one set for the primary. 如果主要端的組態發生變更,次要端上的值將會相應地變更,而無須明確地設定次要端值。If the configuration for the primary changes, the value on the secondaries will change accordingly without the need to set the secondaries value explicitly. PRIMARY 是次要端的預設設定。PRIMARY is the default setting for the secondaries.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

可讓您將查詢最佳化工具基數估計模型設定為 SQL Server 2012 和更舊版本,而不根據資料庫的相容性層級。Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. 預設值為 OFF,這會根據資料庫的相容性層級來設定查詢最佳化工具基數估計模型。The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database. 將 LEGACY_CARDINALITY_ESTIMATION 設為 [ON] 相當於啟用追蹤旗標 9481Setting LEGACY_CARDINALITY_ESTIMATION to ON is equivalent to enabling Trace Flag 9481.

提示

若要在查詢層級完成此操作,請新增 QUERYTRACEON 查詢提示To accomplish this at the query level, add the QUERYTRACEON query hint. SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 查詢提示,而不要使用追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT query hint instead of using the trace flag.

PRIMARYPRIMARY

只有在資料庫位於主要端上時,此值才會在次要端上有效,並且指定所有次要端上的查詢最佳化工具基數估計模型設定將採用針對主要端設定的值。This value is only valid on secondaries while the database in on the primary, and specifies that the query optimizer cardinality estimation model setting on all secondaries will be the value set for the primary. 如果主要端上查詢最佳化工具基數估計模型的組態發生變更,次要端上的值將會相應地變更。If the configuration on the primary for the query optimizer cardinality estimation model changes, the value on the secondaries will change accordingly. PRIMARY 是次要端的預設設定。PRIMARY is the default setting for the secondaries.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY}PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

啟用或停用參數探查Enables or disables parameter sniffing. 預設值是 ON。The default is ON. 將 PARAMETER_SNIFFING 設為 OFF 相當於啟用追蹤旗標 4136Setting PARAMETER_SNIFFING to OFF is equivalent to enabling Trace Flag 4136.

提示

若要在查詢層級完成此操作,請參閱 OPTIMIZE FOR UNKNOWN 查詢提示To accomplish this at the query level, see the OPTIMIZE FOR UNKNOWN query hint. SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,也可以使用 USE HINT 查詢提示Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, the USE HINT query hint is also available.

PRIMARYPRIMARY

只有在資料庫位於主要端上時,此值才會在次要端上有效,並且指定所有次要端上此設定的值將採用針對主要端設定的值。This value is only valid on secondaries while the database in on the primary, and specifies that the value for this setting on all secondaries will be the value set for the primary. 如果主要端上使用參數探查的組態發生變更,次要端上的值將會相應地變更,而無須明確地設定次要端值。If the configuration on the primary for using parameter sniffing changes, the value on the secondaries will change accordingly without the need to set the secondaries value explicitly. [PRIMARY] 是次要端的預設設定。PRIMARY is the default setting for the secondaries.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

啟用或停用查詢最佳化 Hotfix,而不管資料庫的相容性層級為何。Enables or disables query optimization hotfixes regardless of the compatibility level of the database. 預設值為 OFF,這會停用在針對特定版本導入最高可用相容性層級之後 (RTM 後) 發行的查詢最佳化 Hotfix。The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM). 將此值設定為 ON 相當於啟用追蹤旗標 4199Setting this to ON is equivalent to enabling Trace Flag 4199.

提示

若要在查詢層級完成此操作,請新增 QUERYTRACEON 查詢提示To accomplish this at the query level, add the QUERYTRACEON query hint. SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 查詢提示,而不要使用追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT query hint instead of using the trace flag.

PRIMARYPRIMARY

只有在資料庫位於主要端上時,此值才會在次要端上有效,並且指定所有次要端上此設定的值將採用針對主要端設定的值。This value is only valid on secondaries while the database in on the primary, and specifies that the value for this setting on all secondaries is the value set for the primary. 如果主要端的組態發生變更,次要端上的值將會相應地變更,而無須明確地設定次要端值。If the configuration for the primary changes, the value on the secondaries changes accordingly without the need to set the secondaries value explicitly. [PRIMARY] 是次要端的預設設定。PRIMARY is the default setting for the secondaries.

IDENTITY_CACHE = { ON | OFF }IDENTITY_CACHE = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

在資料庫層級啟用或停用識別快取。Enables or disables identity cache at the database level. 預設值為 ONThe default is ON. 識別快取可用來改善含有識別資料行之資料表上的 INSERT 效能。Identity caching is used to improve INSERT performance on tables with identity columns. 若要避免因伺服器意外重新啟動或容錯移轉至次要伺服器,而導致識別資料行值不連貫,請停用 IDENTITY_CACHE 選項。To avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. 此選項類似於現有的追蹤旗標 272差異在於此選項可以在資料庫層級設定,而不僅止於在伺服器層級設定。This option is similar to the existing Trace Flag 272, except that it can be set at the database level rather than only at the server level.

注意

只能針對 PRIMARY 設定此選項。This option can only be set for the PRIMARY. 如需詳細資訊,請參閱識別資料行For more information, see identity columns.

INTERLEAVED_EXECUTION_TVF = { ON | OFF }INTERLEAVED_EXECUTION_TVF = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

可讓您在資料庫或陳述式範圍啟用或停用多重陳述式資料表值函式的交錯執行,同時仍保有 140 (含) 以上的資料庫相容性層級。Allows you to enable or disable Interleaved execution for multi-statement table valued functions at the database or statement scope while still maintaining database compatibility level 140 and higher. 交錯執行是 Azure SQL DatabaseAzure SQL Database 中自適性查詢處理的部分功能。Interleaved execution is a feature that is part of Adaptive query processing in Azure SQL DatabaseAzure SQL Database. 如需詳細資訊,請參閱智慧查詢處理For more information, please refer to Intelligent query processing.

注意

針對資料庫相容性層級 130 或更低,這個資料庫範圍設定沒有任何作用。For database compatibility level 130 or lower, this database scoped configuration has no effect.

僅限 SQL Server 2017 (14.x),選項 INTERLEAVED_EXECUTION_TVF 舊稱為 DISABLE_INTERLEAVED_EXECUTION_TVF。In SQL Server 2017 (14.x) only, the option INTERLEAVED_EXECUTION_TVF had the older name of DISABLE_INTERLEAVED_EXECUTION_TVF.

BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

可讓您在資料庫範圍啟用或停用批次模式記憶體授與回饋,同時仍保有 140 (含) 以上的資料庫相容性層級。Allows you to enable or disable batch mode memory grant feedback at the database scope while still maintaining database compatibility level 140 and higher. 批次模式記憶體授與回饋是 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 推出的智慧查詢處理其中一項功能。Batch mode memory grant feedback a feature that is part of Intelligent query processing introduced in SQL Server 2017 (14.x)SQL Server 2017 (14.x).

注意

針對資料庫相容性層級 130 或更低,這個資料庫範圍設定沒有任何作用。For database compatibility level 130 or lower, this database scoped configuration has no effect.

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF}BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

可讓您在資料庫範圍啟用或停用批次模式自適性聯結,同時仍保有 140 (含) 以上的資料庫相容性層級。Allows you to enable or disable batch mode adaptive joins at the database scope while still maintaining database compatibility level 140 and higher. 批次模式自適性聯結是 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 推出的智慧查詢處理其中一項功能。Batch mode adaptive joins is a feature that is part of Intelligent query processing introduced in SQL Server 2017 (14.x)SQL Server 2017 (14.x).

注意

針對資料庫相容性層級 130 或更低,這個資料庫範圍設定沒有任何作用。For database compatibility level 130 or lower, this database scoped configuration has no effect.

TSQL_SCALAR_UDF_INLINING = { ON | OFF }TSQL_SCALAR_UDF_INLINING = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL Database (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database (feature is in public preview)

可讓您在資料庫範圍啟用或停用 T-SQL 純量 UDF 內嵌,同時仍保有 150 (含) 以上的資料庫相容性層級。Allows you to enable or disable T-SQL Scalar UDF inlining at the database scope while still maintaining database compatibility level 150 and higher. T-SQL 純量 UDF 內嵌是智慧查詢處理功能系列的一部分。T-SQL Scalar UDF inlining is part of the Intelligent query processing feature family.

注意

針對資料庫相容性層級 140 (含) 以下,這個資料庫範圍設定沒有任何作用。For database compatibility level 140 or lower, this database scoped configuration has no effect.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

適用於Azure SQL DatabaseAzure SQL Database (功能處於公開預覽階段)Applies to: Azure SQL DatabaseAzure SQL Database (feature is in public preview)

可讓您選取選項,讓引擎自動將支援的作業提升至線上。Allows you to select options to cause the engine to automatically elevate supported operations to online. 預設為 OFF,這表示除非在陳述式中指定,否則不會將作業提升至線上。The default is OFF, which means operations will not be elevated to online unless specified in the statement. sys.database_scoped_configurations 會反映 ELEVATE_ONLINE 目前的值。sys.database_scoped_configurations reflects the current value of ELEVATE_ONLINE. 這些選項將僅適用於線上支援的作業。These options will only apply to operations that are supported for online.

FAIL_UNSUPPORTEDFAIL_UNSUPPORTED

此值會將所有支援的 DLL 作業提升至 ONLINE。This value elevates all supported DDL operations to ONLINE. 不支援線上執行的作業將會失敗並擲回警告。Operations that do not support online execution will fail and throw a warning.

WHEN_SUPPORTEDWHEN_SUPPORTED

此值會提升支援 ONLINE 的作業。This value elevates operations that support ONLINE. 不支援線上的作業將離線執行。Operations that do not support online will be run offline.

注意

您可以在指定 ONLINE 選項的情形下提交陳述式,進而覆寫預設設定。You can override the default setting by submitting a statement with the ONLINE option specified.

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL Database (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database (feature is in public preview)

可讓您選取選項,讓引擎自動將支援的作業提升至可繼續。Allows you to select options to cause the engine to automatically elevate supported operations to resumable. 預設為 OFF,這表示除非在陳述式中指定,否則不會將作業提升至可繼續。The default is OFF, which means operations are not be elevated to resumable unless specified in the statement. sys.database_scoped_configurations 會反映 ELEVATE_RESUMABLE 目前的值。sys.database_scoped_configurations reflects the current value of ELEVATE_RESUMABLE. 這些選項僅適用於可繼續支援的作業。These options only apply to operations that are supported for resumable.

FAIL_UNSUPPORTEDFAIL_UNSUPPORTED

此值會將所有支援的 DLL 作業提升至 RESUMABLE。This value elevates all supported DDL operations to RESUMABLE. 不支援可繼續執行的作業會失敗並擲回警告。Operations that do not support resumable execution fail and throw a warning.

WHEN_SUPPORTEDWHEN_SUPPORTED

此值會提升支援 RESUMABLE 的作業。This value elevates operations that support RESUMABLE. 不支援可繼續的作業會在非可繼續的情況下執行。Operations that do not support resumable are run non-resumably.

注意

您可以在指定 RESUMABLE 選項的情形下提交陳述式,進而覆寫預設設定。You can override the default setting by submitting a statement with the RESUMABLE option specified.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database

允許或不允許在第一次編譯批次時,將已編譯的計劃虛設常式儲存在快取中。Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. 預設值為 OFF。The default is OFF. 針對資料庫啟用 OPTIMIZE_FOR_AD_HOC_WORKLOADS 資料庫範圍組態之後,在第一次編譯批次時,就會將已編譯的計劃虛設常式儲存在快取中。Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time. 與完整的已編譯計劃大小相比,計劃虛設常式的記憶體耗用量較少。Plan stubs have a smaller memory footprint compared to the size of the full compiled plan. 如果再次編譯或執行某個批次,就會移除已編譯的計劃虛設常式,並以完整的已編譯計劃取代。If a batch is compiled or executed again, the compiled plan stub will be removed and replaced with a full compiled plan.

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database

在目前的資料庫上啟用或停用原生編譯 T-SQL 模組的模組層級執行統計資料收集。Enables or disables collection of execution statistics at the module-level for natively compiled T-SQL modules in the current database. 預設值為 OFF。The default is OFF. 執行統計資料會反映在 sys.dm_exec_procedure_statsThe execution statistics are reflected in sys.dm_exec_procedure_stats.

如果這個選項是 ON,或已透過 sp_xtp_control_proc_exec_stats 啟用統計資料收集,則會收集原生編譯 T-SQL 模組的模組層級執行統計資料。Module-level execution statistics for natively compiled T-SQL modules are collected if either this option is ON, or if statistics collection is enabled through sp_xtp_control_proc_exec_stats.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database

在目前的資料庫上啟用或停用原生編譯 T-SQL 模組的陳述式層級執行統計資料收集。Enables or disables collection of execution statistics at the statement-level for natively compiled T-SQL modules in the current database. 預設值為 OFF。The default is OFF. 執行統計資料會反映在 sys.dm_exec_query_stats查詢存放區The execution statistics are reflected in sys.dm_exec_query_stats and in Query Store.

如果這個選項是 ON,或已透過 sp_xtp_control_query_exec_stats 啟用統計資料收集,則會收集原生編譯 T-SQL 模組的陳述式層級執行統計資料。Statement-level execution statistics for natively compiled T-SQL modules are collected if either this option is ON, or if statistics collection is enabled through sp_xtp_control_query_exec_stats.

如需原生編譯 Transact-SQLTransact-SQL 模組效能監控的詳細資訊,請參閱監視原生編譯預存程序的效能For more information about performance monitoring of natively compiled Transact-SQLTransact-SQL modules see Monitoring Performance of Natively Compiled Stored Procedures.

ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL Database (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database (feature is in public preview)

可讓您在資料庫範圍啟用或停用資料列模式記憶體授與回饋,同時仍保有 150 (含) 以上的資料庫相容性層級。Allows you to enable or disable row mode memory grant feedback at the database scope while still maintaining database compatibility level 150 and higher. 資料列模式記憶體授與回饋是 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 推出的智慧查詢處理其中一項功能 (SQL Server 2019SQL Server 2019Azure SQL DatabaseAzure SQL Database 支援資料列模式)。Row mode memory grant feedback a feature that is part of Intelligent query processing introduced in SQL Server 2017 (14.x)SQL Server 2017 (14.x) (row mode is supported in SQL Server 2019SQL Server 2019 and Azure SQL DatabaseAzure SQL Database).

注意

針對資料庫相容性層級 140 (含) 以下,這個資料庫範圍設定沒有任何作用。For database compatibility level 140 or lower, this database scoped configuration has no effect.

BATCH_MODE_ON_ROWSTORE = { ON | OFF}BATCH_MODE_ON_ROWSTORE = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL Database (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database (feature is in public preview)

可讓您在資料庫範圍啟用或停用資料列存放區上的批次模式,同時仍保有 150 (含) 以上的資料庫相容性層級。Allows you to enable or disable batch mode on rowstore at the database scope while still maintaining database compatibility level 150 and higher. 資料列存放區上的批次模式是智慧查詢處理功能系列的其中一項功能。Batch mode on rowstore is a feature that is part of Intelligent query processing feature family.

注意

針對資料庫相容性層級 140 (含) 以下,這個資料庫範圍設定沒有任何作用。For database compatibility level 140 or lower, this database scoped configuration has no effect.

DEFERRED_COMPILATION_TV = { ON | OFF}DEFERRED_COMPILATION_TV = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL Database (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database (feature is in public preview)

可讓您在資料庫範圍啟用或停用資料表變數延遲編譯,同時仍保有 150 (含) 以上的資料庫相容性層級。Allows you to enable or disable table variable deferred compilation at the database scope while still maintaining database compatibility level 150 and higher. 資料表變數延遲編譯是智慧查詢處理功能系列的其中一項功能。Table variable deferred compilation is a feature that is part of Intelligent query processing feature family.

注意

針對資料庫相容性層級 140 (含) 以下,這個資料庫範圍設定沒有任何作用。For database compatibility level 140 or lower, this database scoped configuration has no effect.

GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }

適用於Azure SQL DatabaseAzure SQL Database (功能處於公開預覽階段)Applies to: Azure SQL DatabaseAzure SQL Database (feature is in public preview)

允許設定全域暫存資料表的自動卸除功能。Allows setting the auto drop functionality for global temporary tables. 預設值為 [ON],表示全域暫存資料表會在沒有任何工作階段使用時自動卸除。The default is ON, which means that the global temporary tables are automatically dropped when not in use by any session. 當設為 [OFF] 時,將需要使用 DROP TABLE 陳述式,才能明確卸除全域暫存資料表,或是等到伺服器重新啟動時再自動卸除。When set to OFF, global temporary tables need to be explicitly dropped using a DROP TABLE statement or will be automatically dropped on server restart.

  • 使用 Azure SQL DatabaseAzure SQL Database 單一資料庫和彈性集區,此選項可以在 SQL Database 伺服器的個別使用者資料庫中進行設定。With Azure SQL DatabaseAzure SQL Database single databases and elastic pools, this option can be set in the individual user databases of the SQL Database server.
  • SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database 受控執行個體中,此選項會在 TempDB 中設定,且個別使用者資料庫的設定不會有任何效果。In SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database managed instance, this option is set in TempDB and the setting of the individual user databases has no effect.

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF}LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database

可讓您啟用或停用輕量型查詢分析基礎結構Allows you to enable or disable the lightweight query profiling infrastructure. 輕量型查詢分析基礎結構 (LWP) 提供比標準分析機制更具效率的查詢效能資料,預設會予以啟用。The lightweight query profiling infrastructure (LWP) provides query performance data more efficiently than standard profiling mechanisms and is enabled by default.

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF}VERBOSE_TRUNCATION_WARNINGS = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) and Azure SQL DatabaseAzure SQL Database

可讓您啟用或停用新的 String or binary data would be truncated 錯誤訊息。Allows you to enable or disable the new String or binary data would be truncated error message. SQL Server 2019SQL Server 2019 針對此案例引進了更加特定的新錯誤訊息 (2628):introduces a new, more specific error message (2628) for this scenario:

String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.

在資料庫相容性層級 150 下設為 ON 時,截斷錯誤會引發新的錯誤訊息 2628 以提供更多內容,並簡化疑難排解程序。When set to ON under database compatibility level 150, truncation errors raise the new error message 2628 to provide more context and simplify the troubleshooting process.

在資料庫相容性層級 150 下設為 OFF 時,截斷錯誤會引發先前的錯誤訊息 8152。When set to OFF under database compatibility level 150, truncation errors raise the previous error message 8152.

在資料庫相容性層級 140 或更低層級下,錯誤訊息 2628 會保有必須啟用追蹤旗標 460 的選擇加入錯誤訊息,且此資料庫範圍的組態沒有任何作用。For database compatibility level 140 or lower, error message 2628 remains an opt-in error message that requires trace flag 460 to be enabled, and this database scoped configuration has no effect.

LAST_QUERY_PLAN_STATS = { ON | OFF}LAST_QUERY_PLAN_STATS = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019SQL Server 2019) (feature is in public preview)

可讓您啟用或停用 sys.dm_exec_query_plan_stats 中最後一個查詢計畫統計資料 (相當於實際執行計畫) 的集合。Allows you to enable or disable colection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.

權限Permissions

資料庫上需要 ALTER ANY DATABASE SCOPE CONFIGURATIONRequires ALTER ANY DATABASE SCOPE CONFIGURATION on the database. 可由在資料庫上具有 CONTROL 權限的使用者來授與此權限。This permission can be granted by a user with CONTROL permission on a database.

一般備註General Remarks

雖然您可以設定讓次要資料庫擁有與其主要資料庫不同的範圍組態設定,但所有次要資料庫都會使用相同的組態。While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases use the same configuration. 無法為個別次要資料庫設定不同的設定。Different settings cannot be configured for individual secondaries.

執行此陳述式會清除目前資料庫中的程序快取,這意謂著所有查詢都必須重新編譯。Executing this statement clears the procedure cache in the current database, which means that all queries have to recompile.

針對有 3 部分名稱的查詢,會採用查詢的目前資料庫連線設定,而不會採用在目前資料庫內容中編譯 SQL 模組 (例如程序、函式及觸發程序) 的設定,因此會使用其所在資料庫的選項。For 3-part name queries, the settings for the current database connection for the query are honored, other than for SQL modules (such as procedures, functions, and triggers) that are compiled in the current database context and therefore uses the options of the database in which they reside.

ALTER_DATABASE_SCOPED_CONFIGURATION 事件會以 DDL 事件的形式新增,可用來引發 DDL 觸發程序,且是 ALTER_DATABASE_EVENTS 觸發程序群組的子觸發程序。The ALTER_DATABASE_SCOPED_CONFIGURATION event is added as a DDL event that can be used to fire a DDL trigger, and is a child of the ALTER_DATABASE_EVENTS trigger group.

資料庫範圍組態設定會伴隨資料庫,這表示當還原或附加特定資料庫時,會保留現有的組態設定。Database scoped configuration settings will be carried over with the database, which means that when a given database is restored or attached, the existing configuration settings remain.

限制事項Limitations and Restrictions

MAXDOPMAXDOP

細微的設定可以覆寫全域設定,而資源管理員則可以設定所有其他 MAXDOP 設定的限制。The granular settings can override the global ones and that resource governor can cap all other MAXDOP settings. 以下是 MAXDOP 設定的邏輯:The logic for MAXDOP setting is the following:

  • 查詢提示會覆寫 sp_configure 和資料庫範圍設定。Query hint overrides both the sp_configure and the database scoped configuration. 如果已針對工作負載群組設定資源群組 MAXDOP:If the resource group MAXDOP is set for the workload group:

    • 如果將查詢提示設定為零 (0),資源管理員設定就會覆寫該提示。If the query hint is set to zero (0), it is overridden by the resource governor setting.

    • 如果查詢提示不是零 (0),就會以資源管理員設定作為其限制。If the query hint is not zero (0), it is capped by the resource governor setting.

  • 除非有查詢提示,否則資料庫範圍設定 (值為零時除外) 會覆寫 sp_configure 設定,並以資源管理員設定作為其限制。The database scoped configuration (unless it's zero) overrides the sp_configure setting unless there is a query hint and is capped by the resource governor setting.

  • 資源管理員設定會覆寫 sp_configure 設定。The sp_configure setting is overridden by the resource governor setting.

QUERY_OPTIMIZER_HOTFIXESQUERY_OPTIMIZER_HOTFIXES

使用 QUERYTRACEON 提示來啟用 SQL Server 7.0 到 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 版本的預設查詢最佳化工具或查詢最佳化工具 Hotfix 時,在查詢提示與資料庫範圍組態設定之間會是 OR 條件;也就是說,如果啟用兩者其中之一,就會套用資料庫範圍設定選項。When QUERYTRACEON hint is used to enable the default query optimizer of SQL Server 7.0 through SQL Server 2012 (11.x)SQL Server 2012 (11.x) versions or query optimizer hotfixes, it would be an OR condition between the query hint and the database scoped configuration setting, meaning if either is enabled, the database scoped configurations apply.

異地災害復原Geo DR

可讀取次要資料庫 (Always On 可用性群組和 Azure SQL DatabaseAzure SQL Database 異地複寫資料庫),會透過檢查資料庫的狀態來使用次要值。Readable secondary databases (Always On Availability Groups and Azure SQL DatabaseAzure SQL Database geo-replicated databases), use the secondary value by checking the state of the database. 儘管重新編譯並不會發生在容錯移轉時,而且就技術而言,新的主要資料庫會有使用次要設定的查詢,但主要資料庫與次要資料庫之間的設定只有在工作負載不同時會有差異,因此快取的查詢會使用最佳設定,而新查詢則會挑選適合它們的新設定。Even though recompile does not occur on failover and technically the new primary has queries that are using the secondary settings, the idea is that the setting between primary and secondary only vary when the workload is different and therefore the cached queries are using the optimal settings, whereas new queries pick the new settings that are appropriate for them.

DacFxDacFx

因為 ALTER DATABASE SCOPED CONFIGURATIONAzure SQL DatabaseAzure SQL DatabaseSQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始) 中會影響資料庫結構描述的新功能,所以匯出的結構描述 (不論有無資料) 不能匯入至較舊的 SQL ServerSQL Server 版本,例如 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)Since ALTER DATABASE SCOPED CONFIGURATION is a new feature in Azure SQL DatabaseAzure SQL Database and SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) that affects the database schema, exports of the schema (with or without data) are not able to be imported into an older version of SQL ServerSQL Server, such as SQL Server 2012 (11.x)SQL Server 2012 (11.x) or SQL Server 2014 (12.x)SQL Server 2014 (12.x). 例如,從使用此新功能的 SQL DatabaseSQL DatabaseSQL Server 2016 (13.x)SQL Server 2016 (13.x) 資料庫匯出至 DACPACBACPAC 的匯出項目,將無法匯入至舊版伺服器。For example, an export to a DACPAC or a BACPAC from an SQL DatabaseSQL Database or SQL Server 2016 (13.x)SQL Server 2016 (13.x) database that used this new feature would not be able to be imported into a down-level server.

ELEVATE_ONLINEELEVATE_ONLINE

此選項僅適用於支援 WITH (ONLINE = <syntax>) 的 DDL 陳述式。This option only applies to DDL statements that support the WITH (ONLINE = <syntax>). 不會影響 XML 索引。XML indexes are not affected.

ELEVATE_RESUMABLEELEVATE_RESUMABLE

此選項僅適用於支援 WITH (RESUMABLE = <syntax>) 的 DDL 陳述式。This option only applies to DDL statements that support the WITH (RESUMABLE = <syntax>). 不會影響 XML 索引。XML indexes are not affected.

中繼資料Metadata

sys.database_scoped_configurations (Transact-SQL) 系統檢視會提供有關資料庫內範圍組態的資訊。The sys.database_scoped_configurations (Transact-SQL) system view provides information about scoped configurations within a database. 資料庫範圍組態選項只會出現在 sys.database_scoped_configurations 中,因為它們會覆寫伺服器層級預設設定。Database-scoped configuration options only show up in sys.database_scoped_configurations as they are overrides to server-wide default settings. sys.configurations (Transact-SQL) 系統檢視只會顯示伺服器層級設定。The sys.configurations (Transact-SQL) system view only shows server-wide settings.

範例Examples

下列範例示範如何使用 ALTER DATABASE SCOPED CONFIGURATIONThese examples demonstrate the use of ALTER DATABASE SCOPED CONFIGURATION

A.A. 授與權限Grant Permission

此範例會將執行 ALTER DATABASE SCOPED CONFIGURATION 所需的權限授與使用者 Joe。This example grant permission required to execute ALTER DATABASE SCOPED CONFIGURATION to user Joe.

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B.B. 設定 MAXDOPSet MAXDOP

此範例會在異地複寫案例中,針對主要資料庫設定 MAXDOP = 1,並針對次要資料庫設定 MAXDOP = 4。This example sets MAXDOP = 1 for a primary database and MAXDOP = 4 for a secondary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 4 ;

此範例會在異地複寫案例中,將次要資料庫及其主要資料庫的 MAXDOP 設定為相同。This example sets MAXDOP for a secondary database to be the same as it is set for its primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;

C.C. 設定 LEGACY_CARDINALITY_ESTIMATIONSet LEGACY_CARDINALITY_ESTIMATION

此範例會在異地複寫案例中,將次要資料庫的 LEGACY_CARDINALITY_ESTIMATION 設定為 ON。This example sets LEGACY_CARDINALITY_ESTIMATION to ON for a secondary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;

此範例會在異地複寫案例中,將次要資料庫及其主要資料庫的 LEGACY_CARDINALITY_ESTIMATION 設定為相同。This example sets LEGACY_CARDINALITY_ESTIMATION for a secondary database as it is for its primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;

D.D. 設定 PARAMETER_SNIFFINGSet PARAMETER_SNIFFING

此範例會在異地複寫案例中,將主要資料庫的 PARAMETER_SNIFFING 設定為 OFF。This example sets PARAMETER_SNIFFING to OFF for a primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;

此範例會在異地複寫案例中,將次要資料庫的 PARAMETER_SNIFFING 設定為 OFF。This example sets PARAMETER_SNIFFING to OFF for a secondary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;

此範例會在異地複寫案例中,將次要資料庫及主要資料庫的 PARAMETER_SNIFFING 設定為相同。This example sets PARAMETER_SNIFFING for secondary database as it is on primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;

E.E. 設定 QUERY_OPTIMIZER_HOTFIXESSet QUERY_OPTIMIZER_HOTFIXES

在異地複寫案例中,將主要資料庫的 QUERY_OPTIMIZER_HOTFIXES 設定為 ON。Set QUERY_OPTIMIZER_HOTFIXES to ON for a primary database in a geo-replication scenario.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

F.F. 清除程序快取Clear Procedure Cache

此範例會清除程序快取 (可能僅適用於主要資料庫)。This example clears the procedure cache (possible only for a primary database).

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

G.G. 設定 IDENTITY_CACHESet IDENTITY_CACHE

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL Database (功能目前為公開預覽版)Applies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database (feature is in public preview)

此範例會停用識別快取。This example disables the identity cache.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF ;

H.H. 設定 OPTIMIZE_FOR_AD_HOC_WORKLOADSSet OPTIMIZE_FOR_AD_HOC_WORKLOADS

適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database

此範例會允許在第一次編譯批次時,將已編譯的計劃虛設常式儲存在快取中。This example enables a compiled plan stub to be stored in cache when a batch is compiled for the first time.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I.I. 設定 ELEVATE_ONLINESet ELEVATE_ONLINE

適用於Azure SQL DatabaseAzure SQL Database (功能處於公開預覽階段)Applies to: Azure SQL DatabaseAzure SQL Database (feature is in public preview)

此範例會將 ELEVATE_ONLINE 設定為 FAIL_UNSUPPORTED。This example sets ELEVATE_ONLINE to FAIL_UNSUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED ;

J.J. 設定 ELEVATE_RESUMABLESet ELEVATE_RESUMABLE

適用於Azure SQL DatabaseAzure SQL DatabaseSQL Server 2019SQL Server 2019 (功能目前為公開預覽版)Applies to: Azure SQL DatabaseAzure SQL Database and SQL Server 2019SQL Server 2019 (feature is in public preview)

此範例會將 ELEVEATE_RESUMABLE 設定為 WHEN_SUPPORTED。This example sets ELEVATE_RESUMABLE to WHEN_SUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

K.K. 從計畫快取清除查詢計劃Clear a query plan from the plan cache

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

此範例會將特定計劃從程序快取清除This example clears a specific plan from the procedure cache

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

其他資源Additional Resources

MAXDOP 資源MAXDOP Resources

LEGACY_CARDINALITY_ESTIMATION 資源LEGACY_CARDINALITY_ESTIMATION Resources

PARAMETER_SNIFFING 資源PARAMETER_SNIFFING Resources

QUERY_OPTIMIZER_HOTFIXES 資源QUERY_OPTIMIZER_HOTFIXES Resources

ELEVATE_ONLINE 資源ELEVATE_ONLINE Resources

線上索引作業的指導方針Guidelines for Online Index Operations

ELEVATE_RESUMABLE 資源ELEVATE_RESUMABLE Resources

線上索引作業的指導方針Guidelines for Online Index Operations

詳細資訊More information