• Artigo

SQL Q & A Consistência do banco de dados, tabelas temporários e muito mais

Paul S. Randal

QPercebi algum comportamento muito estranho em um dos nossos bancos de dados do SQL Server 2005. É executado um DBCC CHECKDB no banco de dados como parte da nossa manutenção noturna do banco de dados e alguns noites o DBCC retorna erros corrompido. A coisa estranha é que nós não vemos falhas de somas de verificação de página durante o dia anterior e se eu manualmente executar DBCC CHECKDB pela manhã após a falha de trabalho de manutenção, as corrupções desapareceram. Você pode explicar o que está acontecendo? Isso tem sido acontecendo para sobre um mês e estou preocupado que eu não é possível confiar DBCC CHECKDB.

AÉ bom ver que você tenha DBCC CHECKDB como parte da sua manutenção regular e se as somas de verificação de página estão habilitadas. O comportamento que você descrever pode ser muito disconcerting quando ele ocorre — parece quase que DBCC CHECKDB está fornecendo resultados incorretos. Mas esse cenário ocorre vez com tanta freqüência — há não indicações de tempo de execução de dano, DBCC CHECKDB localiza corrupção e, em seguida, muitas horas posteriormente as corrupções serão eliminadas quando DBCC CHECKDB é executado novamente. Vamos este passo a passo.

Primeiro, é muito comum de corrupções a serem relatados por DBCC CHECKDB, que não foram relatadas durante operações de banco de dados regular. Embora somas de verificação de página sejam um método excelente de detecção corrupções causadas por um subsistema de E/s, eles são efetivos somente quando as páginas de dados são lidos pelo SQL Server após uma corrupção de ocorrência.

Imagine que uma página de dados tiver um checksum de página aplicada a ele e, em seguida, ele está corrompido em algum momento posterior pelo subsistema de E/s. Mesmo que a soma de verificação de página irá detectar a corrupção, ele é somente quando a página é lido na memória pelo SQL Server que a soma de verificação de página é validada e a corrupção for descoberta. Se essa página nunca é lido pelo SQL Server, a corrupção nunca será descoberta. É por isso é essencial ativar somas de verificação de página e regularmente executar verificações de consistência que — verificações de consistência serão ler todas as páginas no banco de dados, validar seus somas de verificação de página e descobrindo mais cedo possível corrupção.

No seu caso, parece como se o corrupções tiverem ocorrido em páginas de dados que não foram lidos como parte das operações regulares do banco de dados e assim as corrupções não foram descobertas até DBCC CHECKDB ler as páginas corrompidas. Embora ele pareça como se as somas de verificação página não detectar a corrupção enquanto elas devem, que não é o caso.

Em segundo lugar, corrupções "desaparecendo" podem ocorrer facilmente entre execuções sucessivas de DBCC CHECKDB, mas somente em bancos de dados onde as alterações ocorrem entre as execuções DBCC dois. Digamos que uma página está realmente danificada e DBCC CHECKDB informa como sendo corrompidos. Agora digamos que a página é, em seguida, desalocar de uma tabela (por exemplo, porque torna vazio). Um DBCC CHECKDB subseqüentes não lê-lo neste momento e, portanto, não reportará-lo como sendo corrompidos. Leituras de DBCC CHECKDB somente alocado páginas (páginas de significado que estão atualmente em uso). No seu caso, eu estou adivinhar que o resto do trabalho manutenção noturna inclui recria de índice ou reorganizar — que drasticamente podem alterar o conjunto de páginas que são alocados para um objeto específico ou o índice. Isso seria conta para o comportamento que você está vendo. Uma reconstrução de índice Desaloca as páginas corrompidas como um efeito colateral e, em seguida, o DBCC CHECKDB próxima vem volta limpa.

Para capturar as páginas corrompidas, altere seu trabalho de manutenção para que ele pára se a etapa de DBCC CHECKDB falhar. Dessa forma, você poderá verificar as corrupções manualmente e ação ainda mais.

QEstamos planejando atualizar do SQL Server 2000 diretamente para o SQL Server 2008, ignorando o SQL server 2005. Mas estou preocupado tempdb. Isso já causou problemas para nós no SQL Server 2000, que que tenha muitas tabelas temporárias curta duração. De que sei, tempdb é usado muito mais muito agora (no SQL Server 2005 e SQL Server 2008) e provisionamento de tão especial deve ser feito para impedir que o desempenho de descartar após uma atualização. Explique por que este é o caso e o que devemos fazer?

ASua pergunta, eu estou adivinhar que já o possuía que implementar a arquitetura de tempdb de um arquivo por processador que é geralmente necessária quando muitas tabelas temporárias curta duração são criadas por várias conexões de banco de dados. E que você talvez tenha tido mesmo habilitar o rastreamento de 1118-sinalizador (consulte" Aprimoramentos de concorrência para o banco de dados tempdb"para obter mais informações sobre isso).

Embora haja a possibilidade de gramatura maior tempdb uso no SQL Server 2005 em diante, você pode não ver isso menos que especificamente use recursos que dependam tempdb. Observe que se você tirar uma solução que teve problemas de desempenho de tempdb com o SQL Server 2000 e atualizá-lo para o SQL Server 2008 no mesmo hardware, em muitos casos verá os problemas de desempenho aliviadas um pouco devido a algumas alterações específicas de tempdb o mecanismo de armazenamento do SQL Server no SQL Server 2005.

Os recursos que tornam o uso pesado de tempdb no SQL Server 2005 e no SQL Server 2008 são:

  • Operações de indexação on-line
  • Disparadores DML
  • Conjuntos de resultados de múltiplos ativo (MARS)
  • Isolamento de instantâneo (nível de transação e nível de instrução)

Todas as quatro desses recursos usam uma tecnologia de base chamada controle de versão para armazenar várias versões de ponto no tempo de registros de dados. Simplificando, essas versões de registro são armazenados no armazenamento de versão em tempdb, com todos os bancos de dados usuários compartilhando o mesmo armazenamento de versão no tempdb único. O mais você usá-los recursos, o heavier o uso do armazenamento de versão e, portanto, tempdb será e o local mais possível terá um impacto no desempenho.

A chave para qualquer atualização bem-sucedida é implementar uma carga representativa de produção em um sistema de teste com o novo esquema e medir o desempenho para evitar surpresas que podem ocorrer se você fosse ir direto para produção.

Infelizmente, essa discussão é maneira além do escopo desta coluna capa detalhada, mas há alguns recursos excelentes disponíveis que eu recomendo que você check-out:

QEstá implementando um plano de manutenção noturna do banco de dados que inclui a melhorar o desempenho de índice. Ouvi que a configuração da opção fator de preenchimento de índices pode remover completamente a necessidade de manutenção de índices. Este É verdadeira? Parece que alguns índices em nosso banco de dados sofrer de fragmentação e alguns não. Deve definimos um fator de preenchimento padrão para o banco de dados que serão aplicadas a todos os índices e nesse caso, o valor deve, usar?

AA configuração de fator de preenchimento realmente pode ser usada para parcialmente minimizar a necessidade de manutenção do índice, mas raramente pode ela ser usada para remover completamente a necessidade. Em poucas palavras, a configuração do fator de preenchimento instrui o mecanismo de armazenamento para deixar uma determinada porcentagem de espaço livre em páginas de índices de cluster e que não estão em cluster quando são criadas ou reconstruídos. (Observe que a configuração do fator de preenchimento não é mantida durante operações de inserir/atualizar/excluir regulares.) Um fator de preenchimento de 90, por exemplo, deixa espaço livre de 10 %. Fatores de preenchimento de 0 ou 100 ambos deixe sem espaço livre (isso tem sido a fonte de muita confusão).

A idéia é que espaço é deixado nas páginas, que permite registros na página para expandir ou novos registros a serem inseridos na página sem causar uma operação cara, fazendo com que a fragmentação chamada uma divisão de página. Você especifica uma porcentagem de espaço livre para que as páginas podem se tornar mais constantemente total até que ocorra a próxima operação de manutenção do índice, que redefine o fator de preenchimento novamente. O truque é escolher uma porcentagem que minimize divisões de página entre operações de manutenção do índice.

Para um banco de dados OLTP (processamento de transações on-line), não há nenhuma resposta fácil, exceto para escolher um fator de preenchimento para cada índice com base em trial e erro. Para depósitos de dados, onde os índices não alterar, o fator de preenchimento deve ser 100 % (ou seja fique sem espaço livre nas páginas). É bastante incomum que o fator de preenchimento padrão para um banco de dados é alterado de padrão de 100 %, pois os fatores de preenchimento melhores para vários índices são geralmente diferentes. O tópico manuais online do SQL Server 2008" Fator de preenchimento"tem muito mais informações sobre isso.

Uma outra opção é alterar o índice para que as divisões de página não ocorrem. Isso pode envolver alteração a chave de índice para que insere não sejam aleatório (por exemplo, usando não uma chave primária de GUID aleatória) ou não permitindo operações que alterar o tamanho de colunas de comprimento variável.

QVamos ser movendo para SQL Server 2008 assim SP1 vem check-out e um dos recursos que queremos frente usar é FILESTREAM, e remove o limite de tamanho 2 GB para valores de coluna. Antes de começar a criar a próxima versão do nossa esquema para usar o tipo de dados FILESTREAM, existe são qualquer desvantagens ou problemas, lembre-se de que pode causar problemas na produção?

AÉ sempre uma idéia inteligente para determinar todas as características de um novo recurso antes de criá-lo em um novo esquema ou aplicativo, especialmente um que utiliza tecnologias fora do SQL Server, como é o caso com FILESTREAM. A maioria das informações que você precisa está contido em um documento que escrevi para a equipe do SQL Server chamado" Armazenamento FILESTREAM no SQL Server 2008." Eu recomendo você ler esse documento para uma discussão completa, mas irá resumir as principais áreas de preocupação aqui.

Em primeiro lugar, os dados FILESTREAM são armazenados no sistema de arquivos NTFS, em vez de em arquivos de dados do SQL Server. Há diversas etapas de configuração que devem ser seguidas para garantir que o NTFS desempenho bem com muito grande número de arquivos em um único diretório, como desativar a geração de nome 8.3, definindo o NTFS cluster tamanho adequadamente e, possivelmente, separando o FILESTREAM dados para separar discos físicos dos outros dados.

Em segundo lugar, você deve garantir que o tamanho médio dos dados armazenados usando FILESTREAM será 1 MB ou superior. Pesquisa mostrou que para tamanhos de dados de 256KB menor e, às vezes, entre 256 KB e 1 MB, melhorar o desempenho pode ser obtido ao armazenar os dados diretamente dentro do SQL Server em vez de usando um mecanismo como FILESTREAM.

Em terceiro lugar, você deve considerar as operações que serão executadas nos dados de FILESTREAM. Atualizações parciais não há suporte para dados FILESTREAM, portanto, mesmo atualizar um byte de um valor de dados 200MB resultará em um valor de 200MB inteiramente novo que está sendo criado. Além de ser uma operação cara, isso pode levar à fragmentação de nível por NTFS, que pode reduzir ainda mais o desempenho. Se atualizações parciais estão comuns no aplicativo, algum tipo de mecanismo de processamento em lotes pode ser necessário para evitar atualizações repetidas para um único valor FILESTREAM.

Por último, você deve considerar a compatibilidade de recurso entre de FILESTREAM com tecnologias de alta disponibilidade. FILESTREAM totalmente oferece suporte a operações de backup e restauração (incluindo a recuperação de ponto no tempo), o envio de log e replicação. Não, no entanto, é compatível com o espelhamento do banco de dados de nenhuma forma no SQL Server 2008. (Eu já foi disse que isso será corrigido na próxima versão do SQL Server.)

Isso é apenas um gosto das coisas a serem considerados. Para a imagem completa, leia o white paper. Como com qualquer novo recurso, porém, antes de criar um aplicativo em torno dele, certifique fazer alguns testes abrangentes para ver se seus recursos de atender às suas necessidades. Considerando que FILESTREAM também abrange o armazenamento NTFS, também fazer pré-desempenho e recuperação de desastres teste para garantir que nada férias você backup quando você entrar ao vivo.

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.