SQL Q & A Removendo a fragmentação de índice, sincronização e sincronização e muito mais
Paul S. Randal
QEstou confuso sobre como remover fragmentação de índice afeta as estatísticas. Ouvi que às vezes, deve recriar Meus estatísticas depois de recriar um índice e, às vezes, não deveria, e que recriar um índice de cluster afetará todos os outros índices muito. Você pode shed alguns luz acesa este please, como o deseja ter certeza de que estou fazendo não inadvertidamente pior desempenho?
AIsso é certamente um problema que faz com que muita confusão, mas você está certo de que uma estratégia de manutenção de banco de dados abrangente inclui removendo fragmentação de índice e atualizando estatísticas. Não vou entrar em detalhes de exatamente quando e por que remover fragmentação de índice, para isso, consulte o artigo agosto de 2008 " Principais dicas para manutenção de banco de dados efetivas." Em vez disso, considerarei que você sabe quais índices para trabalhar com e apenas concentrarei no funcionam das coisas.
A primeira parte da confusão é em torno do qual remoção fragmentação operações afetam as estatísticas. Recriando um índice (usando ALTER INDEX … RECRIAR, DBCC DBREINDEX ou CREATE INDEX … WITH DROP_EXISTING) atualiza estatísticas com o equivalente de uma verificação completa, mas reorganizar um índice (usando ALTER INDEX … REORGANIZAR ou DBCC INDEXDEFRAG) não atualiza as estatísticas, embora ambos remover fragmentação de índice.
O motivo que recriar atualiza as estatísticas e reorganizar não tem a ver com os algoritmos usados para essas operações. Uma operação de reconstrução de índice tem uma visão completa do índice e, portanto, pode atualizar as estatísticas corretamente. Uma operação de reorganizar, no entanto, apenas opera em algumas páginas do índice por vez e não é possível atualizar corretamente as estatísticas para o índice inteiro.
A segunda parte da confusão é sobre quais estatísticas são atualizadas quando um índice é recriado. Há dois tipos gerais de estatísticas para uma tabela — aqueles para as colunas da tabela indexado e aquelas para as colunas não-indexadas. Uma operação de reconstrução de índice apenas atualiza as estatísticas para o índice que está sendo reconstruído. As estatísticas de colunas não-indexadas devem ser atualizadas manualmente no plano de manutenção. Além disso, como mencionei no artigo "superior dicas para efetivas banco de dados manutenção", você deve ser cuidado para não atualizar manualmente estatísticas de índice depois de recriar o índice, como a atualização manual pode usar uma taxa de amostra de menor que 100 %, enquanto a recriação de índice usará o equivalente de uma amostra de (100 %) de exame completo. Em outras palavras, isso pode levar a substituir uma estatística de exame completo por uma estatística de amostra.
A última parte da confusão é em torno da qual efeito recriando um índice tem sobre outros índices. A resposta é sempre que recriar um índice afeta somente esse índice específico e suas estatísticas. A exceção é para um índice em cluster não-exclusivo no SQL Server 2000, onde a recriação de um índice também causará todos os índices não-agrupados na tabela que ser refeitas. Isso foi corrigido do SQL Server 2005 em diante. Você pode encontrar mais informações nesse ponto na minha postagem de blog em" Índices de cada ângulo."
Para resumir, a manutenção de índice e estatísticas deve fazer o seguinte:
- Recriar ou reorganizar os índices para remover a fragmentação
- Atualizar estatísticas de índice para os índices que não foram recriados
- Atualizar índice sem estatísticas
QTenha sido testando com o SQL Server 2008 e eu tiver encontrado algum comportamento estranho. Parece que se eu ativar a compactação de dados no banco de dados de produção e, em seguida, o banco de dados de backup e tente restaurá-lo em uma instância de Standard Edition, a restauração falhe! É esse comportamento esperado? Em caso afirmativo, é isso limita a compactação de dados ou quaisquer outros recursos afetados? E por que não a restauração falhar imediatamente em vez de seeming fazer a restauração inteira antes de falhar?
AO comportamento que você está vendo é por design. Mais "empresa somente" recursos apenas limite as edições do SQL Server com o qual você pode fazer uso do recurso para Enterprise Edition, Enterprise Evaluation Edition e Developer Edition. Há alguns recursos, no entanto, limite ou também que as edições do SQL Server pode restaurar um backup de um banco de dados que contém o recurso. Encontrei um dos recursos SQL Server 2008 que tem essa restrição, compactação de dados.
O comportamento é na verdade, não é novo para SQL Server 2008. No SQL Server 2005, se um banco de dados contiver quaisquer tabelas particionadas ou índices (explicitamente usando o recurso de particionamento), em seguida, um backup desse banco de dados só pode ser restaurado usando uma edição na lista que acabei de mencionar. No entanto, há dois problemas com isso, no SQL Server 2005. Primeiramente, ele pode ser difícil dizer se houver qualquer particionamento em um banco de dados, e em segundo lugar, a restauração não falha até que apenas antes de ter deve ter concluído.
Isso significa que você não descobrir que a restauração será falha até que você já aguardou por meio de toda a operação restauração (potencialmente execução demorada). Isso ocorre porque o banco de dados não é transacional consistente até que a parte de recuperação da restauração seja concluída, e para uma operação durante a recuperação pode adicionar ou remover a partição. Encontrar essa situação pode ser realmente irritante uma situação de recuperação de desastres acontece em que o único servidor disponível para obter rapidamente o banco de dados restaurado é Standard Edition.
No SQL Server 2008, a lista de recursos "empresa somente" com esse comportamento cresceu para quatro: a compactação de dados, captura de dados de alteração, criptografia de dados transparente e particionamento. Isso significa que mais pessoas forem encontrar esse problema. Por esse motivo, um novo DMV foi adicionado, sys.dm_db_persisted_sku_features, que permite que um banco de dados para facilmente verificar se qualquer um desses recursos estão habilitadas em um banco de dados.
Por exemplo, em um banco de dados em uma tabela tenha sido compactação de dados habilitada, executando a DMV fornecerá os seguintes resultados:
SELECT * FROM sys.dm_db_persisted_sku_features;
GO
feature_name feature_id
-------------- -----------
Compression 100
No entanto, o SQL Server 2008 não soluciona o problema com a restauração operação precisar quase concluída antes do banco de dados é informado de que o banco de dados, na verdade, não pode ser restaurado. Na verdade, esse problema é improvável que ser abordados dada a natureza do funcionamento de restauração, consulte o O problema de outubro de 2008 coluna p & r do SQL Para obter mais detalhes.
Se é possível que um banco de dados precisa ser restaurado no Standard Edition (ou uma edição inferior), é imperativo que o banco de dados não permite a esses recursos ou usa a DMV regularmente para evitar uma surpresa desagradável ao tentar restaurar em uma situação crítica.
QEU tenha implementado o espelhamento do banco de dados síncrono e foi na impressão de que isso significava que o banco de dados de espelhamento sempre é sincronizado com o banco de dados principal. Entretanto, ocasionalmente, que já vi o estado do espelho se tornar a sincronização em vez de SYNCHRONIZED, bem como quando primeiro configuramos um espelho. Por isso acontece?
AAntes que responder à pergunta, irá definir o banco de dados SYNCHRONIZED and sincronização espelhamento estados. Em poucas palavras, o espelhamento do banco de dados funciona continuamente enviar registros de log de transação física entre a instância do SQL Server que hospeda o banco de dados principal e a instância que hospeda o banco de dados espelho. Não se houver nenhum log de transação aguardando o objeto seja enviada para o espelho, o estado de espelhamento será SINCRONIZADO (em outras palavras, os dois bancos de dados são sincronizados). Se não houver transações registros de log que ainda não foram enviados para o espelho (o banco de dados de espelhamento é o principal atrás), o estado de espelhamento é sincronização.
O método para inicializar um espelho de banco de dados é levar um backup completo do banco de dados, além de pelo menos um log de transações backup e restaurá-los (usando WITH NO_RECOVERY) no espelho. Em seguida, quando o espelhamento está habilitado, o estado de espelhamento inicialmente está SINCRONIZANDO. Isso ocorre porque pode ter havido alguma atividade de transação de capital desde o último backup de log que foi restaurado no espelho. Isso é muito provável para um banco de dados com uma carga de trabalho constante.
O truque para minimizar o tempo em que o espelhamento permanece estado de sincronização deve tentar fazer o banco de dados de espelhamento tão atualizado quanto possível usando backups de log. No entanto, para um banco de dados com uma carga de trabalho constante, isso pode ser muito difícil e portanto, o estado de espelhamento inicialmente estará sincronização por algum tempo. Depois que o espelho captura backup, o estado de espelhamento será alterado para SYNCHRONIZED.
Após esse ponto, é possível para o banco de dados espelho para novamente estão por trás do objeto, nesse caso o estado de espelhamento cairá novamente a sincronização. Pode ter ocorrido um isso se o espelho e o principal conseguem se comunicar por um período de tempo. Ou ele pode acontecer se o objeto está gerando o log de transações mais rápido do que o log pode ser enviado para o espelho. Em ambos os casos, e dependendo de como o espelhamento do banco de dados está configurado, o banco de dados principal pode continuar a processar transações e uma fila de log de transações registros criará (chamado de fila SEND). Isso deve ser enviado para o espelho para que ela se atualize. Enquanto o banco de dados de espelhamento tem detectada backup, o estado de espelhamento permanecerá sincronização.
Se o vínculo de rede for não-confiável entre o objeto e o espelho, você pode ver o estado de espelhamento alternar back-e-estabelecidos entre sincronização e SYNCHRONIZED. Você pode encontrar uma discussão completa sobre esses estados no white paper Banco de dados de espelhamento no SQL Server 2005.
QTemos síncrono espelhamento conjunto de backup para nosso banco de dados principal do aplicativo do banco de dados e nossos monitoração mostra que a fila de refazer no espelho está geralmente quase zero. Gostaria de usar o banco de dados de espelhamento para relatórios e verificação para que podemos fazer melhor de consistência use do hardware redundante. Sabemos que isso é possível, mas há qualquer desvantagens para isso que nós devem saber do?
A Isso é uma prática muito comum, mas existem algumas desvantagens que você talvez precise atento.
A primeira questão é licenciamento. A licença para a instância do SQL Server que hospeda o banco de dados espelho está livre se a instância for usada apenas para essa finalidade. Assim que você criar um instantâneo de banco de dados no banco de dados do espelho ou fazer mais com essa instância do SQL Server nada, você deve adquirir uma licença.
Como diz respeito a verificação de consistência, executá-los no banco de dados do espelho (através de um instantâneo de banco de dados) não oferecem quaisquer garantias sobre a consistência do banco de dados principal. Apenas registros de log de transação são espelhados entre os bancos de dados, portanto, se o subsistema de E/s danificar uma página no banco de dados principal, essa corrupção não irá ser espelhada em banco de dados de espelhamento. Isso significa que uma verificação de consistência do banco de dados espelho não deve ver página corrompida no banco de dados principal.
Relatório é um bom uso de um espelho de banco de dados. Primeira saída pessoas executarem no é como atualizar o instantâneo de banco de dados que estejam executando os relatórios em. Um instantâneo de banco de dados não pode ser atualizado para um novo instantâneo deve ser criado e o aplicativo de geração de relatórios deve se conectar ao novo instantâneo. Isso requer lógica extra a ser criado para o aplicativo relatórios. A segunda questão é determinar como o aplicativo de geração de relatórios deve se comportar em caso de um failover de espelhamento — deve ele permanecer conectado ao novo objeto ou ela deve mover para o novo espelhamento? Entrar em detalhes sobre isso está além do escopo deste artigo.
Com qualquer uso extra de banco de dados de espelhamento, a principal preocupação é a possibilidade de causando problemas de desempenho no espelho. Criar um instantâneo de banco de dados adiciona carga extra de E/s em que na primeira vez uma página for alterada no banco de dados de espelho, ele deve ser enviado para o instantâneo de banco de dados. Além disso, qualquer carga de trabalho é, em seguida, colocar no instantâneo de banco de dados aumenta a carga de E/s no banco de dados do espelho — como muitas das páginas lidas do instantâneo de banco de dados serão realmente ser ler do banco de dados espelho, como terem serão não alteradas desde que o instantâneo foi criado.
Essa carga extra de E/s no banco de dados do espelho pode diminuir a repetição de registros do log de transações e por sua vez cliente potencial a um registro posterior. Esse registro posterior é chamado a fila de REFAZER e é a quantidade de log de transações que deve ser repetido antes do banco de dados de espelhamento pode ficar online após um failover. Quanto maior a carga de E/s no banco de dados espelho do instantâneo banco de dados, quanto maior que a fila de REFAZER potencialmente aumentará e quanto mais o banco de dados ficará indisponível após um failover.
Você mencionar que o comprimento de fila de REFAZER é geralmente quase zero para que isso não pode ser um problema para você, mas, definitivamente, é algo para observar check-out para você não acaba comprometendo a disponibilidade do banco de dados no returno para a maior capacidade de executar relatórios.
S. Paul Randal é o diretor de gerenciamento de SQLskills.come um MVP do SQL Server. Ele trabalhou na equipe mecanismo de armazenamento do SQL Server da Microsoft de 1999 para 2007. Paul escreveu DBCC CHECKDB/reparo para o SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Paul é um especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados e é um apresentador regular em conferências em todo o mundo. Blogs de he em SQLskills.com/blogs/paul, e ele pode ser encontrado em Twitter em Twitter.com/PaulRandal.