P+R SQL: Diminuindo, cada vez maior e bancos de dados de um novo projeto
Bancos de dados SQL vêm em todas as formas e tamanhos e esquemas. Este mês, nossas especialistas em SQL ajuda condensado crescendo e bancos de dados de um novo projeto.
Paul S. Randal
O banco de dados incrível reduzindo
P: Podemos ocasionalmente estiver forçados a executar redução nos nossos bancos de dados por causa de uma falta de espaço em disco, mesmo que eu sei que isso pode causar problemas de desempenho. Podemos cuidar de qualquer fragmentação do índice posteriormente. Você pode explicar por que a redução parece executada muito lenta para alguns bancos de dados de outras pessoas, mesmo quando elas são de tamanho semelhante?
R: Fico feliz por que você é do cognizant dos efeitos colaterais da execução de uma redução do banco de dados. Eu também perceba que, às vezes, é apenas inevitável.
Atividade simultânea de banco de dados e o esquema de tabelas no banco de dados podem afetar o tempo de execução de uma operação de redução do banco de dados. Isso significa que dois bancos de dados de tamanho igual, mas com diferentes esquemas podem demorar consideravelmente diferentes quantidades de tempo para reduzir.
Encolher funciona através da movimentação páginas de arquivo de dados para consolidar espaço livre no final do arquivo, em seguida, retornará ao sistema de arquivos (reduzindo o tamanho do arquivo data). Para mover uma página de arquivo de dados, SQL Server devem adquirir um bloqueio exclusivo na página. Isso significa que ninguém mais pode ter bloqueios de qualquer ou todos os registros na página. Se não houver atividade simultânea no banco de dados envolve a aquisição de bloqueios, encolher foi bloqueado e, em seguida, tem de esperar pelo bloqueio, ela precisa. Isso fará com que a redução de levar mais tempo de execução que, se não houver nenhuma outra atividade no banco de dados.
Outro fator ao encolher move uma página de arquivo de dados é o caso outras estruturas de banco de dados tiverem físicos ponteiros para os dados na página que está sendo movida, ele deverá atualizar os ponteiros físicos com o novo local da página. Isso não é um problema, exceto quando uma tabela é uma pilha (possui índice de cluster) e/ou quando uma tabela tem um ou mais colunas de objeto grande (LOB) armazenado (separados do registro de dados da tabela) fora da linha.
Quando uma tabela é uma pilha, todos os índices não clusterizados no heap contêm físicos ponteiros para os registros da tabela de dados. Ao encolher move uma página de dados da tabela, os índices não clusterizados precisam ser atualizado. SQL Server faz isso chamando o processador de consultas para fazer a manutenção de índice de índices não clusterizados 100 linhas cada vez.
Quando uma tabela tem dados LOB de fora da linha, os registros de dados apontam para dados LOB fora da linha. No entanto, há sem back-ponteiro dos dados LOB para os registros de dados. Isso significa que quando a redução se move uma página de texto (que contém os dados fora da linha LOB,), todos os registros de dados que apontem para os dados LOB nessa página devem ser atualizados. Como não há nenhum ponteiros de trás, ele deve executar uma verificação de tabela para localizar os registros de dados correto para atualizar. Como você pode imaginar, esse processo pode ser muito lento para uma tabela com uma grande quantidade de dados LOB.
Embora a redução pode ser lenta, do SQL Server 2005 em diante fornece através da coluna percent_complete sys.dm_exec_requests gerenciamento dinâmico de modo de exibição de relatório de andamento. Você também pode monitorar o contador de desempenho de bytes/s de reduzir a movimentação de dados no objeto de desempenho de bancos de dados para ver quão rápido redução está em andamento.
Permitir que ele automaticamente-expandir
P: Eu sou um DBA novo e que eu tenha lido muitas informações online sobre as práticas recomendadas para configurações de banco de dados. Eu estou confusos com os modos de exibição conflitantes sobre o crescimento automático deve ser habilitada ou não. Pode me apenas desativá-la sem causar nenhum problema?
R: A resposta simples é que você sempre deve habilitar o aumento automático, mas não contam com ele. A prática geral é monitorar transações e dados de log de tamanho/uso de arquivos e pró-ativamente crescê-los (ou investigue o crescimento repentino inesperado). Habilitar crescimento automático para os casos de emergências quando quem é responsável pelo monitoramento de uso/tamanho do arquivo não está disponível imediatamente para gerenciar os arquivos.
Se o aumento automático não está ativado para a transação de log de arquivo e os preenchimentos de arquivo, e em seguida, todas as gravação atividade no banco de dados será impedida até que mais espaço é disponibilizado no log de transações. E se o crescimento automático não está ativado para os arquivos de dados, operações de inserção ou operações de manutenção de banco de dados como recriar os índices pode falhar.
A parte complicada é descobrir a definição de aumento automático. No SQL Server 2005 em diante, o aumento automático padrão para arquivos de log de transações está 10 por cento e 1 MB para arquivos de dados. No entanto, um com base em porcentagem de aumento automático significa que como expandir os seus arquivos, portanto, também, faz o nível de aumento automático. Isso também significa que o tempo que leva também pode aumentar se você Don tiver habilitada a inicialização instantânea de arquivo. Portanto, a ambos os tipos de arquivo devem ter um aumento automático fixo para crescimento automático comportamento é previsível.
Ter um aumento de automático extremamente grande ou com base no percentual pode ser especialmente problemático para arquivos de log de transações. A inicialização instantânea de arquivo não é uma opção, qualquer espaço recém-alocado do arquivo deve ser inicializado para zero. Durante a inicialização do zero ocorre, todos gravar a atividade do log de transações está bloqueada. Portanto, faz sentido para equilibrar o transação de log arquivo aumento automático para que ele é grande o suficiente que as operações podem continuar por algum tempo, mas não muito grande que o fluxo de operações é interrompido por muito tempo.
Arquivos de dados, um aumento automático de 1 MB é ridiculamente pequeno, mas é difícil determinar o valor correto. Isso depende se você deseja que o aumento automático para uma medida de emergência provisórias, ou para substituir o gerenciamento de tamanho de arquivo de dados manual. Ele depende também no novo quanto espaço você precisa diariamente para acomodar os dados que está sendo inseridos no banco de dados. O resultado final é: Você deve permitir um crescimento automático e defini-la a uma quantidade apropriada, porcentagem não.
Esquema de armazenamento
P: Eu estou redimensionando nossa esquema de banco de dados para que as consultas são mais eficientes. Algumas das tabelas envolvidas têm muitos dados de caractere e eu gostaria de certificar-se de que eu estou armazená-los da maneira mais eficiente. Existem quaisquer diretrizes ou práticas recomendadas que você pode compartilhar?
R: A maneira como você optar por armazenar os dados LOB pode ter um enorme impacto no desempenho de consulta, portanto, é vital que você escolha a técnica de à direita. Uma análise detalhada de todas as opções está além do escopo desta coluna, mas aqui estão algumas diretrizes:
Em primeiro lugar, os dados sempre será menor do que de 8000 bytes? Nesse caso, tente um tipo de dados char (n) ou varchar (n), mas não uma verdadeiras LOB tipos de dados como XML, (n)varchar(max), varbinary (max), texto (n) ou imagem, a menos que seja absolutamente necessário. Se você precisar de um tipo de dados LOB verdadeiro causa do tamanho dos dados, Don use texto (n) ou a imagem como esses dados de tipos foram preteridos no SQL Server 2005. Elas não são tão funcionais quanto os outro, mais recentes LOB tipos de dados.
Em segundo lugar, se você precisar de um tipo LOB true, considere se deve armazenar os dados na linha (na mesma tabela de registro de dados como colunas na tabela) ou fora de linha (armazenados em páginas de arquivo de dados separado com um link de registro de dados da tabela). Se você usar os dados LOB com freqüência, seria melhor para armazená-lo na linha, como consultas podem recuperá-lo com mais eficiência. Caso contrário, ele geralmente é melhor para armazená-los fora da linha. Consultas ocasionais pagam um pouco alto custo para recuperar os dados LOB, mas os registros de dados será menores, ultrapassando o armazenamento de dados mais densa e geral melhor desempenho de consulta. Observe que você pode apenas dados de armazenamento de LOB de 8000 bytes na linha ou de qualquer quantidade é possível devido as outras colunas no registro de dados — após o qual ele tem automaticamente colocados fora da linha.
Em terceiro lugar, se uma tabela contiver uma coluna LOB, operações de indexação on-line serão impedidas para os índices que incluem a coluna LOB. Por definição, isso afeta o índice de cluster da tabela. Por esse motivo, algumas pessoas armazenam os dados LOB em uma tabela separada totalmente (particionamento vertical para fora desta coluna LOB) e, em seguida, executam uma operação de associação entre a tabela principal e a tabela LOB, quando os dados LOB são requeridos por uma consulta. Isso provoca um pouco mais armazenamento por causa da complexidade do JOIN, mas permite que mais uma opção da estratégia de manutenção do índice.
Você também pode se preocupar largura fixa em relação a tipos de dados de comprimento variável e, possivelmente, até mesmo exige acesso de fluxo contínuo rápido aos dados, nesse caso, você deve considerar o tipo de dados de FILESTREAM do SQL Server 2008. Para uma análise mais detalhada de todos os tipos de armazenamento em dados LOB, consulte meu blog para postar “ de prioridade de escolha direita técnica de armazenamento de LOB ”.
Verificações de críticas e saldos
P: Eu estou retrabalhando as práticas de manutenção de banco de dados de nossa empresa e vou começar a executar verificações de DBCC em nossos bancos de dados críticos. A freqüência com que me deve executar uma verificação de cada banco de dados?
R: Verificações de consistência pró-ativa são uma parte essencial de qualquer plano de manutenção de banco de dados abrangente — para bancos de dados de usuário e do sistema. Também é importante usar um método de verificação de página. Para bancos de dados do SQL Server 2005 e posteriores, habilite somas de verificação. Bancos de dados do SQL Server 2000, use a detecção de página interrompida.
Como diz respeito verificações de consistência, é difícil dar uma resposta absoluta para a freqüência de executá-los. Eu geralmente recomendo executá-las sempre que possível, pelo menos uma vez por semana. A freqüência ideal de verificações de consistência para que você é um clássico “ depende. ”
Aqui estão alguns dos fatores a considerar:
Em primeiro lugar, o que é a sua janela de manutenção? As verificações de consistência consumir grandes quantidades de CPU, memória e recursos de e/S, portanto, se a janela de manutenção de quando esses recursos podem ser sobra é menor que o tempo necessário para executar todas as verificações de consistência, talvez não seja capaz de verificar todos os bancos de dados ao mesmo tempo. Talvez seja necessário escalonar as verificações de consistência de uma semana inteira ou mesmo descarregar as verificações de consistência de um sistema que não seja de produção (por restaurar um backup e executar as verificações de consistência no banco de dados restaurado).
Em segundo lugar, como estável é o subsistema de e/S em que os bancos de dados estão armazenados? Se o subsistema de e/S está tendo problemas, convém executar verificações de consistência sempre que possível, para obter a indicação mais cedo possível corrupção. Na minha experiência, a mais do que a corrupção vai despercebida, mais abrangente, ele recebe e mais difícil é recuperar o banco de dados ao mesmo tempo em que o objetivo de ponto de recuperação e o objetivo de tempo de recuperação de reunião.
O resultado final é que cabe a você e seu nível de conforto. Em agosto de 2009, realizou uma pesquisa no meu blog e check-out de 276 pesquisados, 37% semanalmente executar verificações de consistência e um mais de 25% executá-los diariamente. Você pode ver os resultados completos da minha pesquisa, junto com muito mais obter informações sobre como descobrir a freqüência de verificação no www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx de .
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.