P+R SQL: Mantendo logs e índices
Algumas das principais maneiras de manter o SQL Server em execução com eficiência são a preservação de backups de logs e a manutenção adequada de índices.
Paul s. Randal
Don quebrar a cadeia de
P: Eu tiver sido definindo uma estratégia de backup para nossos bancos de dados. Meu plano envolve backups do log de transação, de forma podemos executar a recuperação de desastres com pouca perda de dados. Eu tiver sido investigando alguns dos problemas que podem encontrar e li várias vezes que eu preciso ter cuidado para não quebrar a cadeia de backup do log. Você pode explicar isso e como ele poderá ser desfeito?
R: Essa é uma ótima pergunta e é algo que muitas pessoas esquecer. A cadeia de backup de log (chamada às vezes, simplesmente a cadeia de log) refere-se a uma seqüência ininterrupta de backups de log de transações que abrangem o tempo de backup de dados mais recente (completo ou diferencial) para o ponto em que você deseja restaurar. Uma seqüência de restauração do exemplo deve ser o seguinte:
- O backup do banco de dados completo mais recente
- Em seguida, o backup mais recente do banco de dados diferencial
- Em seguida, todas as transações backups de log executados depois que
A maioria das pessoas manter transação mais backups de log ao redor no caso de um dos backups torna-se corrompido e você precisar restaurar um backup recente com menos dados. Você pode obter mais informações sobre backups e restaurações nos dois TechNet Magazine artigos que escrevi no ano passado, “ do Noções básicas sobre backups do SQL Server” e “ recuperar de desastres usando backups . ”
Se qualquer um dos backups de log necessários estão danificados ou não está disponível para a seqüência de restauração escolhido, a cadeia de backup de log será interrompida e você não poderá restaurar os últimos apontar. Se houver apenas um dos backups de log está danificado, você poderá forçá-lo para restaurar usando a opção WITH CONTINUE_AFTER_ERROR. Que forçaria uma restauração de registros de log de transação corrompidos que possam resultar em corrupção de banco de dados. Eu seria muito hesitante sobre como forçar a esse tipo de restauração.
Uma operação que pode resultar em um backup de log necessários indisponibilidade é um backup de log “ fora da banda ”, sem garantia de que um backup do log é mantido. Você pode fazer isso para fornecer uma cópia para um desenvolvedor, por exemplo. O backup do log faz parte da cadeia de backup de log, pois é a única pessoa que irá conter os registros de log gerados desde o backup do registro anterior.
Ou seja, a menos que você use a opção WITH COPY_ONLY, que executa o backup do registro, mas também permite que o backup do registro seguinte efetivamente fazer o backup do mesmo conjunto de registros de log. Consulte meu blog para postar, “ BACKUP WITH COPY_ONLY, ” para ver mais detalhes sobre como evitar quebrar a cadeia de backup .
Um exemplo mais comum de uma operação de quebrar a cadeia de backup do log é aquele que impede a execução de um backup do log de transação durante as operações normais. Esses tipos de operações:
- Alternar para a recuperação simples do modelo e, em seguida, de volta para FULL ou BULK_LOGGED
- Despejar o log no SQL Server 2005 e versões anteriores, usando as opções de TRUNCATE_ONLY ou BACKUP do LOG … WITH NO_LOG
- Revertendo um banco de dados a partir de um instantâneo de banco de dados
Você precisará executar um backup de dados (completo ou diferencial) após qualquer uma dessas operações para permitir que os backups de log continuar. Isso é chamado de reiniciar a cadeia de backup do log.
Uma última dica: Ao contrário do que para mito popular, realizar um backup completo ou diferencial faz a cadeia de backup do log divida do não e, na verdade, não tem efeito sobre os backups de log que seja.
Esses índices de cluster
P: Muitas das tabelas no nosso banco de dados do SQL Server 2008 Don tem um índice de cluster. Ouvi dizer que eu poderia ter problemas de desempenho com registros encaminhados causando ss extras. Você pode me dizer como posso verificar isso e o que pode fazer sobre isso?
R: Um heap é uma tabela que não tem um índice de cluster. Ele é inerentemente não ordenados. Para os leitores que Don sabem sobre registros encaminhados em heaps e como eles são usados, consulte meu blog para postagem, “ encaminhamento e encaminhados registros e o tamanho do ponteiro back , ” para obter mais detalhes. Registros encaminhados em heaps podem levar a operações de e/S aleatórias extra durante o processamento da consulta, que por sua vez, leva a um desempenho ruim.
A maneira mais fácil de verificar se há consultas que estão processando encaminhadas de registros é examinar o contador de desempenho de registros encaminhados/s' no objeto de desempenho de métodos de acesso. Em seguida, use a função de gerenciamento dinâmico de sys.dm_db_index_physical_stats com o modo descrito em relação a algumas das tabelas no banco de dados e retornará o número de registros encaminhados para cada tabela na coluna forwarded_record_count da saída. Consulte neste tópico nos manuais online para obter mais detalhes.
A pior maneira de remover registros encaminhados é criar um índice de cluster e, em seguida, solte-o novamente. Isso faz com que tudo isso sem cluster índices na tabela a ser recriados automaticamente duas vezes, que é um desperdício enorme de recursos. Consulte meu blog para postar para obter mais detalhes: “o que acontece com índices não clusterizados quando a estrutura da tabela é alterada?”
A maneira mais fácil para remover permanentemente e evitar registros encaminhados em heaps é criar índices de cluster. Eu Don quero entrar “ índice de cluster vs. heap ” debate aqui sobre por que você deve ter agrupado índices na maioria dos casos, em vez de pilhas. Consulte o blog de “ chave de cluster ” do minha esposa Kimberly Tripp lançar série sobre esse assunto, para obter mais detalhes. Eu o incentivo para avaliar o uso de índices em cluster.
Quando os registros da tabela de aumentam o tamanho, ele poderá causar registros encaminhados quando não houver espaço suficiente. Outra maneira de evitar encaminhado registros, portanto, é impedir que os registros alterando o tamanho. Isso pode significar, por exemplo, usando os valores padrão para colunas de comprimento variável.
No SQL Server 2008, há uma nova instrução ALTER REBUILD de … TABLE que permite que você reconstrua heaps. Isso funciona da mesma forma que a instrução ALTER INDEX … REBUILD permite reconstruir índices. A Microsoft adicionou a esta declaração de suporte ao recurso de compactação de dados, mas ele funcionará para nossos objetivos. Consulte neste tópico nos manuais online para obter mais detalhes.
Manutenção do índice
P: Alterei o nossas rotinas de manutenção de índice para usar recria índices online, mas ainda vejo problemas de bloqueio, às vezes, ao executar as rotinas de manutenção. Por que isso? Pensei que as operações de indexação on-line Don utilizar bloqueios, portanto, Don vejo nenhum bloqueio. É esse comportamento esperado ou que estou fazendo algo errado?
R: Você está vendo o comportamento esperado. Não há um bloqueio de tabela compartilhada necessário no início da operação, enquanto a operação é inicializado (um processo muito rápido). Isso será imediatamente interrompido. Esse bloqueio deve ser colocado na fila como qualquer outro bloqueio e irá impedir que qualquer consulta nova modificações à tabela até que você pode conceder e liberar o bloqueio novamente.
Você não pode adquirir o bloqueio até que você concluiu todas as consultas em execução no momento da modificação. Isso pode levar bastante tempo, dependendo de sua carga de trabalho. Isso significa que o bloqueio pode ocorrer no início de uma operação de indexação on-line.
No final da operação, você precisa usar um bloqueio de modificação do esquema – Pense nisso como um bloqueio exclusivo — para permitir que ele seja concluído. Isso também acontece extremamente rápido. Em seguida, você soltá-lo imediatamente. Esse bloqueio impedirá que qualquer tipo de novas consultas na tabela (de leitura ou gravação) até que você conceda e liberação do bloqueio.
Uma vez, você não pode adquirir o bloqueio até que o SQL foi concluída, tudo isso em execução ler ou escrever consultas. Novamente, isso significa que há a possibilidade de bloqueio.
Para resumir, embora o nome do recurso seja operações de indexação on-line, ele ainda requer dois bloqueios de curto prazo que podem causar problemas de bloqueio. O ganho em operações de indexação off-line tradicionais é que para a grande maioria da operação de índice, que são não há bloqueios mantidos e então, em geral a simultaneidade é aumentada. O white paper “ de de operações de indexação on-line no SQL Server 2005 ” tem muito mais detalhes sobre o funcionam dessas operações.
Reduzindo o tempo de manutenção do índice
P: Eu já herdada alguns sistemas em que os trabalhos de manutenção de índices regulares levam muito tempo para executar e gerar grande quantidade de e/S, mas Don executam qualquer reconstruções de índice porque os índices Don estão obtendo fragmentados. Eu gostaria para reduzir o trabalho que está sendo feito, pois não estou obtendo qualquer ganho de desempenho. Você pode recomendar uma estratégia para ajudá-lo?
R: Este é um problema muito comum. Ele deriva da maneira como os trabalhos de manutenção do índice determinam quais índices para recriar ou reorganizar.
A maioria das pessoas que executar a função de gerenciamento dinâmico sys.dm_db_index_physical_stats (mencionada anteriormente) em todos os índices no banco de dados, escolha se deseja recriar, reorganizar ou não fazer nada. Eles baseiam esta decisão de avg_fragmentation_in_percent, o page_count e os valores de avg_page_space_used_in_percent usando uma cláusula WHERE na saída.
O problema é que a fragmentação do índice não será armazenada na memória como outras estatísticas. Esta função deve ler e processar cada índice, para determinar a extensão de sua fragmentação. Se a maioria dos índices no banco de dados é estática ou alterar muito lentamente (em termos de fragmentação), em seguida, elas não serão ser recriadas nem reorganizadas. Verificação de fragmentação toda vez que você executa um trabalho de manutenção do índice é essencialmente um desperdício de tempo.
Exibições de gerenciamento mais dinâmicas oferecem suporte a “ predicado push-down, ” onde somente os dados processados são que corresponde ao predicado da cláusula WHERE. No entanto, o sys.dm_db_index_physical_stats é uma função, não um modo de exibição, para que ela não é possível fazer isso. Isso significa que você precisa filtrar manualmente e solicite apenas a função para processar os índices que você sabe que têm o potencial de fragmentação e pode exigir recompilando ou reorganizando.
Eu recomendo o monitoramento de fragmentação no decorrer de algumas semanas. Dessa forma que você ter uma idéia das quais índices valem a pena verificando se há fragmentação, em vez de verificar tudo. Depois de ter essa lista de índices, crie uma tabela com o nome da tabela, o limite de nome e a fragmentação do índice para executar uma ação. Você pode descobrir que alguns índices podem ter mais fragmentação antes de afetar o desempenho do que outras pessoas. Isso será “ driver tabela ”, em seguida, você usa para orientar o trabalho de manutenção do índice. Ele deve fazer um loop através de todos os índices descritos na tabela a e apenas executar a função sys.dm_db_index_physical_stats neles.
Eu o implementei para vários clientes. Em alguns casos, ele reduziu o tempo de execução do trabalho de manutenção do índice de horas até 15 minutos ou menos. Isso é puramente de não executar essa função nos índices estáticos. Também poderia ir um passo além e manter controle sobre a freqüência com que um índice é recriado e alterar potencialmente o fator de PREENCHIMENTO do índice definindo automaticamente, por sorte, levando a uma redução adicional no trabalho realizado pelo trabalho de manutenção do índice.
Para obter mais informações sobre os diversos métodos de realizar a manutenção do índice, consulte minha postagem de blog, “ importância da manutenção do índicedo ” e uma explicação detalhada sobre o que acontece bastidores da função, também consulte meu blog postar “ Inside sys.dm_db_index_physical_stats . ”
Graças à l. de Kimberly Tripp da SQLskills.com para sua revisão técnica da coluna deste mês.
.jpg)
Paul s. Randal é o diretor administrativo da SQLskills.com e MVP do SQL Server de um diretor regional da Microsoft. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft de 1999 para 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 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 encontrar no Twitter no Twitter.com/PaulRandal.