SQL Q & A: Reparando o dano
Dê uma olhada em sua seleção do plano de consulta, nos backups excessivamente lentos e no processo de reparação de um banco de dados de replicação.
Paul S. Randal
Planejar sua consulta
**P.**Ao SQL Server é escolher um plano de consulta para executar minhas consultas, leva em conta os dados que estão atualmente na memória?
**.**A resposta simples é que o otimizador de consulta considera nunca o buffer conteúdo piscina ao escolher um plano de consulta. O otimizador de consulta avalia vários planos como ele estreita para baixo as escolhas possíveis. Ele procura o melhor plano pode determinar dentro de um prazo razoável. O otimizador de consulta não sempre identifica o melhor plano de absoluto. O otimizador de consulta não pode gastar uma quantidade excessiva de tempo na compilação do plano, mas ele sempre seleciona um plano de "suficientemente bom".
O otimizador de consulta precisa saber sobre as várias tabelas envolvidas na consulta. Ele encontra essas informações nos metadados de relacional para as tabelas. Os metadados relacional descreve as colunas da tabela, índices e restrições, bem como estatísticas sobre as distribuições de valor nas várias colunas (se estas estatísticas foram criadas). O metadados de armazenamento descreve como a tabela e os índices são realmente armazenados nos arquivos de dados. O otimizador de consulta não usa essas informações para determinar um plano.
SQL Server não acompanhar quais partes de uma tabela e seus índices são em memória a qualquer momento. O pool de buffer rastreia as páginas de arquivo de dados de um banco de dados na memória. No entanto, nada no SQL Server não faz qualquer tipo de agregação automática. Não, por exemplo, será capaz de determinar que 50% do índice dois da tabela X está na memória, Considerando que apenas 5 por cento do índice três da tabela X está na memória.
O otimizador de consulta assume que nada é na memória, então é provável que escolher um plano de consulta que envolve a menor quantidade de i/o físico. Planos com um monte de e/s físicas são time-consuming e caro. Considere uma tabela com dois índices não-clusterizados que poderia satisfazer uma consulta SELECT. O primeiro índice tem todas as colunas necessárias. O segundo índice tem todas as colunas necessárias, além de várias colunas adicionais.
O primeiro índice terá registros de índice menores, assim ele terá mais linhas de índice por página do arquivo de dados. O otimizador de consulta irá escolher esse índice. Acessar os registros de índice necessário para satisfazer que a consulta exigirá leitura menos páginas de arquivo de dados na memória usando i/o físico, em comparação com usando o índice do segundo. O segundo tem registros maiores e menos registros por página do arquivo de dados. Este raciocínio é chamado de otimização baseada em custo, e é como o processador de consultas no SQL Server é projetado.
No entanto, se uma grande parte do segundo, maior índice já está na memória e nenhum do primeiro índice está na memória? A consulta vai exigir física i/o para ler o índice escolhido na memória. Isso será muito mais lento do que usar o índice mais amplo já na memória. Nesse caso, o plano de consulta é realmente de qualidade inferior. No entanto, o otimizador de consulta não tem como saber o que está na memória, e este é apenas um exemplo.
Mesmo com isso em mente, porém, se o otimizador de consulta reconheceu o que estava na memória e gerado um plano que usou o índice menos eficiente porque ele já estava na memória? Esse plano seria ideal, como essa situação persistiu. Se o índice mais eficiente foi lido na memória para outra consulta, a consulta, em seguida, teria um plano de qualidade inferior. Como o plano iria ser invalidado para que ele pudesse ser recompilado novamente?
Como engenheiro de software antigo, a equipe de SQL Server , eu sei que a complexidade de engenharia de manter uma exibição agregada dos quais tabelas e índices estão na memória para ajudar a invalidação e a seleção de plano de consulta é extremamente desafiador. Isso provavelmente adicionaria um desempenho indesejável sobrecarga para apenas um benefício ocasional, e provavelmente nunca acontecerá.
Se você estiver interessado em Ver o pool de buffer, confira o sys.dm_os_buffer_descriptors de exibição de gerenciamento dinâmico (DMV) e as várias consultas eu coloquei sobre o seção de Pool de Buffer do meu blog SQLskills.
Backups de longos
**P.**Nós usamos o envio de logs para fornecer um banco de dados secundário para efeitos de relatórios. De vez em quando, nos deparamos com um problema onde aplicar um backup do log no banco de dados secundário leva muito mais tempo do que o habitual. Você tem alguma idéia o que poderia causar isso?
**.**Sim, já vi esta situação algumas vezes. Se você estiver usando banco de dados um log transporte secundário de relatórios, isso significa que você está usando a opção WITH STANDBY ao restaurar os backups de log do banco de dados secundário. Isso funciona em três etapas:
- Escreva todos os registros de log de backup do arquivo de log do banco de dados.
- Execute a parte REDO de recuperação (assegurando que todas as operações de transações confirmadas estão presentes no banco de dados).
- Execute a parte de desfazer de recuperação (assegurando que todas as operações de transações não confirmadas não estão presentes no banco de dados).
Passo 3 escreve todos os registros de log gerados pelas operações de desfazer em um arquivo especial denominado arquivo de desfazer. Isso significa que o banco de dados está no modo somente leitura. Também é transacionalmente consistente para que os usuários possam ter acesso. A razão de registros de log são gravados para o arquivo desfazer é para que o log de transações do banco de dados não é alterado de alguma forma. Isso permite que você restaure backups de log subseqüentes.
Quando o processo de restauração começa no banco de dados secundário, se existe um arquivo de desfazer, existe uma outra etapa executada antes as três primeiras etapas. Esta etapa inicial leva todos os registros de log no arquivo desfazer e desfaz os efeitos neles. Isso coloca o banco de dados de volta no estado em que estava no final da etapa 2. Este estado de banco de dados é o mesmo como se o backup de log anteriores havia sido restaurado usando WITH NORECOVERY, em vez de com o modo de espera.
O problema que está ocorrendo é quando um está sendo restaurado do backup de log contém uma transação de longa duração que não confirma antes do fim do backup de log. Isto significa que tem que ser completamente desfeita como parte de restaurar o backup de log. Isso resulta em um arquivo grande de desfazer, que, por si só, pode fazer a restauração de um log de backup demorar mais tempo. O backup de log está sendo restaurado também tem uma transação não confirmada, de longa duração, então se a tempestade perfeita. Passo 3 também vai levar um longo tempo.
Isso pode acontecer quando o banco de dados principal está passando por manutenção de índice e um backup de log termina perto do fim de uma longa operação de recompilação de índice de um índice clusterizado. A restauração inicial desse backup de log do banco de dados secundário leva muito mais tempo do que o habitual para completar devido à etapa 3 do processo de restauração.
O próximo backup de log do banco de dados principal também seja concluída antes de uma recompilação de índice. Quando ele for restaurado no secundário, o arquivo desfazer toda tem de ser desfeita novamente. Então acontece a restauração do log, e outro grande desfazer arquivo é gerado para desfazer a recompilação de índice não confirmadas a segunda.
Se o banco de dados secundário precisa 24x7 acesso para relatórios, você deve estar ciente dessa possibilidade. Nesse caso, cuidadosamente aumentar as operações de manutenção de índice do principal banco de dados com backups de log. Isso garantirá que apenas recriações de índice completo, comprometidos estão presentes nos backups de log sendo restaurados no banco de dados secundário.
Uma alternativa é mover de envio para espelhamento de banco de dados de log. Aqui, registros de log continuamente estão sendo enviados do principal para o banco de dados espelho. Não há qualquer etapas extras envolvendo operações de log desfazendo várias vezes. Há uma troca de complexidade envolvida, como a desvantagem desta abordagem é que relatórios teria que usar os instantâneos de banco de dados.
Replicar para reparar
**P.**De vez em quando vamos acabar com arquivos corrompidos. Nossos backups também acabam danificados, por isso temos que executar uma operação de reparo. Na semana passada, um dos bancos de dados, que eu tive que reparar foi um banco de dados de publicação de replicação. No Microsoft Online de livros, ele diz que todos os assinantes devem ser reinicializados após reparar um banco de dados de publicação. Você pode explicar porquê?
**.**Se você está pensando em usar a opção REPAIR_ALLOW_DATA_LOSS de (DBCC) CHECKDB (vou apenas dizer "reparar" daqui) de verificação de consistência de banco de dados, você tem que pensar duas vezes se você está indo para reparar banco de dados é um banco de dados de publicação de replicação. Se possível, use seus backups em vez de executar o reparo.
Se você estiver usando replicação de mesclagem, linguagem de manipulação de dados (DML) dispara captura alterações no banco de dados de publicação e converte-los em operações lógicas. Se você estiver usando a replicação transacional, análise de log de transação do banco de dados capta as alterações de banco de dados de publicação. Em seguida, as operações registradas, físicas são convertidas em operações lógicas. Em ambos os casos, as operações lógicas são então aplicadas aos bancos de dados de assinatura de replicação.
Nenhum mecanismo permite a captura de operações de reparação. Operações de reparação são sempre mudanças físicas directas para as estruturas de banco de dados. Estas são necessárias para corrigir as inconsistências nas estruturas, como uma página de banco de dados, um registro da tabela ou uma ligação entre duas páginas. Eles são diferentes das alterações de banco de dados físico por causa de consultas executando inserções, atualizações ou exclusões em tabelas.
Essas operações não podem traduzir em operações lógicas que você pode ter aplicado aos assinantes de replicação de reparação. Não há nenhum operações lógicas do que pode ser expresso usando Transact-SQL para o equivalente das mudanças estruturais diretos que a reparação está realizando. Imagine que uma operação de reparo é forçada a remover uma página de dados de um índice clusterizado exclusivo (essencialmente excluir alguns registros da tabela). A assinatura não é reinicializada. Esses registros ainda existiria na cópia da tabela replicada.
Se uma operação de inserção posterior inserido registros com valores de chave de cluster correspondente aos registros excluídos pela operação de reparo, o Distribution Agent falhará ao tentar aplicar as inserções na cópia replicada. A operação de reparação que não foram aplicada no banco de dados de assinatura, então ocorrerá um erro de violação de chave duplicado ao tentar aplicar o inserir tabela replicada. Isto significa que se uma operação de reparo altera qualquer tabela que faz parte de uma publicação de replicação, a assinatura de replicação não é mais válida. Ele terá que ser reinicializadas.
Se a operação de reparo afeta qualquer uma das tabelas de metadados de replicação, a publicação de replicação inteira está em um estado inconsistente. Replicação deve ser completamente removida e reconfigurada. Obviamente, este é um processo muito mais invasivo do que apenas Reinicializando uma assinatura. A linha inferior é que, se possível, você quer evitar ter de reparar um banco de dados de publicação de replicação.
.jpg)
Paul S. Randal é diretor-gerente da SQLskills.com, diretor regional da Microsoft e um SQL Server MVP. Ele trabalhou no SQL Server Storage Engine equipe da 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. Ele bloga em SQLskills.com/blogs/paul e você pode encontrá-lo no Twitter em Twitter.com /PaulRandal..