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

适用于: 是SQL Server是Azure SQL 数据库否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 ServerThis 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 数据库和 SQL Server 2019 或更高版本。Specifying a query plan handle is available in Azure SQL Database and SQL Server 2019 or higher.

MAXDOP = {<value> | PRIMARY } <value>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 在服务器级别设置“max degree of parallelism”。 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 }

启用或禁用查询优化修补程序,而无论数据库兼容性级别。Enables or disables query optimization hotfixes regardless of the compatibility level of the database. 默认值为 OFF,可禁用在为特定版本 (post-RTM) 引入可用度最高的兼容性级别后发布的查询优化修补程序 。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. 默认值为 ON 。The 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

此值可将所有支持的 DDL 操作提升为 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

此值可将所有支持的 DDL 操作提升为 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_stats 中。The execution statistics are reflected in sys.dm_exec_procedure_stats.

如果该选项为“开”,或通过 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.

如果该选项为“开”,或通过 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 数据库服务器的单个用户数据库中设置此选项。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 2012 (11.x)SQL Server 2012 (11.x) 版本或查询优化器修补程序启用 SQL Server 7.0 的默认查询优化器时,会成为查询提示和数据库范围配置设置之间的 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 数据库SQL DatabaseSQL Server 2016 (13.x)SQL Server 2016 (13.x) 数据库到 DACPACBACPAC 的导出无法导入到下级服务器。For example, an export to a DACPAC or a BACPAC from an SQL 数据库SQL 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 CONFIGURATION 的用法These examples demonstrate the use of ALTER DATABASE SCOPED CONFIGURATION

A.A. 授予权限Grant Permission

本示例为用户 Joe 授予执行 ALTER DATABASE SCOPED CONFIGURATION 所需的权限。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)

此示例将 ELEVATE_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