P+R SQLLogs de transações grandes, quando usar Reparar e muito mais

Paul S. Randal

P Notei um comportamento estranho com backups que espero que você possa me explicar. Periodicamente, fazemos o backup de nosso banco de dados de produção de 62 GB para atualizar os dados usados por nossos desenvolvedores de aplicativos. Sempre excluímos a cópia antiga antes de restaurar a nova. A cópia restaurada é do mesmo tamanho do banco de dados de produção e os dados parecem os mesmos, mas o processo de restauração leva um tempo bem maior do que o processo de backup. O que está havendo? Por que se leva muito mais tempo para restaurar do que para fazer o backup?

R Realmente não há nada estranho acontecendo. Dependendo das suas circunstâncias, esse geralmente é um comportamento esperado. A diferença no tempo necessário para um backup em relação a uma restauração provém das etapas que cada processo deve executar.

Fazer o backup de um banco de dados compreende duas etapas. Basicamente, é simplesmente fazer E/Ss de leitura no banco de dados e E/Ss de gravação no dispositivo de backup:

Etapa 1 do backup Leia todos os dados alocados nos arquivos de dados e grave-os no dispositivo de backup.

Etapa 2 do backup Leia algum log de transação e grave-o nos dispositivos de backup.

A quantia exata do log de transação necessária pode variar muito, mas é realmente a quantia necessária para poder recuperar o banco de dados restaurado para um momento consistente. (Eu forneci uma explicação mais aprofundada sobre isso em uma postagem do blog, disponível em sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx.)

Restaurar um banco de dados, por outro lado, pode abranger até quatro etapas. E o trabalho envolvido é mais complicado do que apenas ler e gravar E/Ss:

Etapa 1 da restauração Se os arquivos do banco de dados não existem, crie-os.

Etapa 2 da restauração Leia todos os dados e o log de transação do backup e grave-os nos arquivos relevantes do banco de dados.

Etapa 3 da restauração Execute a fase REDO de recuperação no log de transação.

Etapa 4 da restauração Execute a fase UNDO de recuperação no log de transação.

O tempo total decorrido para ambas as etapas do backup deve ser aproximadamente o mesmo que o tempo necessário para a Etapa 2 da restauração (supondo que ele possui hardware similar e um servidor sem atividade do usuário). A etapa 1 da restauração pode levar um tempo longo se os arquivos de dados forem grandes e precisarem ser inicializados com zero (que é o comportamento no SQL Server 2000 e o comportamento padrão no SQL Server 2005).

Para impedir isso, não exclua os arquivos existentes antes de iniciar a restauração. Ou, como alternativa, habilite a inicialização instantânea para que os arquivos sejam criados muito rapidamente (mais informações sobre isso podem ser encontradas em msdn.microsoft.com/library/ms175935.aspx).

As etapas 3 e 4 da restauração estão executando a recuperação no banco de dados restaurado para torná-la transacionalmente consistente; esse é o mesmo processo pelo qual um banco de dados passaria durante a recuperação contra panes. A duração de tempo que a recuperação levará depende da quantia de log de transação que precisa ser processada. Por exemplo, se houvesse uma transação ativa de muito longa duração no momento em que o backup foi feito, todo o log de transação para aquela transação estaria no backup e teria que ser revertido.

P Estou tentando escolher entre o envio de log e o espelhamento do banco de dados para fornecer uma cópia redundante do nosso banco de dados de produção. Estou preocupado com a quantia de logs de transação que precisará ser enviada entre os servidores, especialmente para as operações de recompilação de índice que fazemos todas as noites. Ouvi dizer que o espelhamento envia os verdadeiros comandos de recompilação em vez do log de transação e as recompilações são feitas no espelho. Isso é verdade? Isso deveria tornar o espelhamento uma solução melhor do que, até mesmo, o envio de log com o modelo de recuperação BULK_LOGGED, certo?

R O que você ouviu não é verdade. O espelhamento do banco de dados funciona pelo envio dos registros de log de transação reais do banco de dados principal ao servidor de espelhamento, onde eles são “reproduzidos novamente” no banco de dados de espelhamento. Não existe nenhuma conversão ou filtragem de tipo algum acontecendo, nem qualquer tipo de interceptação de comandos T-SQL para um banco de dados espelhado.

O espelhamento de banco de dados suporta apenas o modelo de recuperação FULL, o que significa que uma operação de recompilação de índice sempre será totalmente registrada. Dependendo do tamanho dos índices em questão, isso poderia significar uma quantia relevante de log de transação sendo gerado e, sucessivamente, um grande arquivo de log no banco de dados principal e uma largura de banda de rede considerável para envio dos registros de log ao espelho.

Você pode pensar no espelhamento do banco de dados como envio de log em tempo real (na verdade, esse era um nome usado para o recurso no início durante o desenvolvimento do SQL Server 2005). No envio de log, os backups do log de transação do banco de dados primário são enviados regularmente ao servidor secundário e são restaurados no banco de dados secundário.

O envio de log suporta os modelos de recuperação FULL e BULK_LOGGED. Para uma operação de recompilação de índice em um banco de dados com envio de log com o modelo de recuperação FULL, a mesma quantia de log de transação será gerada como a quantia gerada para um banco de dados espelhado. No entanto, no cenário de banco de dados com envio de log, os dados são enviados ao banco de dados redundante em um backup de log (ou uma série de backups de log), em vez de como um fluxo contínuo.

Se o modelo de recuperação BULK_LOGGED for usado no banco de dados com envio de log enquanto a recompilação de índice for feita, apenas uma quantia mínima de log de transação será gerada. No entanto, o próximo backup de log de transação também irá conter todas as extensões de arquivos de dados que foram alteradas pela operação de recompilação de índice minimamente registrada. Isso significa que os backups de log que abrangem a recompilação de índice no modelo de recuperação BULK_LOGGED serão quase que exatamente do mesmo tamanho daqueles que abrangem a recompilação de índice no modelo de recuperação FULL.

Então, a quantia de informação que precisa ser enviada ao banco de dados redundante é quase exatamente a mesma que para uma recompilação de índice em um banco de dados espelhado e em um banco de dados com envio de log. A diferença real é de como as informações são enviadas — continuamente ou em lotes.

Existem vários outros fatores a considerar ao escolher entre essas duas abordagens (muitos fatores para se discutir em apenas uma edição do P+R SQL). Você deveria ver como todos esses fatores se correspondem aos seus requisitos (como limite de perda de dados aceitável e tempo de inatividade permitido) antes de tomar uma decisão.

P Estou executando o SQL Server 2005 e um dos meus bancos de dados possui um log de transação que simplesmente continua aumentando. O banco de dados está no modo de recuperação completo e estou fazendo backups de log de transação. Eu tinha a impressão de que isso deveria impedir o log de transação de aumentar. O que exatamente está dando errado aqui?

R Você tem razão em achar que fazer backups de log de transação no modo de recuperação completo é importante. No entanto, existem outros fatores que podem contribuir com o aumento do log de transação. Tudo se resume ao que está exigindo que o log de transação seja necessário (ou ativo). Outros fatores comuns (com exceção de uma falta de backups de log de transação) que podem causar seu problema incluem a replicação, o espelhamento do banco de dados e uma transação ativa.

A replicação funciona pela leitura assíncrona dos seus registros de log de transação e, em seguida, pelo carregamento das transações a replicar em um banco de dados de distribuição separado. Qualquer registro de log de transação que ainda não tenha sido lido pela tarefa do leitor de log de replicação não pode ser liberado. Se sua carga de trabalho gerar muitos registros de log de transação e você tiver definido um intervalo grande para a freqüência com a qual o leitor de log de replicação será executado, muitos registros poderão ser acumulados e fazer com que o log de transação aumente.

Se estiver executando o espelhamento de banco de dados assíncrono, pode haver uma lista de pendências de registros de log de transação não enviada do principal ao espelho (chamada de fila SEND do espelhamento do banco de dados). Os registros do log de transação não podem ser liberados até terem sido enviados com sucesso. Com uma alta taxa de geração de registro de log de transação e largura de banda limitada na rede (ou outros problemas de hardware), a lista de pendências pode aumentar muito e fazer com que o log de transação aumente.

Finalmente, se um usuário inicia uma transação explícita (como usar uma instrução BEGIN TRAN) e, em seguida, faz uma modificação de algum tipo (como uma instrução DDL ou uma ação de inserir/atualizar/excluir), os registros de log de transação gerados precisarão ser mantidos até que o usuário confirme ou reverta a transação. Isso significa que qualquer registro de log de transação subseqüente gerado por outras transações também não pode ser liberado, uma vez que o log de transação não pode ser liberado de modo seletivo. Se esse usuário, por exemplo, for embora sem concluir a transação, o log de transação continuará aumentando quanto mais registros de log de transação forem gerados, mas não poderá ser liberado.

Você pode descobrir por que o log de transação não pode ser liberado consultando o modo de exibição de catálogo do sistema sys.databases e examinando a coluna log_reuse_wait_desc, desta forma:

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

Se a causa vier a ser uma transação ativa, você poderá usar a instrução DBCC OPENTRAN para obter mais informações sobre a transação:

DBCC OPENTRAN ('dbname')

P Ouvi dizer que REPAIR_ALLOW_DATA_LOSS deve ser usado apenas como um último recurso para recuperar da corrupção; a restauração de backups deve ser usada em seu lugar. Você pode explicar por que o "reparo para o SQL Server 2005" não deve ser usado e por que ele está no produto, considerando o quanto ele é "perigoso"?

R Primeiramente, eu realmente escrevi reparo para o SQL Server 2005. O problema com REPAIR_ALLOW_DATA_LOSS (que eu chamarei apenas de reparo daqui em diante) é que ele não é claro sobre como funciona. O nome da opção de reparo foi escolhido para explicar que executá-la poderia levar à perda de dados do banco de dados. A maneira com a qual o recurso normalmente repara uma estrutura de banco de dados corrompida é excluir a estrutura corrompida e, em seguida, corrigir todo o resto no banco de dados que consultou ou foi consultado pela estrutura excluída. O reparo realmente pretende ser um último recurso para deixar o banco de dados estruturalmente consistente — seu foco não é salvar dados do usuário. O reparo não sai do seu caminho para excluir dados do usuário, mas ele também não sai do seu caminho para salvar dados do usuário.

Isso pode parecer como um modo irresponsável de conduzir reparos, mas quando a opção de recuperação precisa ser usada, ela fornece o método mais rápido e confiável para corrigir a corrupção. A velocidade é de importância suprema em uma situação de recuperação de desastres e a precisão é absolutamente necessária. É quase impossível planejar algoritmos de reparo mais complexos que podem ser comprovados por funcionarem de modo rápido e correto em todos os casos. Existem alguns algoritmos complexos no código de reparo para resolver o caso em que dois índices possuem a mesma página ou extensão alocada, por exemplo, mas, na maioria das vezes, o algoritmo é de reparo e correção.

Ainda assim, existem alguns problemas com o reparo sobre os quais você deve estar ciente:

• O reparo não considera restrições de chave estrangeira ao excluir estruturas corrompidas, então ele pode excluir registros de uma tabela que possua uma relação de chave estrangeira com outra tabela. Não há como determinar se isso ocorreu sem a execução de DBCC CHECKCONSTRAINTS após executar o reparo.
• O reparo não considera (e não pode considerar) nenhuma lógica de negócios inerente ou relação de dados definidas no nível do aplicativo que poderia ser quebrada por algum dado que estivesse sendo excluído. Novamente, não há como determinar se algo está quebrado sem executar qualquer verificação de consistência personalizada que esteja incorporada no aplicativo.
• Algumas operações de reparo não podem ser replicadas. Executar o reparo em um editor ou nó em uma topologia ponto a ponto pode introduzir inconsistências dentro da topologia, o que deve ser corrigido manualmente.

Por essas razões, é sempre uma boa idéia recuperar da corrupção usando um backup em vez de executar a opção de reparo. Mas o reparo é oferecido no produto porque sempre existe a possibilidade de se entrar em uma situação na qual seu banco de dados está corrompido, não existe backup e você precisa de alguma maneira de colocar o banco de dados online rapidamente.

P Acabei de mudar para uma nova empresa como DBA e sou responsável por vários aplicativos e seus bancos de dados back-end. Um dos aplicativos tem um desempenho muito fraco ao fazer atualizações. Fiz uma investigação e descobri que as tabelas usadas pelo aplicativo possuem um número enorme de índices em cada uma. Após fazer umas perguntas, parece que o DBA anterior tinha o hábito de adicionar um índice da coluna de cada tabela, além de algumas combinações. Não acredito que todos esses índices são necessários — como posso saber quais posso ignorar com segurança? Nós usamos o SQL Server 2005.

R O grande número de índices é bem provavelmente o fator que mais contribui pelo fraco desempenho, como você supõe. Sempre que uma linha é inserida, atualizada ou excluída da tabela, as operações correspondentes precisam ser feitas em cada índice não agrupado. Isso acrescenta muita sobrecarga em termos de E/Ss, utilização da CPU e geração de log de transação.

No SQL Server 2000, a única maneira de determinar quais índices estavam sendo usados era usar a criação de perfis e examinar planos de consulta. Com o SQL Server 2005, existe uma nova DMV (exibição de gerenciamento dinâmico) que controla o uso do índice — sys.dm_db_index_usage_stats.

Essa DMV registra todas as vezes que um índice foi usado (e como ele foi usado), desde quando o banco de dados ao qual ele faz parte foi iniciado. As estatísticas para todos os bancos de dados são perdidas quando o SQL Server é desligado e as estatísticas para um único banco de dados são perdidas quando ele é fechado ou desconectado. A idéia é que se um índice não aparece na saída, significa que ele não foi usado desde quando o banco de dados foi iniciado.

Uma abordagem simples para controlar o uso do índice com o tempo é tirar instantâneos periódicos da saída da DMV e, em seguida, comparar os instantâneos. Uma coisa que várias pessoas deixam escapar é que você deve controlar o uso do índice em um ciclo de negócios inteiro. Se você apenas tirou um instantâneo para um único dia, poderá encontrar vários índices que não são usados. Mas se esses índices forem usados, digamos, para ajudar os relatórios de final de mês a executarem magnitudes com mais rapidez, os índices provavelmente não devem ser removidos. Se um índice realmente não tiver sido usado por um ciclo de negócios inteiro, provavelmente você poderá ignorá-lo e recuperar espaço e desempenho.

Para obter alguns códigos simples que você pode usar para tirar instantâneos periódicos da DMV, consulte a postagem do meu blog em sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx.

Paul S. Randal é diretor administrativo da SQLskills.com e MVP do SQL Server. Paul 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. Especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados, Paul é apresentador regular de conferências. Ele bloga em SQLskills.com/blogs/paul.