/
*
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!
Todo DB do SQL Server é composto, no mínimo, por dois arquivos. Um MDF,
onde estão armazenadas os objetos, tabelas e indices do banco e um arquivo LDF
que é o nosso arquivo de Log.
Quando você inicia um Update ou Insert em
uma tabela, todos os dados que serão alterados são armazenados no arquivo de
Log. Se você cancelar uma transação no meio do caminho, esse “backup” garante
que o banco volte ao status anterior ao seu comando.
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.
*/
CREATE Database Teste
GO
ALTER DATABASE Teste SET RECOVERY SIMPLE
GO
use Teste
GO
-- Dando uma
olhada nos arquivos criados.
sp_helpfile
GO
name fileid size
--------- ------
------------------
Teste 1
2304 KB
Teste_log 2 576 KB
-- Agora vamos
criar e popular nossa tabela de teste.
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)
-- Seguem
resultados
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.
Vamos agora refazer o banco, a tabela e a
população e fazer o update de forma diferente.
*/
DECLARE @Contador int = 1
WHILE @Contador <= 576
BEGIN
UPDATE top
(1)
Tabela
SET Campo = REPLICATE('Y',1003)
WHERE
Campo = REPLICATE('X',1003)
SET @Contador = @Contador + 1
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!
Porque será? O mesmo ocorreu algumas vezes no processo de Insert apesar de
serem inserções em bloquinhos de uma linha.
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
