T-SQL Tips - Grandes UPDATES em Blocos, seu arquivo de log agradece!

/*

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