SQL q & R: Histórias sobre transações

Artigo
08/19/2016

Os logs de transações são críticos aos esforços de backup, mas você deve garantir que estão configurados corretamente e compensação quando é suposto para limpar.

Paul S. Randal

Limpar os Logs

**P.**Eu li que no modelo de recuperação completa, transação de log compensação só ocorre quando há um backup de log de transação. Eu estou vendo um comportamento estranho, embora. Às vezes os backups de log não limpar o log. Parece ser os backups de dados que estão fazendo isso. O que pode estar acontecendo?

**R.**Você está correto: nos modelos de recuperação completa e bulk-logged, só pode ser apagado do log de transações (porções do log são marcadas como reutilizáveis) quando há um backup de log de transação. No modelo de recuperação simples, é um ponto de verificação que limpa o log de transações. Para mais fundo no log e recuperação em geral, consulte meu artigo da TechNet, "Noções básicas sobre logs e recuperação no SQL Server."

No entanto, há uma torção. Embora a operação de limpeza de log de transação ocorrerá no final de um backup de log de transação, não há nenhuma garantia de que quaisquer partes do log de transações serão realmente limpo. O fato de que uma parte do log de transações foi feita o backup não é suficiente para deixá-lo ser desmarcada. SQL Server não tem necessidade dessa parte do log de transações para qualquer outra finalidade em qualquer outro momento.

SQL Server ainda podem exigir acesso a uma parte do log de transações porque um backup de dados (como um backup de banco de dados completo) está em execução. Um backup de dados deve incluir parte do log de transações. Ele terá, pelo menos, os dados de log de transação gerados enquanto os dados está sendo copiados do banco de dados. Ele ainda pode precisar mais.

Isso significa que, durante a execução de um backup de dados, log de compensação não pode ocorrer. Isso é verdadeiro mesmo se ocorre um backup de log de transações simultâneas (backups de log e dados simultâneos tem sido possíveis desde o SQL Server 2005).

Neste caso especial, a parte de cópia de segurança do SQL Server "lembra" lá tem sido um backup de log de transação e se lembra quais partes do log de transações foram feitos o backup. Quando for concluído o backup de dados simultâneos, SQL Server pode limpar as porções de log de transação que foram acompanhadas do backup de log de transação (contanto que outra coisa não precisa desse log de transações, como uma transação não confirmada de longa duração).

Esse comportamento é chamado de truncamento de log diferidos. Esse é o comportamento que você está enfrentando. Parece que o backup de dados é limpar o log de transação, mas é realmente um artefato de backups de log de transações simultâneas.

Espelho, espelho

**P.**Estamos em processo de implementação de espelhamento de banco de dados para proteger contra perda de dados para um de nossos bancos de dados. Você pode me dizer o que nós deve monitorar para garantir que nossa espelhamento de banco de dados está funcionando corretamente?

**R.**Ao implementar o espelhamento de banco de dados, você absolutamente deve monitorar o tamanho da fila de envio e a fila de restauração. Estas diretamente relacionam à perda de dados e o tempo de inatividade, respectivamente.

A fila de envio controla quanto log de transações de banco de dados principal ainda não foram enviado para o servidor espelho. Esta parte do log de transações descreve alterações para o banco de dados principal que seriam perdidos caso um desastre processado não está disponível.

Muitas pessoas pensam que se você configurar o espelhamento de banco de dados para segurança alta ou alta disponibilidade (também conhecido como síncrono de espelhamento), a fila de envio sempre será zero. Uma transação no banco de dados principal não pode cometer até que todos os logs de transação para a transação foram enviados para o servidor espelho. No entanto, isso não é verdade. É possível, em determinadas circunstâncias, para os servidores principal e espelho perder o contato com os outros e o banco de dados principal para permanecer online. Neste caso, a fila de envio vai começar a crescer. Isso aumenta o risco de perda de dados. Perda de dados é garantida apenas quando o estado de espelhamento é sincronizado.

A fila de restauração controla quanto log de transação no servidor espelho ainda não foram repetido no banco de dados espelho. É um equívoco comum que quando o servidor principal envia o log de transações para o servidor espelho, é também imediatamente repetido no banco de dados espelho. Isto, também, não é verdade. Tudo que é necessário é que o log de transações é escrito para armazenamento durável no servidor espelho.

Isso significa que, dependendo do hardware de servidor espelho (incluindo seu subsistema de I/O, qualquer carga de trabalho simultânea e outros fatores que afetam o desempenho), pode haver uma fila de log de transação considerável que ainda não tenha sido repetida no banco de dados espelho.

Quando há um failover de espelhamento, banco de dados espelho não virá on-line até que o log de transações tem sido repetido. Isto é aproximadamente proporcional à quantidade de log de transações un-replayed. Ele também representa o tempo de inatividade que do aplicativo e seus usuários vão experimentar quando ocorre um failover de espelhamento.

Você também deve ter cuidado sobre o tamanho da fila de restauração se você estiver indo para usar instantâneos de banco de dados do banco de dados espelho para fins de relatório. Quando você cria um instantâneo do banco de dados, recuperação de banco de dados deve processar o log de transações pendentes para que o database snapshot é uma exibição consistente transacional do banco de dados subjacente. Quanto maior a fila de restauração, mais tempo que levará para criar o instantâneo de banco de dados. Também afetará o desempenho do servidor espelho, que pode fazer a fila de refazer a crescer.

Existem várias outras métricas que você deve monitorar, como a latência de rede entre os servidores principal e espelho. Isso se traduz em sobrecarga por transação de implementar o espelhamento síncrono. Você pode usar o Monitor de desempenho (sys.dm_os_performance_counters exibição de gerenciamento dinâmico [DMV]) para controlar essas métricas. Você também pode usar a ferramenta Monitor de espelhamento de banco de dados embutido no Management Studio e descrito em sql Server Books Online. A ferramenta permite que você facilmente criar alertas com base em limites de tamanho de fila.

Benefícios no desempenho

**P.**Eu tenho sido avisado que eu deveria ter vários arquivos de dados para ajudar com desempenho, e entender o porquê. Agora eu estou sendo dito eu também deveria ter vários arquivos de log de transações para que SQL Server possa fazer mais eficientes operações de e/S para o log de transações. Está correto?

**R.**Não, isso não é correto. Infelizmente, eu ouço esta recomendação de vez em quando.

Vários arquivos de dados podem ajudar com contenção de subsistema de I/O. Em alguns casos (geralmente com tempdb), eles também podem ajudar a contenção em estruturas de alocação de banco de dados que estão na memória. Há todos os tipos de orientações sobre quantos arquivos de dados para criar.

A recomendação para que vários arquivos de log de transações é extrapolada da mesma recomendação para arquivos de dados, mas é incorreta. Vários arquivos de log de transações não fornecem qualquer ganho em desempenho, disponibilidade, escalabilidade ou qualquer outra métrica mensurável.

O log de transações é e deve ser seqüencial na natureza para que SQL Server não executar e/SS paralelos no log de transações se há vários arquivos de log de transações atual. O primeiro arquivo será usado na sua totalidade, então o segundo arquivo, então o terceiro e assim por diante. Isso vai acontecer até que o log de transações envolve e começa novamente o primeiro arquivo de log de transação. Assim os arquivos de log extras não fornecem nenhum ganho.

Há uma situação onde um segundo arquivo de log de transação pode ser necessária. Se o primeiro arquivo de log de transação é completo, o log de transações não pode limpar (ver a resposta à primeira pergunta para uma explicação adicional). O primeiro arquivo de log não pode crescer para acomodar mais registros de log de transação. Nesse caso, adicionar um segundo arquivo de log de transação temporariamente permitirá modificações de banco de dados continuar até que o primeiro arquivo de log de transação pode limpar.

Reduzir e/S, aumentam o desempenho

**P.**Estamos já a investigar o uso de discos de Estado sólidos (SSDs) para tentar reduzir alguns dos nossos problemas de desempenho de e/S, mas estamos confusos sobre quais bancos de dados para colocar sobre eles. Você pode nos dar algumas orientações sobre a melhor forma de usar SSDs?

**R.**Esta é uma pergunta interessante. Há uma variedade de respostas "definitivas" você verá em fóruns de ajuda de Internet, tais como, "sempre coloque o tempdb em seu SSD" ou "sempre colocar as logs de transações em seu SSD." Nenhum dos casos é apropriado.

Existem alguns factores a ter em conta quando se considera a melhor forma de usar SSDs com SQL Server:

SSDs são caro, assim que você quer se certificar de que você está recebendo o melhor ROI deles.
SSDs fornecer o maioria dos ganho de desempenho para cargas de trabalho de I/O aleatórios, as cargas de trabalho de I/O não seqüenciais.
Para qualquer parte de um subsistema sobrecarregado de I/O, um SSD irá fornecer um aumento de desempenho — independentemente do padrão I/O — devido a sua natureza de vastamente reduzindo latência de leitura e gravação.
Conexão direta SSDs deve fornecer um aumento de desempenho mais profundo do que aqueles acessados por qualquer tipo de tecido de comunicações.
Os logs de transações são gravação seqüencial, com maioria leituras seqüenciais (e algumas leituras aleatórias se existe um grande potencial para a reversão de transações).
Tempdb pode ser muito levemente usado no SQL Server. Mesmo se eles são moderadamente usados, eles não podem experimentar uma elevada quantidade de atividade de gravação de arquivo de dados.

Quando você considerar esses fatores, você percebe que colocar os logs de transação ou tempdb no seu SSDs pode não ser a melhor maneira de usá-los. Identifica as partes de subsistema de I/O que são o maior gargalo de desempenho para sua carga de trabalho. Estes também podem ser arquivos de dados para um banco de dados de processamento (OLTP) voláteis transações on-line ou um log de transações para um banco de dados com uma carga de trabalho pesada inserir — ou eles podem muito bem ser tempdb. Estes são os candidatos para colocação no seu SSDs, ao invés de escolher apenas uma parte do armazenamento do SQL Server sem realizar qualquer investigação.

Outra peça de maus conselhos sobre SSDs é que você pode parar de ser preocupado com a fragmentação do índice quando usando SSDs para armazenar arquivos de dados. Isso absolutamente não é o caso. É verdade que os efeitos da leitura menos eficiente antes de fragmentação serão atenuados um pouco por muito reduzida latência de leitura, mas há ainda o custo de e/S mais do que o necessário. Você pode reduzir que, abordando a fragmentação.

O que a maioria das pessoas não consideram é que a fragmentação não é apenas sobre Leia mais à frente. Um dos efeitos colaterais do que faz com que a fragmentação (operações chamadas "page divisões") é que a porcentagem de espaço não utilizado em páginas de arquivo de dados pode aumentar drasticamente — de 40% a 50% (isto é chamado "densidade de página").

Se uma grande parte dos índices do banco de dados é fragmentada, em seguida, sem manutenção do índice regular as páginas de arquivo de dados vão contêm uma grande quantidade de espaço vazio. Além de reduzir a eficiência de e/S e uso de memória, que também significa você está usando SSDs caro para armazenar o espaço vazio. Isso não é um bom ROI em livro de ninguém.

Paul Randal

**Paul S. Randal**é o diretor administrativo SQLskills.com, diretor regional da Microsoft e MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server na Microsoft desde 1999 a 2007. Ele escreveu o DBCC CHECKDB/repair para SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Randal é especialista em recuperação de desastre, alta disponibilidade e manutenção de bancos de dados, e participa regularmente de conferências em todo o mundo. Ele bloga em SQLskills.com/blogs/paul e você pode encontrá-lo no Twitter em Twitter.com/PaulRandal.