SQL Q & a: Sem deixar vestígios
Processos como backup, restauração e verificação de consistência podem causar comportamentos inesperados, apesar de fazerem sentido.
Paul S. Randal
O rigor da restauração
**P.**Eu estou trabalhando os requisitos de tempo de inatividade para alguns dos nossas instâncias do SQL Server como parte do planejamento de recuperação de desastres. É suficiente para considerar apenas o tempo que leva para restaurar backups?
**.**Não, existem algumas outras coisas que você precisa considerar. Primeiro, considere o tempo total que leva para restaurar todos os backups necessários. Que inclui o mais recente backup completo, o backup diferencial mais recente e todos os backups de log de transação. Assuma sempre o pior cenário — onde o banco de dados é destruído antes de tomar o próximo backup completo, assim você tem o maior número possível de backups de log.
Em seguida, considere o tempo adicional que levará para restaurar o backup completo inicial para criar arquivos de log de transações e dados se eles já não existem. Se você habilitou a inicialização instantânea de arquivos, em seguida, arquivos de dados serão criados quase instantaneamente. O arquivo de log de transação, no entanto, deve ser inicializado em zero.
Se você tem um arquivo grande que está em cima de centenas de gigabytes, então uma restauração pode levar várias horas. Se você então tem que restaurar de um backup diferencial, que irá novamente completamente zero-inicializar o arquivo de log de transação. Você terá que contabilizar esse tempo. Se houver qualquer transação adicional arquivos de log que foram temporariamente adicionado (mas não removido) você terá a zero-inicializar estas também — potencialmente duas vezes.
A última fase do processo de restauração de banco de dados é executar uma recuperação de falhas. O tempo necessário para isto dependerá de quantos registros de log de transação, você precisará reverter. Eles são parte de transações não confirmadas no momento do backup de log final. Se você tiver transações de longa duração em seu banco de dados, assuma o pior. Suponha que você vai ter que reverter-se de quase todas as transações mais longo possível. Você precisará adicionar esse tempo à equação.
Finalmente, considere também quanto tempo demora o servidor físico para chegar ao ponto onde você pode começar a restauração de backups. Em outras palavras, quanto tempo leva o servidor de arranque (POST, verificações de memória em execução e assim por diante) e iniciar o Windows? Isso também poderia adicionar o tempo de inatividade.
Se você considerar todas essas coisas em seus casos piores, o que lhe dará um tempo de inatividade máximo possível. Você pode se surpreender quando você somar tudo.
Não interrompa
**P.**Recentemente, deparei com um problema interessante. Eu tentei interromper um processo de DBCC CHECKDB foi levando mais tempo do que o habitual. Achei que eu não poderia interromper-se e teve que esperar muito tempo para o processo terminar. Você pode explicar o que estava acontecendo?
**.**Esse comportamento é esperado, mas não intuitivo em tudo. Quando DBCC CHECKDB é iniciado, ele cria um instantâneo do banco de dados ocultos. O instantâneo de banco de dados é necessária para fornecer o DBCC CHECKDB com uma visão imutável transacionalmente consistente do banco de dados. Dessa forma, o DBCC CHECKDB sabe está verificando a consistência de um banco de dados estático que não deve ter corrupções.
O processo cria um instantâneo do banco de dados, primeiro ponto de verificação do banco de dados. Em seguida, ele cria o instantâneo de banco de dados vazio e usa o log de transações do banco de dados para executar recuperação de falhas sobre o instantâneo de banco de dados. Em outras palavras, ele reverte quaisquer transações ativas para o instantâneo de banco de dados sem realmente afetar o banco de dados real. O instantâneo de banco de dados torna-se transacionalmente consistente.
O tempo que leva para executar recuperação de falhas, enquanto criar o instantâneo de banco de dados é proporcional à quantidade e comprimento das transações não confirmadas no banco de dados ao iniciar o instantâneo de banco de dados. Se houver uma transação de longa duração, ele pode demorar muito tempo para reverter. Isso significa criar o instantâneo de banco de dados e o processo de DBCC CHECKDB levará mais tempo.
Em casos extremos, quando criar o instantâneo de banco de dados leva muito mais tempo do que o normal e você decide matar o processo de DBCC CHECKDB, nada acontecerá imediatamente. Você tem que esperar para a recuperação de falhas de instantâneo de banco de dados concluir antes do processo irá responder ao sinal de matar. Você não pode interromper a recuperação de falhas, e não sem distinção o código de recuperação de falha no SQL Server entre recuperação de falha real após um desligamento inesperado e uma recuperação de um instantâneo de banco de dados.
A única alternativa nesse caso é de reiniciar a instância do SQL Server, que irá remover o instantâneo de banco de dados ocultos. Isso não funciona no caso de uma recuperação de falhas real de banco de dados normal. Nesses casos, a recuperação continuará após reiniciar uma instância.
Existem várias maneiras que você pode evitar esse cenário. Tente apenas executar DBCC CHECKDB quando você sabe que não há nenhuma transação de longa duração ativo no banco de dados. Você teria que ter estes laminados volta como parte de criar o instantâneo de banco de dados ocultos de DBCC CHECKDB. Você também pode usar um mecanismo de verificação de consistência, que é restaurar o banco de dados para outro servidor e verifique consistência com cópia restaurada. Isso evita a possibilidade de transações de longa duração no total.
Encontrar o momento certo
**P.**Na semana passada eu tive que restaurar backups para salvar uma tabela alguém acidentalmente caiu. O rastreamento de padrão já havia perdido as informações sobre quando a tabela foi descartada, então foi um processo tedioso para encontrar a posição de backup para que eu precisava para restaurar. Existe uma maneira de encontrar o ponto certo no tempo a que deve restaurar?
**.**Qualquer momento que você está tentando determinar quando uma tabela foi descartada, confira o traço padrão. Que faz a observação de eventos de linguagem de definição de dados (DDL). Você pode ler mais sobre o rastreamento padrão no SQL Server Books Online.
O único problema com rastreamento padrão é que é um tamanho finito. Ele também foi preterido em favor de eventos estendidos do SQL Server 2012. Assim se há muita atividade, ocorrendo no servidor, o registro de quando a tabela foi descartada pode não existir no rastreamento mais.
Isso significa que a única maneira de encontrar quando a tabela foi descartada é fazer o que eu chamo "avançando através do log de transações." Restaure uma cópia do banco de dados para um tempo quando a tabela foi conhecida a existir. Então repetidamente execute restaurações point-in-time, usando as opções WITH STOPAT e com modo de espera. Avançar um pouco no tempo de cada vez. Quando você encontrar o tempo quando a tabela não existe mais, restaurar o banco de dados imediatamente antes que o tempo e você pode recuperar os dados da tabela.
Este processo é muito tedioso e pode levar um longo tempo. Cada vez que você restaurar o banco de dados usando com modo de espera, todas as transações não confirmadas nesse ponto são revertidas em um arquivo de desfazer. Desfaz o desfazer, restaura um pouco mais e novamente reverte transações não confirmadas para o arquivo desfazer a restauração próxima no processo. Você tem que repetir esse processo até encontrar a hora correta.
Há uma elegante alternativa para fazer isso. Analise os registros de log nos backups de log de transação para procurar transacções chamadas DROPOBJ. Fazer isso com uma função com valor de tabela sem documentos chamada fn_dump_dblog. Isso se comporta da mesma forma como o fn_dblog mais amplamente conhecido, que despejos de log de registros em um log de transação ativa, trabalhando contra um backup de banco de dados.
Você pode usar esta função para encontrar a transação que caiu o objeto no qual você está interessado. Em seguida, você pode usar a transação número de seqüência de Log (ou LSN) para executar uma restauração com STOPATMARK = ' lsn: < o LSN da transação >'. Isto irá restaurar o log de transações, mas não inclusive, a transação que caiu da mesa. Fazê-lo desta forma poupa você de ter a "polegada através do registro," como descrita anteriormente. Você pode ler mais sobre essa função e como usá-lo no meu blog.
Filtragem de eventos
**P.**Agora que o rastreamento do SQL foi preterido no SQL Server 2012, gostaria de entender mais sobre eventos estendidos. Você pode explicar como os eventos estendidos são suposto para ser mais leve do que o rastreamento do SQL?
**.**A principal razão para o desempenho diferencial entre os dois mecanismos é como os eventos são filtrados. Ao definir uma sessão de rastreamento ou evento, você pode filtrar eventos em ambos os casos com base em vários critérios de evento. Filtragem em atividade em um determinado banco de dados é um bom exemplo disso.
Com rastreamento do SQL, os eventos são gerados o tempo todo. O consumidor do evento faz a filtragem. Isso significa que SQL Server está sobrecarregado com a geração de todos os eventos, mesmo que alguns não vai ser consumido. Este processo é muito ineficiente.
Com eventos estendidos, o mecanismo de eventos estendidos do SQL Server executa a filtragem de evento. O mecanismo de eventos estendidos avalia os predicados especificados quando definiu-se a sessão de evento. Isso significa que quando o evento é acionado, somente o mínimo de trabalho é necessário recolher dados de base do evento. Isso permite que o mecanismo de eventos avaliar o predicado. Se o predicado for avaliada como false, o evento é imediatamente Descartado. O mecanismo de eventos não realiza nenhum processamento adicional. Isso minimiza a sobrecarga de desempenho de coleta de eventos quando comparado com o rastreamento do SQL.
Também, SQL Trace recolhe todas as colunas, associadas a um evento e descarta todas as colunas não é necessárias. Por outro lado, eventos estendidos, só recolhe a colunas e outros dados especificados. Isso minimiza ainda mais o esforço necessário para acionar um evento.
Embora eventos estendidos é um mecanismo muito superior para coleta de dados de solução de problemas, ele ainda negativamente pode afetar o desempenho do SQL Server se a sessão de evento não é cuidadosamente construída. Se uma sessão de evento requer produzir uma pilha de chamadas de T-SQL, cada vez que um evento muito comum acontece (como adquirir um bloqueio ou uma espera do segmento), isso obviamente vai afetar desempenho.
Com qualquer mecanismo, você deve testar coleta de eventos antes de colocá-lo em produção. Você precisa garantir que o desempenho da carga de trabalho não vai ser comprometido.
.jpg)
Paul S. Randal é diretor-gerente da SQLskills.com, diretor regional da Microsoft e MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server na Microsoft de 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 é um especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados e é apresentador regular em conferências em todo o mundo. Blogs de He em SQLskills.com/blogs/paul, e você pode encontrá-lo no Twitter em Twitter.com /PaulRandal..