SQL Server Transactions: Arquitetura de logs de transação no SQL Server

Neste artigo, estaremos interessados em analisarmos a arquitetura dos logs de transação. Neste momento, teremos então uma visão geral que será apresentada com relação aos VLF’s, que são os Virtual Log Files. Trataremos no decorrer do artigo com relação a questões de armazenamento, truncamentos e considerações com relação a espaços utilizados por estas VLF’s. Temos muito o que compreender sobre os logs daqui pra frente para que possamos garantir uma maior segurança com relação ao armazenamento e utilização dos logs.

O que são os VLF’s (Virtual Log Files)

Os VLF’s são arquivos sequenciais, os quais em outras palavras, são utilizados pelo SQL Server para escrever a sequência dos logs de transação, o que ao contrário de arquivos de dados, que tendem a ser escritos de uma forma aleatória.

Considerações de armazenamento

O que podemos considerar com relação a maneira diferenciada com a qual os dados e arquivos de log são escritos significa, dentre outras coisas, que eles possuem também considerações de armazenamento diferentes a ser abordadas, como por exemplo no que diz respeito à configuração de RAID’s apropriados para as unidades de disco que armazenam cada tipo de arquivo.

Para cada registro de log que for inserido no arquivo de log, este é marcado por um número de sequência lógica (LSN). No caso, quando um banco de dados e seus arquivos de log associados são criados, o primeiro registro de log marca o início do arquivo lógico, o que nesta fase coincidirá com o início do arquivo físico. Os LSN’s com o passar do tempo, passam a crescer, e se tornar cada vez maior, onde o registro de log mais recentemente adicionado terá sempre o maior LSN, e marcará o fim do arquivo lógico. Todos os registros de log associados a uma determinada transação estão ligados em uma cadeia de LSN com ponteiros que apontam tanto para a frente quanto para trás, para que desta forma as operações na transação apresentem as transações que sucederam e precederam a operação atual. Em seus processos internos, o SQL Server realiza a divisão de um arquivo de log de transações em um número de seções, as quais são chamadas de arquivos de log virtuais (VLF). A Figura 1 nos mostra um registo de transações constituído por cinco VLF’s, e que também marca a porção ativa do log.

Figura 1. Porção de um log transacional constituído por cinco partes.

De fato, há uma série de razões, além de ser parte de uma transação ativa que pode manter um registro de log ativo, mas neste momento o que nos importa é que, se um registro de log é necessário para qualquer operação ou atividade existente, o registro de log estará ativo e, como tal, o VLF será parte ativa no processo. Neste processo, o registro de log mais recente sempre terá o maior LSN, apresentado de acordo com a Figura 1, onde demarcamos a porção final ativa como sendo LastLSN, e com isso marcando o fim lógico do log. Todos os registros subsequentes passam a ser escritos para o fim lógico do log. Como já podíamos imaginar, a parte do arquivo que se encontra entre o registro MinLSN e o fim lógico do log é chamado de log ativo. É importante notarmos que o registro ativo não contém apenas detalhes de transações ativas em seu processo, como por exemplo, consideremos um caso em que o MinLSN seja definido por um registro de log para uma transação aberta (T1), que começou exatamente às 10:00 e levou 30 minutos para ser executado por completo. Se uma segunda transação, chamada de T2, começar às 10:10 e terminar às 10:11, esta, ainda fará parte do log ativo, uma vez que o LSN dos registros de log relacionados será maior do que o MinLSN. Num segundo momento, quando o processo de T1 é finalizado às 10:30, e uma nova MinLSN pode ser definida por um registro de log para uma transação aberta, chamada T3, que teve seu início às 10:25. Neste caso, os registros de log para T2 não farão mais parte do log ativo.

Qualquer VLF que contenha qualquer parte do log ativo é considerado como uma VLF ativa. Como podemos observar na Figura 1, vemos que a VLF2 é um VLF ativa, pois contém um trecho ativo. Podemos considerar aqui, de uma forma bem simplória, que as transações começaram e foram comitadas, podemos imaginar então que o ponto inicial do log irá se mover da esquerda para a direita, como mostra a Figura 1, de modo que o VLF que anteriormente continha parte do log ativo se torna inativo, como é o caso do VLF1 e os VLF’s que eram anteriormente intocados, como seria o caso do VLF5, passa a fazer parte do log ativo. Agora, o que acontece para que uma VLF seja marcada como "inativo"? Este é o ponto que depende do modelo de recuperação que estará sendo utilizado para o banco de dados! Vejamos agora com relação a isso.

Truncamento de log e espaço Reutilização

Além de todo o conteúdo abordado até o momento, precisamos observar que a menor unidade de truncamento no arquivo de log não é o log individual de um registro ou mesmo de um bloco de logs, mas sim, o VLF. No caso, se houver apenas um registro de log em um VLF que ainda faça parte do log ativo, então todo o VLF será considerado ativo e dessa forma, não poderá ser truncado. De forma geral, um VLF pode estar em um dos dois estados físicos, ou ativo ou inativo. No entanto, dentre os possíveis "comportamentos" diferentes de um VLF, podemos identificar quatro estados lógicos, os quais são:

1. Ativo - um VLF está em seu estado ativo, quando contém pelo menos um registro de log que faz parte do log ativo, e por isso é necessário para o processo de reversão, ou para outros fins.
2. Recuperável – é quando um VLF está em estado inativo, mas que não foi truncado ou feito backup. Neste caso, o espaço não pode ser reutilizado.
3. Reutilizável – é quando um VLF está em estado inativo, mas neste caso, ele foi truncado ou foi feito backup e o espaço pode ser reutilizado.
4. Não utilizado – neste caso, um VLF está em estado inativo, mas há registros de log que já foram gravados nele.

O ato de marcar um VLF como inativo, de acordo com nossos estados lógicos, significa mudar do estado 2 para o estado 3, o que é conhecido como o truncamento de log! Para a ocorrência desse tipo de truncamento ocorrer, isso irá depender do modelo de recuperação que estará sendo utilizado. Quando um banco de dados está no modelo de recuperação simples, um VLF ativo pode ser desativado pela ocorrência do processo de checkpoint, por exemplo. Já quando o ponto de verificação ocorre, todas as páginas sujas no cache são liberadas para o disco e, em seguida, o espaço no log fica disponível para reutilização. No entanto, nos modelos FULL ou BULK LOGGED, apenas um backup do log pode mudar um VLF ativo para inativo. Neste caso, uma vez que o backup do log tenha marcado qualquer VLF que não será mais necessária como inativa e, portanto, reutilizável. Como apresentado pela Figura 2, podemos ver o resultado de um checkpoint (ou mesmo de um backup), que o VLF1 e parte do VLF2 foram truncados e desta forma, estão inativos. O início do registro lógico agora é o pedaço existente do VLF2.

Figura 2. Log de transação com 3 VLF, depois de truncamento.

A próxima questão a ser considerada aqui é com relação ao que acontece quando o log ativo chega ao fim da VLF3. Neste caso, é mais fácil pensar no espaço que existe no arquivo de log como sendo reutilizado de maneira circular, embora existam fatores que às vezes possam fazer padrões de reutilização de espaço bastante arbitrários, e que nós não iremos nos aprofundar no momento. No entanto, num caso mais simples que posamos imaginar, uma vez que o fim lógico do registro atingir o fim de um VLF, o SQL Server irá então começar a reutilizar o próximo VLF sequencial que está inativo. Na Figura 1, este seria o VLF5. Quando o VLF5 estivesse completo, a reutilização seria em torno dos VLF 1 e 2. No caso de nenhum dos VLF’s estivessem mais disponíveis, seria então necessário haver o auto crescimento do registro e a adição de mais VLF’s. Já no caso disso não ser possível, devido ao auto crescimento que está desativado ou a caixa do disco, o arquivo de log possam estar cheios, então a lógica final do log ativo vai encontrar o fim físico do arquivo de log, com o log de transações cheio, será emitida a mensagem de erro 9002.

Esta arquitetura explica consideravelmente a razão pela qual, por exemplo, uma transação com uma longa duração, ou uma transação replicada que por algum motivo não tenha sido despachada para o banco de dados de distribuição, ou um espelho desconectado, entre outros, podem fazer com que o log cresça demasiadamente. Por exemplo, consideremos que na Figura 1, a transação associada ao MinLSN seja tida por um processo demorado. O registro foi utilizado, completou os VLF’s 1, 2 e 5, e não há mais VLF inativos no decorrer do processo. Mesmo que cada transação que tenha sido comitada após o MinLSN, nenhum dos espaços existentes nestes VLF’s pode ser reutilizado, já que todos os VLF’s ainda fazem parte do log ativo.

Podemos ver isso em ação com bastante facilidade, de acordo com a Listagem 1, a qual usamos primeiro para eliminar e recriar o banco de dados TestDB. Em seguida, criamos uma tabela de exemplo, atualizamos uma das linhas da tabela dentro de uma transação explícita, e deixamos a transação aberta. Em uma segunda operação, que realizaremos numa segunda aba dentro do SQL Server Management Studio (SSMS), executamos então os scripts em listas de 1,2 a 1,4. Desta vez podemos ver que o espaço não está disponível para reutilização após o backup do log. No entanto, se, em seguida, confirmarmos a transação e executarmos novamente o backup de log, ele será reutilizado.

Listagem 1. Criação de uma transação explicita.

Em determinados casos, onde a "área" que foi ocupada pelo log ativo for muito grande, no qual possam ser encontrados muitos espaços que não possam ser reutilizáveis, poderá ocorrer em algum momento o crescimento do log em tamanho. Mais adiante teremos a oportunidade de ver com relação a fatores que podem atrasar o truncamento do arquivo de log.

Como verificar a quantidade de VLF’s?

Em geral, o SQL Server decide qual o tamanho ideal e o número de VLF’s que serão atribuídas. No entanto, em uma operação na qual o log cresce automaticamente com frequência, em pequenos incrementos, este terá um número muito grande de VLF’s com o tamanho pequeno. Este é o tipo de fenômeno que podemos chamar de fragmentação de log e que podemos vê-lo em ação, em algumas medidas, onde estaremos repetindo o exemplo apresentado pela Listagem 1, enquanto questionamos a arquitetura VLF usando um comando chamado DBCC loginfo.

O DBCC loginfo é um comando não documentado e sem suporte oferecido pela Microsoft, que pode ser utilizado para interrogar a VLF. Ao realizarmos esta operação, ele nos retorna cada linha representando uma VLF e, entre outras coisas, indicando o status destas VLF’s. Quando encontramos um valor de estado sendo apresentado como 0, isso quer dizer que o VLF é utilizável (o que quer dizer que estão no estado 3 ou 4), já quando temos um valor de estado sendo apresentado como 2, este indica que o VLF não é utilizável (ou seja, encontra-se no estado 1 ou 2). Neste momento, basta apenas executarmos novamente o código presente na Listagem 1 para eliminarmos e recriarmos o banco de dados TestDB que criamos anteriormente. E em seguida, executarmos novamente o comando DBCC loginfo como apresentado pela Listagem 2.

Listagem 2. Utilização do comando DBCC loginfo.

-- verificação da quantidade de VLF’s DBCC Loginfo GO

Ao executarmos o código apresentado pela Listagem 2, obtemos um retorno da quantidade de VLF’s geradas inicialmente com a criação da tabela de testes que utilizamos no momento. Temos este resultado sendo apresentado pela Figura 3, a seguir.

Figura 3. Verificação da quantidade de VLF’s criadas inicialmente.

Neste momento, não estamos preocupados com o significado de algumas das colunas que nos foram retornadas, basta notarmos aqui que oito linhas foram retornadas o que significa que temos aqui oito VLF’s, como apresentado pela Figura 3. Agora, para complementar o exemplo e vermos a quantidade de VLF’s que serão geradas, vamos utilizar o exemplo apresentado no artigo “Uma abordagem geral sobre os logs de transação”, onde executamos um script para a geração de 500.000 registros aleatórios na base de dados, este script será apresentado através da Listagem 3. Em seguida, executaremos novamente o comando DBCC loginfo, como apresentado pela Listagem 4.

Listagem 3. Geração aleatória de 500 mil registros na tabela TestDB.

Agora que executamos o código apresentado pela Listagem 3, realizaremos agora a análise da quantidade de VLF’s geradas no processo para esta tabela, isto será apresentado de acordo com a Listagem 4, a seguir.

Listagem 4. Nova análise da quantidade de VLF’s geradas para a tabela TestDB.

Como resultado para esta análise, obtivemos um total de 31 linhas retornadas, o que significa que foram geradas 31 VLF’s no processo. As propriedades de crescimento herdadas do banco de dados modelo apresentam um tamanho pequeno inicialmente destes arquivos de log, então obtemos o crescimento também em incrementos relativamente pequenos. Estas propriedades são inadequadas para um banco de dados sujeito a este tipo de atividade e pode levar inclusive à criação de um grande número de VLF’s. Vejamos então de acordo com a Figura 4 como ficou o resultado da segunda análise.

Figura 4. Geração do resultado da quantidade de VLF’s com dados inseridos na tabela TestDB.

Percebam inclusive, de acordo com a Figura 4, com relação ao status obtido nesse último processo, comparado com o primeiro caso, apresentado pela Figura 3, onde neste momento obtivemos que o status de operação aqui é igual a 2, o que nos diz que este espaço não poderá mais ser utilizado. Diferente do apresentado pela Figura 3, onde o status continuava como 0.

O log de fragmentação do arquivo pode ter um impacto considerável sobre o desempenho, especialmente com relação a recuperação de falhas, restaurações, logs de backup, dentre outras possíveis utilidades, em outras palavras, as operações que realizam a leitura do arquivo de log. Mais adiante trataremos com relação ao dimensionamento crescente do log de transação, e mostraremos como evitar a fragmentação dimensionando corretamente o arquivo de log. No entanto, para que tenhamos uma ideia do impacto causado, um estudante da área de banco de dados, também escritor, conhecido como Linchi Shea, demonstrou um enorme efeito com relação ao desempenho de modificações de dados ao comparar um banco de dados com 20.000 VLF’s com um contendo 16 VLF’s. Numa última análise, a causa de um número relativamente "razoável" de VLF’s num arquivo de logs vai depender bastante do tamanho do registo utilizado. Em geral, a Microsoft considera a criação de aproximadamente 200 VLF’s como sendo uma possível causa para preocupações, mas em um arquivo de log muito grande (digamos, 1 TB), apresentando apenas 300 VLF’s também poderia ser considerado um problema, com os VLF’s demasiadamente grandes e limitando assim a reutilização do espaço. Mas com relação a este ponto, um ótimo artigo a ser visto, pode ser o artigo de Kimberly Tripp "Transaction Log VLF – Too Many or Too few?" que discute essa questão com mais detalhes, o link estará disponível no fim do artigo para quem tiver interesse em aprimorar este conhecimento.

Neste artigo, nós procuramos passar as informações mínimas com relação a arquitetura do log de transações que é bastante necessário para compreendermos as questões básicas e problemas em potencial, relacionados com o truncamento, a reutilização do espaço e a fragmentação, ocorrida em arquivos de log. Esperamos que tenham gostado e até a próxima!

LINKS

Kimberly Tripp: http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/