Olá Senhores, Senhoras e Senhoritas DBAS!!!
Um Feliz 2012 (e
um Feliz SQL Server 2012) para todos nós!!!
Para começar o ano, quero explicar uma interessante técnica de como fazer
para executar grandes updates em blocos menores.
Mas porque eu devo me preocupar com isso?
(PARTE TEÓRICA,
se não interessar, pule para a prática!)
A questão está no nosso esquecido LDF!
Talvez você já tenha notado que quando
você reinicia o serviço do SQL, as bases de dados entram em estado “In Recovery”,
que basicamente, é a operação de rollback de todas as transações que não foram
concluídas no momento do crash (UNDO) e a garantia de que as transações
comitadas que estavam na memória estejam devidamente escritas no arquivo de
dados(REDO).
O arquivo de log também é um arquivo circular, ou seja, conforme as transações vão sendo comitadas e transferidas para o MDF o espaço que elas ocupavam no LDF é truncado e pode ser reutilizado para novas operações.
Então, quando você faz um update grande e
o espaço livre atual do arquivo de log não é suficiente, você obriga que arquivo
de log a crescer para comportar a mudança. Isso acarreta em lentidão na
transação (pois é uma operação a mais) e também a fragmentação do seu arquivo
de log que pode causar uma depreciação na performance de todo o banco.
Portanto, quebrar um grande update em
pequenos blocos, garante que o arquivo de log libere espaço para ser
reutilizado e evita seu crescimento.
No final do artigo coloquei um artigo do Paul Randal que é a referencia
forte desse post e é bem aprofundada no assunto.
Tá legal, e como eu faço isso?
(PARTE PRATICA!)
/*
-- Primeiro
vamos criar nosso banco de teste, mudando o
Recovery Mode para Simple, o que evita a necessidade de -- um backup de log
para truncar o log.*/
GO
ALTER DATABASE Teste SET RECOVERY SIMPLE
GO
use Teste
GO
-- Dando uma
olhada nos arquivos criados.
sp_helpfileGO
name fileid size
--------- ------
------------------ Teste 1 2304 KB
Teste_log 2 576 KB
CREATE TABLE Tabela (Campo char(1003) NOT NULL)
GO
INSERT INTO Tabela VALUES (REPLICATE('X',1003))
GO 576 /*
Uma nova checagem no tamanho mostra que não houve um crescimento no log. Em
meu lab algumas vezes o log cresceu para pouco mais de 800 Kb. Como era um
comportamento intermitente foi dificil acha a razão. Só achei no fim desse
artigo!
*/
sp_helpfile
name fileid size
--------- ------
------------------ Teste 1 2304 KB
Teste_log 2 576 KB
/*
Vamos dar uma olhada também na quantidade de VLFs (virtual log files)
existentes dentro do arquivo de log. Isso nos da uma noção da fragmentação
desse arquivo (mais informações sobre VLFs, leia o artigo no final
do post).*/
DBCC LOGINFO
FileId FileSize StartOffset FSeqNo Status
-----------
-------------------- -------------------- ----------- -----------2 253952 8192 86 2
2 327680 262144 0 0
(2 row(s) affected)
-- OK. Cenário
pronto vamos ao grande update.
UPDATE Tabela
SET Campo = REPLICATE('Y',1003)sp_helpfile
name fileid size
--------- ------
------------------ Teste 1 2304 KB
Teste_log 2 2624 KB
*/
DBCC LOGINFO
FileId FileSize StartOffset FSeqNo Status
-----------
-------------------- -------------------- ----------- ----------- 2 253952 8192 88 0
2 327680 262144 89 2
2 262144 589824 90 2
2 262144 851968 91 2
2 262144 1114112 92 2
2 262144 1376256 93 2
2 262144 1638400 94 2
2 262144 1900544 0 0
2 262144 2162688 0 0
2 262144 2424832 0 0
(10 row(s) affected)
/*
Explodiu! O tamanho do log passou
até mesmo o tamanho do arquivo de dados.
Como eu disse acima, isso se deu porque foi necessário que toda a tabela Teste estivesse armazenada no arquivo de log como garantia que no caso de um rollback, as informações pudessem voltar ao estado original.
*/
DECLARE @Contador int = 1
WHILE @Contador <= 576
BEGIN
UPDATE top
(1)
Tabela
SET Campo = REPLICATE('Y',1003)WHERE
Campo = REPLICATE('X',1003)
END
/*
O contador soma um para cada iteração e o
update (por conta do Top (1)) só afeta uma linha por vez. Assim sendo, são 576
iterações e apenas uma linha por update. Isso deve resolver o assunto, certo?
/*
sp_helpfile
name fileid size
--------- ------
------------------ Teste 1 2304 KB
Teste_log 2 1088 KB
*/
DBCC LOGINFO
FileId FileSize StartOffset FSeqNo Status
-----------
-------------------- -------------------- ----------- ----------- 2 253952 8192 94 0
2 327680 262144 93 0
2 262144 589824 95 2
2 262144 851968 92 0
(4 row(s) affected)
/* ERRADO!!! Teoricamente não deveria, mas ocorreu um aumento, menor
que no caso do update seco, mas tivemos!
Bem, a única razão aparente é que o processo de truncar o log é assíncrono
e decidido pelo SQL Server. De forma que, como o update da tabela Teste é bem
rápido, não está dando tempo para o SQL truncar o espaço já comitado antes de
ele ser requisitado pelo novo update do loop.
Para provar a teoria, coloquei um CHECKPOINT após cada update para garantir que todas as paginas em memória fossem
escritas no disco e permitissem o SQL truncar o arquivo de log. E bingo! O
tamanho se manteve em 576 Kb, apesar da operação ter demorado muuuuuuito mais.
ATENÇÂO: O uso do CHECKPOINT não deve estar no seu
código, ele é administrativo e não uma função que deve ser executada a todo
momento. Ele só serviu para provar a teoria.
Enfim, espero
que isso tenha ajudado você. Se tiver dúvidas, criticas ou sugestões de tema,
me mande um email fjantunes@gmail.com
ou me twitta no @felipe_store.
Abraços e
Obrigado!!!
Fonte:
Understanding Logging and Recovery in SQL Server – Paul Randal
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
Este post me ajudou muito hoje viu!!!
ResponderExcluirParabéns pelo Blog!
abraço!