Alterar Tabela Modificar Coluna Problemas ONLINE no SQL Server

Alterar Tabela Modificar Coluna Problemas ONLINE no SQL Server

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 19-08-2020 | Comentários | Relacionado: Mais> Design de Banco de Dados

Problema

Recentemente, executamos uma operação DDL em uma tabela do SQL Server – simplesmente aumentando o tamanho de uma coluna varchar – que deveria ter sido instantânea. Em vez disso, o eliminamos após observar 20 minutos de esperas de HARD_SYNC_COMMIT e um leitor de log de replicação bloqueado. Esse problema poderia ter sido evitado? O que deu errado?

Solução

Aqui estava a tabela original (bastante simplificada):

CREATE TABLE dbo.Floob
( FloobID int IDENTITY(1,1) PRIMARY KEY,
PostalCode varchar(32)
);

E aqui estava o comando para alterar a coluna PostalCode de 32 caracteres para 64:

ALTER TABLE dbo.Floob 
ALTER COLUMN PostalCode varchar(64) NOT NULL
WITH (ONLINE = ON);

Se você não identificar o problema imediatamente, não se preocupe, você não está sozinho. Nós também sentimos falta. O problema é que a tabela original tinha a coluna definida como anulável (embora NULL tenha sido impedido por outra lógica). No entanto, o comando DDL gerado do controle de origem alterou inadvertidamente PostalCode para NOT NULL, com base em uma regra (ou suposição) em vez do estado atual da tabela.

Além disso, quero deixar claro que esse problema não foi causado especificamente pelo fato de a operação estar online – é apenas por isso que foi surpreendente ver, embora tenhamos explicitamente solicitado uma operação online. Você pode não conseguir testar este cenário exatamente, uma vez que alterar uma coluna online é restrito à Enterprise Edition (assim como as operações de índice online). Se você tentar a sintaxe em uma edição inferior, verá esta mensagem de erro:

Leia Também  Como instalar o servidor de banco de dados MySQL 8.0.19 no Windows 10

Msg 1712, Nível 16, Estado 1
As operações de índice online só podem ser realizadas na edição Enterprise do SQL Server.

Alterando nulidade pode ser, efetivamente, uma operação de tamanho de dados, seja online ou não. Em uma mesa pequena, é improvável que isso seja notado; em uma mesa grande, entretanto, pode se tornar um problema. E podemos provar isso simplesmente inspecionando o log de transações antes e depois de uma alteração e entendendo como essas alterações causam impacto no recebimento de dados (basicamente, qualquer coisa que use o leitor de log ou refaça, como replicação e grupos de disponibilidade).

Para configurar um teste rápido, podemos executar repetidamente um script com pequenas alterações para detectar casos em que a quantidade de trabalho registrado varia com o número de linhas (o que não deveria acontecer para uma operação que é verdadeiramente online e indiferente ao número de linhas afetadas):

  • Definir a coluna inicial NULL ou NOT NULL
  • Preenchendo a tabela com 10, 100, 1.000 ou 10.000 linhas
  • Alterar a coluna explicitamente para ser NULL ou NOT NULL
    • Executando esta mudança com ONLINE = ON ou OFF

O esqueleto do teste se parece com este:

-- create table with null or not null
-- populate with n rows
-- count number of log records in fn_dblog()
-- alter table using null or not null, online or offline
-- find delta in number of log records in fn_dblog()

E aqui está o código real (acabei de recriar o banco de dados todas as vezes para manter as coisas limpas):

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
USE master;
GO ALTER DATABASE Splunge SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO DROP DATABASE Splunge;
GO CREATE DATABASE Splunge;
GO USE Splunge;
SET NOCOUNT ON;
GO CREATE TABLE dbo.Floob
(
FloobID int IDENTITY(1,1) PRIMARY KEY,
PostalCode varchar(32) NULL -- change to NOT NULL
);
GO DECLARE @n int = 10; -- 100, 1000, 10000 INSERT dbo.Floob(PostalCode) SELECT TOP (@n) 'N0T L33T'
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2;
GO DECLARE @i int = (SELECT COUNT(*) FROM sys.fn_dblog(DB_ID(), NULL)); DECLARE @d datetime2 = sysdatetime(); ALTER TABLE dbo.Floob
ALTER COLUMN PostalCode varchar(64)
NULL -- change to NOT NULL
WITH (ONLINE = ON); -- change to OFF (or comment out) SELECT DurationMilliseconds = DATEDIFF(MILLISECOND, @d, sysdatetime()); SELECT LogRecords = COUNT(*)[email protected] FROM sys.fn_dblog(DB_ID(), NULL);

Executei cada uma dessas variações de teste 10 vezes e calculei a média da duração (que variava de teste para teste) e dos registros produzidos (que era constante para cada variação). Aqui estão os resultados dos registros de log:

E a duração, que não deve ser surpresa depois de ver a atividade de log:

Leia Também  Solucionando problemas de desequilíbrio do nó NUMA |

Essencialmente, mudar a coluna de NULL para NOT NULL invocou um monte de atividades de log adicionais, e isso aumentou com o número de linhas afetadas, tornando-se uma operação de tamanho de dados, pelo menos até certo ponto. A maior parte dessa atividade de log caiu em LOP_INSERT_ROWS para operações online e LOP_MODIFY_ROW ao executar o ALTER offline. Essa atividade de log pode obviamente impactar a duração e, como sugerido antes, pode afetar os sistemas downstream e a movimentação de dados também.

Você notará que as operações online exigiram quase o dobro da atividade de log do que offline e também cerca do dobro da duração. Este é o preço que você paga para não bloquear – outras atividades podem ser executadas na tabela enquanto a coluna está sendo alterada, mesmo se ela se tornar uma operação de tamanho de dados.

Resumo

Tenha cuidado ao planejar alterações de esquema. O estado de nulidade original de uma coluna é algo que pode ser facilmente esquecido quando seus comandos DDL devem ter uma determinada aparência ou estar em conformidade com algum padrão ou padrão específico. Se sua intenção é mudar a nulidade, então, por suposto, é algo que você tem que fazer. Em nosso caso, isso foi um efeito colateral não intencional, e o impacto no log foi inesperado porque, na maioria dos outros cenários, aumentar o tamanho de uma coluna de comprimento variável é uma operação online apenas de metadados, independentemente de você especificar ou não. Em uma dica futura, falarei sobre o que acontece quando a coluna participa de um índice não agrupado e como as alterações da coluna ONLINE ainda podem causar bloqueio / bloqueio.

Leia Também  Visualize o impacto do Coronavirus usando um painel do Power BI
Próximos passos

Veja estas dicas relacionadas e outros recursos:

Última atualização: 19-08-2020

Sobre o autor

Aaron Bertrand (@AaronBertrand) é um tecnólogo apaixonado com experiência no setor desde Classic ASP e SQL Server 6.5. Ele é editor-chefe do blog relacionado ao desempenho, SQLPerformance.com, e também blogs em sqlblog.org.

Ver todas as minhas dicas