Início / dba / Perguntas / 165840
Accepted
Bogdan Bogdanov
Asked: 2017-03-02 06:01:25 +0800 CST

Como a exclusão de um número semelhante de linhas pode resultar em um crescimento de log muito diferente?

  • 772

Eu tenho (na minha opinião) comportamento muito estranho de crescimento de log do SQL Server. Eu tenho um banco de dados com aproximadamente 8 GB de tamanho de log. Fiz backup do log de transações e comecei a excluir cerca de 4 milhões de linhas. O arquivo de log não cresceu. Então tentei excluir outros 4,9 milhões de outra tabela - o arquivo de log cresceu para cerca de 26 GB e encheu a unidade.

Consegui consertar a situação, mas fiquei curioso - já que (até onde sei) o arquivo de log contém apenas comandos para excluir registros (neste caso), como pode haver uma diferença tão grande entre 2 operações quando o número de linhas afetadas é apenas 20% diferente?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Muito para um comentário, mas apenas para provar que a quantidade de dados tem um impacto muito maior sobre o quanto é registrado, em comparação com simplesmente o número de linhas.

    Primeiro, crie um banco de dados e faça backup dele para não ficarmos pseudo-simples:

    CREATE DATABASE RLS;
GO
ALTER DATABASE RLS SET RECOVERY FULL;
GO
BACKUP DATABASE RLS TO DISK = 'c:\temp\rls.bak' WITH INIT;
GO
USE RLS;
GO

    Agora, crie uma tabela estreita e uma tabela larga e insira 2.000 linhas:

    CREATE TABLE dbo.skinnyTable(id int PRIMARY KEY);

CREATE TABLE dbo.wideTable(id int PRIMARY KEY,
  dt datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME(), 
  n nchar(2000) NOT NULL DEFAULT USER_NAME(),
  x uniqueidentifier NOT NULL DEFAULT NEWID(),
  y uniqueidentifier NOT NULL DEFAULT NEWID(),
  z uniqueidentifier NOT NULL DEFAULT NEWID());

INSERT dbo.skinnyTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

INSERT dbo.wideTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

    Faça backup do log duas vezes:

    BACKUP LOG RLS TO DISK = 'c:\temp\rls1.trn' WITH INIT;
BACKUP LOG RLS TO DISK = 'c:\temp\rls2.trn' WITH INIT;

    Com um log limpo, vamos excluir metade de cada tabela (1.000 linhas aleatórias):

    ;WITH cte AS (SELECT TOP (1000) * FROM dbo.skinnyTable ORDER BY NEWID())
  DELETE cte;

;WITH cte AS (SELECT TOP (1000) * FROM dbo.wideTable ORDER BY NEWID())
  DELETE cte;

SELECT AllocUnitName, log_rows = COUNT(*) 
  FROM sys.fn_dblog (NULL, NULL) AS l
  WHERE AllocUnitName LIKE N'%[ey]Table%'
  GROUP BY AllocUnitName;

    Eu recebo:

    AllocUnitName                                     log_rows
----------------------------------------------    --------
dbo.skinnyTable.PK__skinnyTa__3213E83F83E76BF2    2008
dbo.wideTable.PK__wideTabl__3213E83FEFF3F638      14991

    Claramente, há muito mais espaço de log necessário para excluir o mesmo número de linhas de uma tabela ampla, quando comparada a uma tabela mais fina.

    Como extensão lógica, o número e a largura dos índices também podem influenciar essa diferença. E eu nem tentei dados LOB, nem investiguei as linhas onde AllocUnitNameestava NULL. O acima por si só demonstra uma diferença de 7X.

    • 4

relate perguntas