主页 / dba / 问题 / 303381
Accepted
MichaelD
Asked: 2021-12-02 23:01:05 +0800 CST

添加列时环境之间的性能差异

  • 772

我们有 2 个完全相同的数据库环境。第二个环境包含生产数据库的副本，并在 Invoice 表中托管大约 11M 条记录。此环境的目标用于查看特定升级查询需要多长时间才能知道是否会有任何停机时间（因为表在架构更改期间被锁定）

在第二个环境中执行 add 语句时

alter table Invoice add IsVerified bit not null default(0)

查询立即退出，这很奇怪，因为其中有 11M 条记录。我预计至少会有一点延迟。即使是选择计数（*）也需要更长的时间。然而，在主生产数据库上，它需要更长的时间，超过 30 秒，因此我们必须将此查询计划到一个特殊的维护窗口中。在执行查询时，没有任何东西阻塞 SPID（使用 sp_who2 检查）

可能是什么原因，数据库的第二个副本似乎根本没有努力在 11M 记录数据库中添加一列，而另一个 maindb 无法及时完成（<30 秒）。也许一些特殊设置允许您添加一个默认值列而不需要写入所有记录？难道是因为我们的测试环境是开发版，而生产环境是标准版？也许开发版中的某些特殊功能在 SQLStandard 中不可用？

select count(*) from Invoice //result: 11701200
SQL Server Execution Times:
   CPU time = 2375 ms,  elapsed time = 608 ms.

要添加的脚本：

alter table Invoice add IsVerified bit not null default(0)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 12 ms.
sql-server performance

1 个回答

  1. Best Answer

    将 NOT NULL 列添加为联机操作

    从 SQL Server 2012 (11.x)企业版开始，当默认值为运行时常量时，添加具有默认值的 NOT NULL 列是联机操作。这意味着尽管表中有多少行，但操作几乎是瞬间完成的。因为，表中的现有行在操作期间不会更新。相反，默认值仅存储在表的元数据中，并根据需要在访问这些行的查询中查找该值。这种行为是自动的。除了 ADD COLUMN 语法之外，不需要额外的语法来实现在线操作。运行时常量是一个表达式，它在运行时为表中的每一行生成相同的值，尽管它具有确定性。例如，常量表达式“我的临时数据”或系统函数 GETUTCDATETIME() 是运行时常量。相比之下，函数 NEWID() 或 NEWSEQUENTIALID() 不是运行时常量，因为为表中的每一行生成一个唯一值。添加具有非运行时常量的默认值的 NOT NULL 列始终脱机运行，并且在操作期间获取排他 (SCH-M) 锁。

    在线架构更改仍然是 SQL Server 2019 (15.x) 的一项企业功能，因此您可以在 Developer 版本上执行在线操作，而在 Standard 版本上则离线执行。

    • 5

相关问题