O log de transações do SQL Server é o mecanismo que mantém os registros de alterações que ocorrem nas páginas de dados de um database. O seu uso correto pode trazer bons ganhos de desempenho. Neste contexto, este artigo apresenta as melhores práticas para se trabalhar com transaction log do SQL Server 2008 R2 discutindo formas de torná-lo eficiente e performático.
Em que situação o tema útil:
Em ambientes críticos que possuem por característica muitas transações de escrita e leitura, manter o transaction log configurado corretamente e bem dimensionado para obter o máximo de desempenho possível do ambiente é tarefa básica para um DBA. Nesse artigo vamos mostrar como configurá-lo e dimensionar o hardware correto para os arquivos .ldf do seu servidor.
Resumo DevMan
Análise de desempenho com foco no transaction log é uma operação que deve ser tratada com atenção e habilidade. O ideal é que o profissional entenda tanto de banco de dados quanto de infraestrutura, sendo capaz de configurá-lo corretamente e dimensionar o melhor hardware possível para as operações que nele são realizadas. Após a leitura desse artigo, o profissional estará apto a investigar os possíveis gargalos existentes em seu ambiente e aplicar as boas práticas recomendadas para o produto.
O log de transações do SQL Server é o mecanismo que mantém os registros de alterações que ocorrem nas páginas de dados de um database. Fisicamente é representado por um arquivo com a extensão .ldf e tem como característica ser passível de muitas operações de escrita em conjunto com o "buffer manager". O buffer manager possui várias funções na arquitetura do SQL Server e uma delas é garantir que as alterações nas páginas de dados em memória sejam salvas primeiramente no log de transações e posteriormente no arquivo de dados MDF.
O processo de escrita dos dados contidos no arquivo de log para o arquivo de dados é realizado de forma assíncrona e os processos internos que são executados em background são: checkpoint e lazywriter (ler Notas DevMan 1 e 2). Esses processos são executados de forma automática pela engine do SQL Server e podem ser visualizados através da DMV (sys.dm_exec_requests) conforme Listagem 1 (ler Nota DevMan 3).
Checkpoint é o
serviço responsável por tirar as páginas modificadas e comitadas em memória
para o disco e posteriormente sincronizar os dados que estão no arquivo .LDF
para o arquivo .MDF.
O LazyWriter realiza um trabalho parecido
com o checkpoint, que é gravar as páginas modificadas em memória para o disco.
A diferença básica entre eles é que o LazyWriter atua quando ocorre pressão no
datacache. Durante sua execução o data cache (buffer pool) é recalculado e a
memória é liberada para outros processos.
Incorporadas no SQL 2005, as DMVs são views com
informações referentes ao estado atual do servidor de SQL Server. São
utilizadas para monitorar processos, índices, bloqueios, diagnosticar problemas
e ajudar na análise de desempenho do banco de dados.
Quando um registro é inserido no arquivo de log, ele automaticamente é registrado com um número sequencial denominado LSN (Log Sequence Number). Sempre que ocorre um checkpoint, o SQL Server se encarrega de registrar o último registro lido pelo Checkpoint e o marca no log como MinLSN (Minimun Log Sequence Number) (ler ...
