适用对象:yesSQL Server(从 2016 版开始)yesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库
此语句在单个数据库级别启用多个数据库配置设置。 此语句可用于 Azure SQL Database 以及 SQL Server 2016 (13.x) 和更高版本的 SQL Server。 这些设置包括:
- 清除过程缓存。
- 根据最适合特定主数据库的情况,将 MAXDOP 参数设置为该数据库的任意值(1、2、…),为使用的所有辅助数据库(例如,针对报告查询)设置不同的值(例如 0)。
- 设置独立于数据库兼容级别的查询优化器基数估计模型。
- 在数据库级别启用或禁用参数探查。
- 在数据库级别启用或禁用查询优化修补程序。
- 在数据库级别启用或禁用标识缓存。
- 在第一次编译批处理时启用或禁用要存储在缓存中的已编译计划存根。
- 启用或禁用对本机编译的 T-SQL 模块的执行统计信息收集。
- 对支持 ONLINE= 语法的 DDL 语句启用或禁用“默认联机”选项。
- 对支持 RESUMABLE= 语法的 DDL 语句启用或禁用“默认可恢复”选项。
- 启用或禁用全局临时表的自动删除功能
链接图标 Transact-SQL 语法约定
语法
ALTER DATABASE SCOPED CONFIGURATION
{
{ [ FOR SECONDARY] SET <set_options>}
}
| CLEAR PROCEDURE_CACHE
| 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 }
| OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
| XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }
| XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }
| ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
| ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
| GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }
}
参数
FOR SECONDARY
指定辅助数据库的设置(所有辅助数据库必须具有相同的值)。
MAXDOP = {<value> | PRIMARY } <value>
指定应用于该语句的默认 MAXDOP 设置。 0 是默认值,表示将改用服务器配置。 数据库范围的 MAXDOP 会替代(除非设置为 0)通过 sp_configure 在服务器级别设置“max degree of parallelism”。 查询提示仍然可以替代 DB 作用域内 MAXDOP,以调整需要不同设置的特定查询。 所有这些设置都受为工作负荷组设置的 MAXDOP 限制。
您可以使用 max degree of parallelism 选项来限制并行计划执行时所用的处理器数。 SQL Server 考虑为查询、索引数据定义语言 (DDL) 操作、并行插入、联机更改列、并行统计信息集合以及静态的和由键集驱动的游标填充实施并行执行计划。
要在实例级别设置此选项,请参阅配置 max degree of parallelism 服务器配置选项。
Tip: 要在查询级别完成此操作,请添加 MAXDOP 查询提示。
PRIMARY
仅可为辅助数据库(该数据库位于主数据库上)设置,表示其配置是为主数据库设置的配置。 如果主数据库的配置更改,辅助数据库上的值也会相应地更改,不需要再显式设置辅助数据库值。 PRIMARY 是辅助数据库的默认设置。
LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
可用于独立于数据库兼容性级别将查询优化器基数估计模型设置为 SQL Server 2012 或更低版本。 默认值为 OFF,可根据数据库兼容性级别设置查询优化器基数估计模型。 将 LEGACY_CARDINALITY_ESTIMATION 设置为 ON 等效于启用跟踪标志 9481。
Tip: 要在查询级别完成此操作,请添加 QUERYTRACEON 查询提示。 从 SQL Server 2016 (13.x) SP1 开始,要在查询级别完成此操作,请添加 USE HINT 查询提示,而不是使用跟踪标志。
PRIMARY
此值仅对辅助数据库(该数据库位于主数据库上)有效,指定所有辅助数据库上的查询优化器基数估计模型设置都是为主数据库设置的值。 如果主数据库上查询优化器基数估计模型的配置发生更改,则辅助数据上的值也会相应地更改。 PRIMARY 是辅助数据库的默认设置。
PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
启用或禁用参数截取。 默认值为 ON。 将 PARAMETER_SNIFFING 设置为 ON 等效于启用跟踪标志 4136。
Tip: 要在查询级别完成此操作,请参阅 OPTIMIZE FOR UNKNOWN 查询提示。 从 SQL Server 2016 (13.x) SP1 开始,要在查询级别完成此操作,也可使用 USE HINT 查询提示。
PRIMARY
此值仅对辅助数据库(该数据库位于主数据库上)有效,指定所有辅助数据库上此设置的值都是为主数据库设置的值。 如果主数据库上用于使用参数截取的配置更改,则辅助数据库上的值也会相应地更改,不需要再显式设置辅助数据库值。 PRIMARY 是辅助数据库的默认设置。
QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }
启用或禁用查询优化修补程序,而无论数据库兼容性级别。 默认值为 OFF,可禁用在为特定版本 (post-RTM) 引入可用度最高的兼容性级别后发布的查询优化修补程序。 将此值设置为 ON 等效于启用跟踪标志 4199。
Tip: 要在查询级别完成此操作,请添加 QUERYTRACEON 查询提示。 从 SQL Server 2016 (13.x) SP1 开始,要在查询级别完成此操作,请添加 USE HINT 查询提示,而不是使用跟踪标志。
PRIMARY
此值仅对辅助数据库(该数据库位于主数据库上)有效,指定所有辅助数据库上此设置的值都是为主数据库设置的值。 如果主数据库的配置更改,辅助数据库上的值也会相应地更改,不需要再显式设置辅助数据库值。 PRIMARY 是辅助数据库的默认设置。
CLEAR PROCEDURE_CACHE
清除数据库的过程(计划)缓存,可同时对主要和辅助数据库执行此操作。
IDENTITY_CACHE = { ON | OFF }
适用对象:SQL Server 2017 (14.x) 和 Azure SQL Database
在数据库级别启用或禁用标识缓存。 默认值为 ON。 标识缓存用于提高具有标识列的表的 INSERT 性能。 为了避免服务器意外重启或故障转移到辅助服务器时出现标识列值的差值,请禁用 IDENTITY_CACHE 选项。 该选项与现有跟踪标志 272 类似,但前者可在数据库级别设置,而不只是可在服务器级别设置。
Note: 仅可为 PRIMARY 设置此选项。 有关详细信息,请参阅标识列。
OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
适用于:Azure SQL Database
第一次编译批处理时,启用或禁用要存储在缓存中的已编译计划存根。 默认为 OFF。 为数据库启用了数据库作用域内配置 OPTIMIZE_FOR_AD_HOC_WORKLOADS 后,已编译计划存根可在第一次编译批处理时存储在缓存中。 与完全编译的计划大小相比,计划存根的内存占用空间更小。 如果编译或再次执行批处理,则会删除已编译计划存根,并将其替换为完全编译的计划。
XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }
适用于:Azure SQL Database
启用或禁用对当前数据库的本机编译的 T-SQL 模块在模块级别的执行统计信息收集。 默认为 OFF。 执行统计信息反映在 sys.dm_exec_procedure_stats 中。
如果该选项为“开”,或通过 sp_xtp_control_proc_exec_stats 启用了统计信息收集,则收集对本机编译的 T-SQL 模块的模块级别执行统计信息。
XTP_QUERY_EXECUTION_STATISTICS = { ON |OFF }
适用于:Azure SQL Database
启用或禁用对当前数据库的本机编译的 T-SQL 模块语句级别的执行统计信息收集。 默认为 OFF。 执行统计信息反映在 sys.dm_exec_query_stats 和查询存储中。
如果该选项为“开”,或通过 sp_xtp_control_query_exec_stats 启用了统计信息收集,则收集对本机编译的 T-SQL 模块的语句级别执行统计信息。
有关本机编译的 T-SQL 模块的性能监视的详细信息,请参阅监视本机编译的存储过程的性能。
ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
适用对象:Azure SQL Database(此功能为公共预览版)
允许你选择选项,使引擎自动将支持的操作提升为联机。 默认值为 OFF,表示除非在语句中指定,否则操作不会提升为联机。 sys.database_scoped_configurations 反映 ELEVATE_ONLINE 的当前值。 这些选项只适用于支持联机的操作。
FAIL_UNSUPPORTED
此值可将所有支持的 DDL 操作提升为 ONLINE。 不支持联机执行的操作会失败并引发警告。
WHEN_SUPPORTED
此值可提升支持 ONLINE 的操作。 不支持联机的操作将脱机运行。
Note: 通过提交指定了 ONLINE 选项的语句,可替代默认设置。
ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
适用对象*:SQL Database 和 SQL Server 2019 预览 为公共预览版功能
允许你选择选项,使引擎自动将支持的操作提升为可恢复。 默认值为 OFF,表示除非在语句中指定,否则操作不会提升为可恢复。 sys.database_scoped_configurations 反映 ELEVATE_RESUMABLE 的当前值。 这些选项只适用于支持可恢复的操作。
FAIL_UNSUPPORTED
此值可将所有支持的 DDL 操作提升为 RESUMABLE。 不支持可恢复执行的操作会失败并引发警告。
WHEN_SUPPORTED
此值可提升支持 RESUMABLE 的操作。 不支持可恢复的操作以不可恢复的方式运行。
Note: 通过提交指定了 RESUMABLE 选项的语句,可替代默认设置。
GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }
适用对象:Azure SQL Database(此功能为公共预览版)
允许为全局临时表设置自动删除功能。 默认值为 ON,这意味着如果没有任何会话使用全局临时表,系统会自动删除该表。 如果设置为 OFF,需要使用 DROP TABLE 语句显式删除或将在服务器重启时自动删除该表。
- 在 Azure SQL 数据库逻辑服务器中,可以在逻辑服务器的单个用户数据库中设置此选项。
- 在 SQL Server 和 Azure SQL 数据库托管实例中,要在
TEMPDB中设置此选项且单个用户数据库的设置不起作用。
DISABLE_INTERLEAVED_EXECUTION_TVF = { ON | OFF }
适用对象:SQL Server 2017 (14.x) 和 Azure SQL Database
允许用户在数据库或语句范围内启用或禁用交错执行,同时将数据库兼容性级别维持在 140 或更高。 交错执行是 SQL 数据库中自适应查询处理的一个功能。 有关详细信息,请参阅自适应查询处理
DISABLE_BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }
适用对象:SQL Server 2017 (14.x) 和 Azure SQL Database
允许用户在数据库或语句范围内启用或禁用自适应联接,同时将数据库兼容性级别维持在 140 或更高。 自适应联接是 SQL Server 2017 中推出的自适应查询处理的一个功能。
ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}
适用范围:SQL Database 和 SQL Server 2019 预览 为公共预览版功能
允许用户在数据库或语句范围内启用或禁用行模式内存授予反馈,同时将数据库兼容级别维持在 150 或更高。 行模式内存授予反馈是 SQL Server 2019 中推出的自适应查询处理的一个功能。
Permissions
需要在数据库中执行 ALTER ANY DATABASE SCOPE CONFIGURATION。 用户若具有针对数据库的 CONTROL 权限,便可授予此权限。
一般备注
虽然可以为辅助数据库配置不同于主数据库的作用域内配置设置,但所有辅助数据库都使用相同的配置。 无法为各辅助数据库配置不同的设置。
执行此语句会清除当前数据库中的过程缓存,这意味着需要重新编译所有查询。
对于 3 部分名称查询,采用查询的当前数据库连接设置,在当前数据库上下文中编译的 SQL 模块(例如过程、函数和触发器)除外,因此应使用其所在的数据库的选项。
ALTER_DATABASE_SCOPED_CONFIGURATION 事件添加为可用于触发 DDL 触发器的 DDL 事件,且是 ALTER_DATABASE_EVENTS 触发器组的子项。
将对数据库执行数据库范围的配置设置,这意味着还原或附加一个给定数据库时,将保留现有的配置设置。
限制和局限
MAXDOP
精细设置可以替代全局设置,而资源调控器可以限制所有其他 MAXDOP 设置。 MAXDOP 设置的逻辑如下:
-
查询提示可替代 sp_configure 和数据库作用域内设置。 如果为工作负荷组设置了资源组 MAXDOP:
-
如果查询提示设置为 0,则其会由资源调控器设置替代。
-
如果查询提示未设置为 0,则其会受资源调控器设置限制。
-
-
如果不存在受资源调控器设置限制的查询提示,则 DB 作用域内设置(除非为 0)会替代 sp_configure 设置。
-
sp_configure 设置由资源调控器设置替代。
QUERY_OPTIMIZER_HOTFIXES
QUERYTRACEON 提示用于启用旧版查询优化器或查询优化器修补程序时,会成为查询提示和数据库作用域内配置设置之间的 OR 条件,也就是说,如果启用了两者中任一一个,都会应用这些选项。
GeoDR
可读辅助数据库(Always On 可用性组和 Azure SQL 数据库异地复制数据库)通过检查数据库状态来使用辅助值。 尽管重新编译不会在故障转移时发生,且从技术上讲,新的主数据库具有使用辅助数据库设置的查询,但是,主数据库和辅助数据库的设置仅在工作负荷不同时才有所相同,因此已缓存查询使用的是最佳设置,而新查询选择适合它们的新设置。
DacFx
由于 ALTER DATABASE SCOPED CONFIGURATION 是 Azure SQL 数据库和 SQL Server 2016 及更高版本的 SQL Server 中的一项新功能,并且会影响数据库架构,因此架构导出(有或没有数据)无法导入到较早版本的 SQL Server,如 SQL Server 2012 (11.x) 或 SQL Server 2017 (14.x)。 例如,从使用新功能的 SQL Database 或 SQL Server 2016 (13.x) 数据库到 DACPAC 或 BACPAC 的导出无法导入到下级服务器。
ELEVATE_ONLINE
此选项仅适用于支持 WITH(ONLINE= 语法的 DDL 语句。 XML 索引不受影响。
ELEVATE_RESUMABLE
此选项仅适用于支持 WITH(RESUMABLE= syntax) 的 DDL 语句。 XML 索引不受影响。
元数据
sys.database_scoped_configurations (Transact-SQL) 系统视图提供有关数据库作用域内配置的信息。 数据库作用域内配置选项仅在 sys.database_scoped_configurations 中显示,因为它们是服务器范围内默认设置的替代项。 sys.configurations (Transact-SQL) 系统视图仅显示服务器范围内的设置。
示例
以下示例演示 ALTER DATABASE SCOPED CONFIGURATION 的用法
A. 授予权限
本示例为用户 [Joe] 授予执行 ALTER DATABASE SCOPED CONFIGURATION 所需的权限。
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;
B. 设置 MAXDOP
本示例在异地复制方案中为主数据库设置 MAXDOP = 1,为辅助数据库设置 MAXDOP = 4。
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;
本示例在异地复制方案中为辅助数据库设置与其主数据库相同的 MAXDOP。
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY ;
C. 设置 LEGACY_CARDINALITY_ESTIMATION
本示例在异地复制方案中为辅助数据库将 LEGACY_CARDINALITY_ESTIMATION 设置为 ON。
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=ON ;
本示例在异地复制方案中为辅助数据库设置与其主数据库相同的 LEGACY_CARDINALITY_ESTIMATION。
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=PRIMARY ;
D. 设置 PARAMETER_SNIFFING
本示例在异地复制方案中为主数据库将 PARAMETER_SNIFFING 设置为 OFF。
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;
本示例在异地复制方案中为主数据库将 PARAMETER_SNIFFING 设置为 OFF。
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=OFF ;
本示例在异地复制方案中为辅助数据库设置与其主数据库相同的 PARAMETER_SNIFFING。
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=PRIMARY ;
E. 设置 QUERY_OPTIMIZER_HOTFIXES
在异地复制方案中为主数据库将 QUERY_OPTIMIZER_HOTFIXES 设置为 ON。
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON ;
F. 清除过程缓存
本示例清除了过程缓存(仅可用于主数据库)。
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;
G. 设置 IDENTITY_CACHE
适用对象:SQL Server 2017 (14.x) 和 SQL Database(此功能为公共预览版)
本示例禁用了标识缓存。
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;
H. 设置 OPTIMIZE_FOR_AD_HOC_WORKLOADS
适用于:SQL Database
本示例可在第一次编译批处理时启用要存储在缓存中的已编译计划存根。
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
I. 设置 ELEVATE_ONLINE
适用范围:Azure SQL Database,且作为公共预览版功能
此示例将 ELEVATE_ONLINE 设置为 FAIL_UNSUPPORTED。
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE=FAIL_UNSUPPORTED ;
J. 设置 ELEVATE_RESUMABLE
适用范围:SQL Database 和 SQL Server 2019 预览 为公共预览版功能
此示例将 ELEVATE_RESUMABLE 设置为 WHEN_SUPPORTED。
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE=WHEN_SUPPORTED ;
其他资源
MAXDOP 资源
- 并行度
- Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server(适用于 SQL Server 中的“max degree of parallelism”配置选项的建议和指南)
LEGACY_CARDINALITY_ESTIMATION 资源
PARAMETER_SNIFFING 资源
- 参数截取
- "I smell a parameter!"(“我发现一个参数!”)
QUERY_OPTIMIZER_HOTFIXES 资源
ELEVATE_ONLINE 资源
ELEVATE_RESUMABLE 资源
详细信息
- sys.database_scoped_configurations
- sys.configurations
- 数据库和文件目录视图
- 服务器配置选项
- 联机索引操作的工作方式
- 联机执行索引操作
- ALTER INDEX (Transact-SQL)
- CREATE INDEX (Transact-SQL)
历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。
来源与署名:Microsoft 和文档贡献者(原始作者元数据 CarlRabeler)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。
