Início / dba / Perguntas / 97738
Accepted
Randy Minder
Asked: 2015-04-14 10:03:40 +0800 CST

Alterando uma coluna de NOT NULL para NULL - O que está acontecendo sob o capô?

  • 772

Temos uma tabela com 2,3 bilhões de linhas. Gostaríamos de alterar uma coluna de NOT NULL para NULL. A coluna está contida em um índice (não no índice clusterizado ou PK). O tipo de dados não está mudando (é um INT). Apenas a nulidade. A declaração é a seguinte:

Alter Table dbo.Workflow Alter Column LineId Int NULL

A operação demora mais de 10 antes de ser interrompida (nós ainda não a deixamos terminar porque é uma operação de bloqueio e estava demorando muito). Provavelmente copiaremos a tabela para um servidor dev para testar quanto tempo realmente leva. Mas, estou curioso para saber se alguém sabe o que o SQL Server está fazendo sob o capô ao converter de NOT NULL para NULL? Além disso, os índices afetados precisarão ser reconstruídos? O plano de consulta gerado não indica o que está acontecendo.

A tabela em questão é agrupada (não um heap).

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Conforme mencionado por @Souplex nos comentários, uma possível explicação pode ser se esta coluna for a primeira NULLcoluna capaz no índice não clusterizado do qual ela participa.

    Para a seguinte configuração

    CREATE TABLE Foo
  (
     A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     B CHAR(1) NOT NULL DEFAULT 'B'
  )

CREATE NONCLUSTERED INDEX ix
  ON Foo(B);

INSERT INTO Foo
            (B)
SELECT TOP 100000 'B'
FROM   master..spt_values v1,
       master..spt_values v2

    sys.dm_db_index_physical_stats mostra que o índice não clusterizado ixtem 248 páginas folha e uma única página raiz.

    Uma linha típica em uma página de folha de índice se parece com

    insira a descrição da imagem aqui

    E na página raiz

    insira a descrição da imagem aqui

    Então correndo...

    CHECKPOINT;

GO

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;


SELECT Operation, 
       Context,
       ROUND(SUM([Log Record Length]) / 1024.0,1) AS [Log KB],
       COUNT(*) as [OperationCount]
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName = 'dbo.Foo.ix'
GROUP BY Operation, Context

    Retornou

    +-----------------+--------------------+-------------+----------------+
|    Operation    |      Context       |   Log KB    | OperationCount |
+-----------------+--------------------+-------------+----------------+
| LOP_SET_BITS    | LCX_GAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_IAM            | 0.100000    |              1 |
| LOP_SET_BITS    | LCX_IAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | 8.700000    |              3 |
| LOP_FORMAT_PAGE | LCX_INDEX_LEAF     | 2296.200000 |            285 |
| LOP_MODIFY_ROW  | LCX_PFS            | 16.300000   |            189 |
+-----------------+--------------------+-------------+----------------+

    Verificando a folha de índice novamente, as linhas agora se parecem

    insira a descrição da imagem aqui

    e as linhas nas páginas de nível superior como abaixo.

    insira a descrição da imagem aqui

    Cada linha foi atualizada e agora contém dois bytes para a contagem de colunas junto com outro byte para o NULL_BITMAP.

    Devido à largura de linha extra, o índice não clusterizado agora tem 285 páginas folha e agora duas páginas de nível intermediário junto com a página raiz.

    O plano de execução do

     ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

    parece o seguinte

    insira a descrição da imagem aqui

    Isso cria uma nova cópia do índice em vez de atualizar a existente e precisar dividir as páginas.

    • 28

  2. Ele definitivamente recriará o índice não clusterizado e não apenas atualizará os metadados. Isso é testado no SQL 2014 e realmente não deve ser testado em um sistema de produção:

    CREATE TABLE [z](
    [a] [int] IDENTITY(1,1) NOT NULL,
    [b] [int] NOT NULL,
 CONSTRAINT [c_a] PRIMARY KEY CLUSTERED  ([a] ASC))
go
CREATE NONCLUSTERED INDEX [nc_b] on z (b asc)
GO
insert into z (b)
values (1);

    E agora a parte divertida:

    DBCC IND (0, z, -1)

    Isso nos dará as páginas do banco de dados onde a tabela e o índice não clusterizado são armazenados.

    Encontre PagePIDonde IndexIDé 2 e PageTypeé 2 e faça o seguinte:

    DBCC TRACEON(3604) --are you sure that you are allowed to do this?

    e depois:

    dbcc page (0, 1, PagePID, 3) with tableresults

    Observe que há um bitmap nulo no cabeçalho:

    Extração do cabeçalho da página

    Agora vamos fazer:

    alter table z alter Column b int null;

    Se você estiver realmente impaciente, pode tentar executar o dbcc pagecomando novamente, mas ele falhará, então vamos verificar a alocação novamente com DBCC IND (0, z, -1). A página terá se movido como por mágica.

    Portanto, alterar a nulidade de uma coluna afetará o armazenamento de índices não clusterizados que cobrem essa coluna, pois os metadados precisam ser atualizados e você não precisa reconstruir os índices posteriormente.

    Muitas ALTER TABLE ... ALTER COLUMN ...operações podem ser executadas ONLINEa partir do SQL Server 2016, mas:

    ALTER TABLE (Transact-SQL)

    • A alteração de uma coluna de NOT NULLpara NULLnão tem suporte como uma operação online quando a coluna alterada é referenciada por índices não clusterizados.
    • 9

relate perguntas