为什么 ALTER COLUMN to NOT NULL 会导致大量日志文件增长？

一尘不染

为什么 ALTER COLUMN to NOT NULL 会导致大量日志文件增长？

sql

我有一个 64m 行的表，它的数据在磁盘上占用了 4.3 GB。

每行大约有 30 个字节的整数列，加上一个NVARCHAR(255)用于文本的可变列。

我添加了一个带有 data-type 的 NULLABLE 列Datetimeoffset(0)。

然后我为每一行更新了这一列，并确保所有新插入都在该列中放置一个值。

一旦没有 NULL 条目，我就运行此命令以使我的新字段成为强制性：

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

结果是事务日志大小大幅增长 - 从 6GB 到超过 36GB，直到空间用完！

有谁知道 SQL Server 2008 R2 到底在为这个简单的命令做了什么来导致如此巨大的增长？

阅读 65

2022-10-27

共1个答案

一尘不染

当您将列更改为 NOT NULL 时，SQL Server 必须触及每一页，即使没有 NULL 值也是如此。根据您的填充因子，这实际上可能导致大量页面拆分。当然，每个被触摸的页面都必须被记录，我怀疑由于拆分，可能必须为许多页面记录两个更改。但是，由于这一切都是一次性完成的，因此日志必须考虑所有更改，这样，如果您点击取消，它就会确切地知道要撤消什么。

一个例子。简单表：

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

现在，让我们看一下页面详细信息。首先，我们需要找出我们正在处理的页面和 DB_ID。在我的例子中，我创建了一个名为的数据库foo，而 DB_ID 恰好是 5。

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

输出表明我对第 159 页（DBCC IND输出中唯一带有的行PageType = 1）感兴趣。

现在，让我们在逐步完成 OP 的场景时查看一些选择页面的详细信息。

DBCC PAGE(5, 1, 159, 3);

在此处输入图像描述

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;    
DBCC PAGE(5, 1, 159, 3);

在此处输入图像描述

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

在此处输入图像描述

现在，我没有这个问题的所有答案，因为我不是一个内心深处的人。但很明显 - 虽然更新操作和添加 NOT NULL 约束都不可否认地写入页面 - 后者以完全不同的方式这样做。通过将可空列换成不可空列，它似乎实际上改变了记录的结构，而不仅仅是摆弄位。为什么它必须这样做，我不太确定——我想这对存储引擎团队来说是个好问题。我确实相信 SQL Server 2012 可以更好地处理其中一些场景，FWIW - 但我还没有进行任何详尽的测试。

2022-10-27