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

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

#1 楼

当您将列更改为NOT NULL时，即使没有NULL值，SQL Server也必须触摸每个页面。根据您的填充因子，这实际上可能导致很多页面拆分。当然，每个被触摸的页面都必须记录下来，而且我怀疑由于拆分，许多页面可能不得不记录两个更改。但是，由于所有操作都是一次完成的，因此日志必须考虑所有更改，以便在您单击“取消”时，它确切地知道要撤消的内容。


。简单表：

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-但我尚未进行任何详尽的测试。

#2 楼

执行命令时

ALTER COLUMN ... NOT NULL

这似乎是作为“添加列，更新，删除列”操作实现的。


在sys.sysrscols中插入新行以表示新列。将status的128位置1表示该列不允许NULL。对表的每一行进行更新，将新的columnn值设置为旧的列值。如果该行的“之前”和“之后”版本完全相同，则不会导致将任何内容写入事务日志，否则将记录更新。
原始列被标记为已删除（这是sys.sysrscols中仅元数据的更改。rscolid已更新为大整数，并且status的第2位设置为指示的已删除）
sys.sysrscols中新列的条目为

该表中所有行的rscolid可能导致大量日志记录，但是该操作可能会导致大量日志记录，但这并不意味着这种情况将始终发生。如果该行的“之前”和“之后”图像相同，那么这将被视为非更新更新，并且到目前为止尚未从我的测试中记录。

因此，请解释为什么越来越多的日志记录将取决于为什么该行的“之前”和“之后”版本完全不同。
对于以UPDATE格式存储的可变长度列，我发现设置为FixedVar总是导致需要记录的行发生更改。列数和可变长度列数都增加，并将新列添加到可变长度部分的末尾，从而复制数据。

NOT NULL是固定长度的，但是对于以datetimeoffset(0)格式存储的固定长度列，旧列和新列在行的固定长度数据部分中似乎都被赋予了相同的位置，并且它们都具有相同的长度和值，因此“该行的“和”之后版本是相同的。可以在@Aaron的答案中看到。 FixedVar之前和之后的行的两个版本都是

0x10000c00 01000000 00000000 020000

这没有记录。实际上，这里有所不同，因为应将列数ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;增加到02，但实际上实际上没有发生这种变化。

为什么在固定长度列中可能发生这种情况的一些可能原因是


如果该列最初被声明为03，则新列将与原始列存储在该行的不同部分中，从而导致前后行图像不同。
如果使用任何压缩选项，则随着CD阵列中列数部分的增加，该行的前后版本将有所不同。
在启用了快照隔离选项之一的数据库上，然后更新每行中的版本控制信息（@SQL Kiwi指出，这也可能在未启用SI的数据库中发生，如此处所述）。
可能被实现为仅作为元数据的某些以前的SPARSE操作发生了变化，尚未应用于行。例如，如果添加了新的可为空的可变长度列，则该列最初仅作为元数据更改而应用，并且仅在下一次更新时才实际写到行中（在最后一个实例中实际发生的写操作只是更新为列计数部分和作为行末尾ALTER TABLE NULL_BITMAP列的NULL不会占用任何空间）

#3 楼

关于具有200.000.000行的表，我遇到了相同的问题。最初，我添加了可为空的列，然后更新了所有行，最后通过NOT NULL语句将列更改为ALTER TABLE ALTER COLUMN。
这导致两个巨大的事务令人难以置信地炸毁了日志文件（增加了170 GB）。

我发现最快的方法如下：



使用默认值添加列

ALTER TABLE table1 ADD column1 INT NOT NULL DEFAULT (1)

通过使用动态SQL来删除默认约束，因为之前没有命名约束：

DECLARE 
    @constraint_name SYSNAME,
    @stmt NVARCHAR(510);

SELECT @CONSTRAINT_NAME = DC.NAME
FROM SYS.DEFAULT_CONSTRAINTS DC
INNER JOIN SYS.COLUMNS C
    ON DC.PARENT_OBJECT_ID = C.OBJECT_ID
    AND DC.PARENT_COLUMN_ID = C.COLUMN_ID
WHERE
    PARENT_OBJECT_ID = OBJECT_ID('table1')
    AND C.NAME = 'column1';

执行时间从> 30分钟减少到10分钟，包括通过事务复制来复制更改。我正在运行SQL Server 2008安装（SP2）。

#4 楼

我运行了以下测试：

create table tblCheckResult(
        ColID   int identity
    ,   dtoDateTime Datetimeoffset(0) null
    )

 go

insert into tblCheckResult (dtoDateTime)
select getdate()
go 10000

checkpoint 

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

select * from fn_dblog(null,null)

我相信这与日志保留的空间有关，以防万一您回滚事务。在LOP_BEGIN_XACT行的“日志保留”列中的fn_dblog函数中查找，并查看它打算保留多少空间。

#5 楼

此行为在SQL Server 2012中有所不同。请参阅http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

为SQL Server 2008 R2和更低版本生成的日志记录数将大大高于SQL Server 2012的日志记录数。