SQL q & a: desfragmentações e desastres

Artigo
08/19/2016

Os logs de erros e os arquivos temporários de banco de dados podem crescer descontroladamente em um curto período de tempo. Seguir estas táticas de configuração poderá ajudá-lo.

S de Paul. Randal

Padrão para desfragmentação

**P.**Tiver sido endereçamento alguns problemas de manutenção de banco de dados que temos em nosso servidor e estou considerando a fragmentação do índice. Em vez de gastar tempo para descobrir um fator de preenchimento para cada índice, não seria mais fácil simplesmente definir o fator de preenchimento de padrão para essa instância? Existem Existem desvantagens para fazer isso?

**R.**Geralmente, não recomendaria definindo o fator de preenchimento em toda a instância para qualquer mas o padrão — 100 por cento. A alteração dessa configuração pode levar a espaço desperdiçado no banco de dados.

Quando você cria índices em uma tabela, normalmente apenas um deles corresponderá o tabela padrão de inserção. Todos os outros índices inevitavelmente ficarão fragmentados. Desde que o padrão de inserção é acrescentar apenas (nenhuma inserções aleatórias da necessidade de uma chave aleatória como um identificador exclusivo [GUID]), faz sentido para evitar que o maior índice fragmentado.

O maior índice é sempre o índice de cluster, como o índice de cluster é a tabela. Contém todos os registros de dados com todas as colunas da tabela. Faz sentido para o índice de cluster ser aquele que não fragmentado. Remover a fragmentação do índice em cluster será mais caro (em termos de log de transações, tempo e espaço em disco) que fazê-lo para qualquer outro índice.

Como o índice de cluster geralmente não fragmentado, portanto faz sentido que o fator de preenchimento é definido como 100. Nenhum espaço adicional será necessária. Pode haver outras razões que o índice de cluster fica fragmentado (como from atualizações às colunas de comprimento variável que tornam mais linhas da tabela), mas isso geralmente é true.

Se você definir o fator de preenchimento de toda a instância a nada, exceto a 100 por cento, você deve definir especificamente os fatores de preenchimento de todos os índices em cluster que não fragmentados. Caso contrário, eles vai usar o novo fator de preenchimento de toda a instância e deixe espaço quando eles estão sendo reconstruídos. Essencialmente, isso desperdiça espaço no banco de dados. Você pode optar por fazer isso, mas geralmente não é uma prática recomendada.

Como há uma variedade de índices em qualquer banco de dados, seria comum encontrar um valor de fator de preenchimento único é ideal para todos os índices. Ele geralmente é melhor de uma perspectiva de gerenciabilidade ser examinadas o fator de preenchimento de toda a instância. Especificamente, você poderá definir os fatores de preenchimento inferiores nesses índices que precisam deles.

Respostas de reinicialização.

**P.**Alguns servidores em nosso ambiente raramente são reinicializados. Embora essa seja uma coisa boa de várias maneiras, isso significa que o log de erros de SQL Server pode ficar grande impractically. Parece que podem ocupar dezenas de milhares de mensagens de conclusão de backup para o qual eu tenho sem uso. Há alguma coisa que posso fazer com que os logs de erros menores e mais gerenciáveis?

**R.**Existem duas coisas que você pode fazer: recortar para baixo sobre as mensagens de backup e configurar o gerenciamento de log de erro. Sempre que um backup é concluído, ele gravará uma entrada no log de erros. Isso é de pouca utilidade. Conclusão bem-sucedida de backup é a norma, não um erro.

Há um sinalizador de rastreamento documentados — sinalizador de rastreamento 3226 — que irá impedir que mensagens de sucesso de backup. Você deve que adicionar à lista de sinalizadores de rastreamento de inicialização (usando o Gerenciador de configuração de SQL Server). Para ativar isso sem precisar primeiro parar e reiniciar o SQL Server, você pode ativar também que traçam sinalizar usando o comando "DBCC TRACEON (3226, -1)." O meio-1 para aplicar o sinalizador de rastreamento globalmente. A equipe de SQL Server blogou exatamente sobre o sinalizador de rastreamento em 2007.

Você também pode configurar o gerenciamento de log de erro dentro de SQL Server Management Studio (SSMS). Abra o Explorador de objetos no SSMS e conectar-se a SQL Server. Expandir a caixa de gerenciamento, clique com o botão direito em Logs de SQL Server e selecione Configure. Quando for exibida a caixa de diálogo Configurar Logs de erros de SQL Server, verifique a opção "Limitar o número de arquivos de log de erro antes que sejam reciclados". Selecione 99 logs de erros. Isso substitui o número padrão de seis logs de erros de mantida.

A etapa de configuração final é o que limitará o tamanho de cada log de erro. Instrua o SQL Server para criar um novo log de erro diariamente (chamado "ciclo" o log de erros). Fazer isso criando um trabalho diário de agente de SQL Server que simplesmente faz "EXEC sp_cycle_errorlog". Depois disso, os logs de erro devem ser gerenciáveis.

Controlar o Tempdb.

**P.**O volume de dados cresceu enormemente nos últimos dois anos. Nosso tempdb sempre parece preencher aquele que controlá-la é. Podemos executar algumas consultas complexas, portanto, podemos fazer muito uso de tabelas temporárias. Você pode dar algum conselho sobre como reduzir a nossa utilização tempdb?

R: o uso de Tempdb é um problema perene para usuários de SQL Server. Há apenas um único tempdb para cada instância de SQL Server, para que você tenha cuidado como ele é usado.

Um dos usos mais comuns de tempdb é de tabelas temporárias. Essas tabelas permitem que os resultados intermediários de complexas agregações ou associações para persistir e se tornar parte de uma consulta maior. Geralmente, isso pode ser uma maneira eficiente de dividir uma consulta longa e complexa, mas nem sempre é o caso. Às vezes, os desenvolvedores se acostuma a usando tabelas temporárias, por padrão, em vez de fazer qualquer teste de desempenho para ver se o uso de uma tabela temporária ou não é mais eficientes.

O problema com a criação de um resultado intermediário definido em uma tabela temporária é que ele pode interromper o pipeline de dados eficiente por meio de uma consulta complexa. Ele força o otimizador de consultas para processar a criação da tabela temporária ou população separadamente de operações subseqüentes que usam a tabela temporária. Às vezes, o otimizador de consulta produz um plano de consulta mais eficiente se ele não usa uma tabela temporária. Ele também pode executar com mais eficiência com uma construção de consulta diferentes, como uma tabela derivada ou uma expressão de tabela comum.

Se uma tabela temporária é a maneira mais eficiente para quebrar sua consulta, há duas coisas que você pode fazer para reduzir o uso de tempdb:

Criar índices apropriados: certificar-se de que os índices únicos criados na tabela temporária são realmente úteis para processamento posterior (Verifique isso, analisando e verificando que indexa o plano de consulta está usando). Índices não agrupados abrangidos criados em cada coluna da tabela temporária podem não ser úteis. Verifique também se os índices são criados depois de preencher a tabela temporária, para que eles tenham as estatísticas para ajudar o otimizador de consulta para usar o índice.
Minimizar o tamanho da tabela temporária: certificar-se de que as colunas somente terão efeito na tabela temporária são aqueles usados para processamento adicional, caso contrário, elas são um completo desperdício de espaço. Tabelas temporárias geralmente são criadas com um SELECT * construção com não pensou para quais colunas são realmente necessárias. Se você estiver processando um conjunto de resultados grande, esse espaço desperdiçado pode realmente fazer sentido.

Backups inválidos

**P.**Na semana passada a nossa SAN travou. Podemos sofreu alguma perda de dados em nosso banco de dados de produção. Os backups mais recentes foram armazenados na SAN com os arquivos de banco de dados, para que aqueles estavam danificados. Também descobrimos que nossos backups ligeiramente mais antigos foram corrompidos também — às vezes, a corrupção do mesma. Como podemos evitar essa situação no futuro?

**R.**Esta é uma situação comum — não há bons backups e um único ponto de falha, o que, por fim, leva à perda de dados.

O primeiro problema é que os backups estão corrompidos. Você precisa implementar um estratégia de teste para que você possa determinar se o backup está corrompido ou contém um banco de dados corrompido de backup. Há várias partes para isso:

Implemente uma verificação de consistência regulares do banco de dados de produção. Isso significa que esteja executando o comando DBCC CHECKDB no banco de dados de produção propriamente dito ou uma cópia do banco de dados.
Habilite somas de verificação do banco de dados de produção (se ainda não estiver ativado). Use a opção WITH CHECKSUM em todos os backups. Isso testará somas de verificação, como páginas de arquivo de dados são lidos para inclusão no backup, que ajuda a evita a criação de um backup com um banco de dados corrompido.
Implemente uma verificação de validade dos backups depois que estiver tiradas. Isso envolve fazer o backup para outra instância de SQL Server e restaurá-la (usando a opção WITH CHECKSUM novamente), ou na pior das hipóteses, realizando um RESTORE VERIFYONLY do backup usando WITH CHECKSUM. O objetivo é restaurar o banco de dados e executar o DBCC CHECKDB. Isso também é uma boa maneira de aliviar a carga de trabalho de verificação de consistência do servidor de produção.
Implemente um agendamento regular de teste onde você praticar a restauração do banco de dados de produção a partir de backups disponíveis.

Outra maneira de testar facilmente a validade dos backups de log de transação é criar um envio de log secundário. Constantemente, isso irá restaurar os backups de log de transação da produção. Ele também fornece uma cópia redundante do banco de dados.

O segundo problema é que os backups são armazenados no mesmo subsistema de e/S como o próprio banco de dados. Isso significa que não há nenhuma proteção contra falha do subsistema de e/S.

Você precisa manter cópias de todos os backups em um subsistema de e/S separado do banco de dados de produção — o ideal é que isso seria em um local totalmente separado. Cópias locais dos backups ajudam com recuperação de desastres mais rápida e cópias remotas Certifique-se de que a recuperação de desastres é sempre possível se o armazenamento local seja danificado ou destruído.

Outra coisa a considerar é encorajador exercícios de recuperação de desastres regular. Suponhamos que um desastre e trabalhar por meio de seu plano de recuperação de desastres para determinar a sua eficácia. Meu artigo de abril de 2011, "SQL Server: proteger os dados a todo custo," aborda isso partir de uma perspectiva de gerenciamento.

Prontidão para recuperação de desastres é semelhante a segurança. Uma das diretrizes repetidas com freqüência é a "defesa detalhada". As opções mais que disponíveis para recuperação e os problemas em potencial mais você prever e evitar proativamente, maior a probabilidade, você poderá recuperar um desastre em seus contratos de nível de serviço de tempo de inatividade e perda de dados.

Paul S. Randal

**S de Paul. Randal**é o diretor administrativo da SQLskills.com, um diretor regional da Microsoft e MVP de SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft entre 1999 e 2007. Ele escreveu o DBCC CHECKDB/repair para SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento de SQL Server de 2008. Randal é especialista em recuperação de desastre, alta disponibilidade e manutenção de bancos de dados, e participa regularmente de conferências em todo o mundo. Ele bloga em SQLskills.com /blogs/Paul e ele pode ser encontrado no Twitter no Twitter.com/PaulRandal.