Por que ALTER COLUMN to NOT NULL causa um crescimento maciço do arquivo de log?

Quando você altera uma coluna para NOT NULL, o SQL Server precisa tocar em todas as páginas, mesmo que não haja valores NULL. Dependendo do seu fator de preenchimento, isso pode levar a muitas divisões de página. Cada página que é tocada, é claro, tem que ser registrada, e eu suspeito, devido às divisões, que duas alterações podem ter que ser registradas para muitas páginas. Como tudo é feito em uma única passagem, no entanto, o log precisa levar em conta todas as alterações para que, se você clicar em cancelar, ele saiba exatamente o que desfazer.

Um exemplo. Mesa simples:

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

Agora, vamos ver os detalhes da página. Primeiro precisamos descobrir com qual página e DB_ID estamos lidando. No meu caso, criei um banco de dados chamado foo, e o DB_ID era 5.

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

A saída indicou que eu estava interessado na página 159 (a única linha na DBCC INDsaída com PageType = 1).

Agora, vamos ver alguns detalhes da página selecionada enquanto percorremos o cenário do 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);

Agora, eu não tenho todas as respostas para isso, pois não sou um cara interno profundo. Mas está claro que - enquanto a operação de atualização e a adição da restrição NOT NULL gravam inegavelmente na página - a última o faz de uma maneira totalmente diferente. Parece realmente alterar a estrutura do registro, em vez de apenas mexer nos bits, trocando a coluna anulável por uma coluna não anulável. Por que ele tem que fazer isso, eu não tenho certeza - uma boa pergunta para a equipe do mecanismo de armazenamento , eu acho. Eu acredito que o SQL Server 2012 lida com alguns desses cenários muito melhor, FWIW - mas ainda não fiz nenhum teste exaustivo.

Ao executar o comando

ALTER COLUMN ... NOT NULL

Isso parece ser implementado como uma operação Add Column, Update, Drop Column.

• Uma nova linha é inserida sys.sysrscolspara representar uma nova coluna. O statusbit for 128é definido indicando que a coluna não permite NULLs
• Uma atualização é realizada em cada linha da tabela, definindo o novo valor da coluna para o valor da coluna antiga. Se as versões "antes" e "depois" da linha forem exatamente as mesmas, isso não fará com que nada seja gravado no log de transações, caso contrário, a atualização será registrada.
• A coluna original é marcada como descartada (esta é uma alteração apenas de metadados em sys.sysrscols. rscolidatualizado para um número inteiro grande e statuso bit 2 definido como indicado como descartado)
• A entrada sys.sysrscolspara a nova coluna é alterada para dar-lhe o rscolidvalor da coluna antiga.

A operação que tem o potencial de causar muitos logs é a UPDATEde todas as linhas da tabela, mas isso não significa que isso sempre ocorrerá. Se as imagens "antes" e "depois" da linha forem idênticas, isso será tratado como uma atualização sem atualização e não será registrado nos meus testes até agora.

Portanto, a explicação de por que você está recebendo muitos registros dependerá de por que exatamente as versões "antes" e "depois" da linha não são as mesmas.

Para colunas de comprimento variável armazenadas no FixedVarformato, descobri que a configuração NOT NULLsempre causa uma alteração na linha que precisa ser registrada. A contagem de colunas e a contagem de colunas de comprimento variável são incrementadas e a nova coluna é adicionada ao final da seção de comprimento variável duplicando os dados.

datetimeoffset(0)é de comprimento fixo no entanto e para colunas de comprimento fixo armazenadas no FixedVarformato as colunas antigas e novas parecem ter o mesmo slot na parte de dados de comprimento fixo da linha e como ambas têm o mesmo comprimento e valorizam o "antes" e As versões "depois" da linha são as mesmas . Isso pode ser visto na resposta de @Aaron. Ambas as versões da linha antes e depois do ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;são

0x10000c00 01000000 00000000 020000

Isso não é registrado.

Logicamente, a partir da minha descrição de eventos, a linha deve ser diferente aqui, pois a contagem de colunas 02deve ser aumentada, 03mas essa mudança não acontece na prática.

Algumas possíveis razões pelas quais isso pode ocorrer em uma coluna de comprimento fixo são

• Se a coluna foi originalmente declarada como SPARSEentão a nova coluna seria armazenada em uma parte da linha diferente da original, fazendo com que as imagens da linha anterior e posterior fossem diferentes.
• Se você estiver usando qualquer uma das opções de compactação, as versões antes e depois da linha serão diferentes, pois a seção de contagem de colunas na matriz CD é incrementada.
• Em bancos de dados com uma das opções de isolamento de instantâneo ativada, as informações de versão em cada linha são atualizadas (@SQL Kiwi aponta que isso também pode ocorrer em bancos de dados sem SI ativado , conforme descrito aqui ).
• Pode haver alguma ALTER TABLEoperação anterior que foi implementada apenas como uma alteração de metadados e ainda não foi aplicada à linha. Por exemplo, se uma nova coluna de comprimento variável anulável foi adicionada, então isso é originalmente aplicado como uma alteração apenas de metadados e só é realmente gravado nas linhas quando elas são atualizadas em seguida (a gravação que realmente ocorre nesta última instância é apenas atualizações para a seção de contagem de colunas e NULL_BITMAPcomo uma NULL varcharcoluna no final da linha não ocupa nenhum espaço)

Eu enfrentei o mesmo problema em relação a uma tabela com 200.000.000 linhas. Inicialmente, adicionei a coluna anulável, atualizei todas as linhas e, finalmente, alterei a coluna por NOT NULLmeio de uma ALTER TABLE ALTER COLUMNinstrução. Isso resultou em duas transações enormes explodindo incrivelmente o arquivo de log (crescimento de 170 GB).

A maneira mais rápida que encontrei foi a seguinte:

1. Adicione a coluna usando um valor padrão

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

2. Elimine a restrição padrão usando SQL dinâmico, pois a restrição não foi nomeada antes:

   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';
   

O tempo de execução foi reduzido de > 30 minutos para 10 minutos, incluindo a replicação das alterações por meio da Replicação Transacional. Estou executando uma instalação do SQL Server 2008 (SP2).

Fiz o seguinte teste:

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)

Acredito que isso tenha a ver com o espaço reservado que o log mantém apenas no caso de você reverter a transação. Procure na função fn_dblog na coluna 'Log Reserve' para a linha LOP_BEGIN_XACT e veja quanto espaço ela está tentando reservar.

O comportamento para isso é diferente no SQL Server 2012. Consulte http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

O número de registros de log gerados para o SQL Server 2008 R2 e versões anteriores será significativamente maior do que o número de registros de log para o SQL Server 2012.