Reduzir a fragmentação da tabela do servidor SQL sem adicionar / largar um índice agrupado?
Tenho uma grande base de dados (dados 90GB, índices 70GB) que tem vindo a crescer lentamente no último ano, e o crescimento/alterações tem causado uma grande quantidade de fragmentação interna não só dos índices, mas das próprias tabelas.
É fácil resolver o (grande número de) índices muito fragmentados-uma reorganização ou reconstrução tratará disso, dependendo de quão fragmentados eles são-mas o único conselho que eu posso encontrar sobre a limpeza da fragmentação atual da tabela é adicionar um índice agrupado para a tabela. Eu o largaria imediatamente depois, já que não quero um índice agrupado na mesa indo adiante, mas há outro método de fazer isso sem o índice agrupado? Um comando "DBCC" que fará isto? Obrigado pela ajuda.5 answers
Problema
Vamos ter alguma clareza, porque este é um problema comum, um problema sério para todas as empresas que usam o servidor SQL.Este problema, e a necessidade de criar um índice agrupado, é mal entendido.
Concordei que ter um índice cluster permanente é melhor do que não ter um. Mas esse não é o ponto, e vai levar a uma longa discussão de qualquer maneira, então vamos deixar isso de lado e focar na questão postada.
A questão é, tu tens uma quantidade substancial fragmentação na pilha . Você continua chamando isso de "tabela", mas não existe tal coisa no nível de armazenamento de dados físicos ou Datastrutura. Uma tabela é um conceito lógico, não físico. É uma coleção de estruturas de dados físicos. A coleção é uma de duas possibilidades:
Heap
mais todos os índices Não agrupados
mais cadeias de texto/imagemOu um índice agrupado
(elimina o Monte e Um índice não agrupado)
mais todos os índices Não agrupados
mais cadeias de texto / imagem.
Os cabeçalhos ficam muito fragmentados; quanto mais intercalados (aleatórios)os espaços inseridos/apagados/actualizações existirem, mais fragmentação.
Não há maneira de limpar o Monte, como está. O MS não fornece uma instalação (outros vendedores fazem).
Solução
No entanto, sabemos que criar um índice agrupado reescreve e re-ordena o Heap, completamente. O método (não trick), portanto, é criar um índice agrupadoapenas com o propósito de Des-fragmentar o Monte, e larga-a depois. Você precisa de espaço livre no db de table_size x 1,25.Já agora, por todos os meios, use o FILLFACTOR para reduzir a fragmentação do futuro . O Heap irá então ocupar mais espaço alocado, permitindo futuras Inserções, apagamentos e expansões de linhas devido a atualizações.
Nota
Note que existem três níveis de fragmentação; trata-se apenas do Nível III, fragmentação dentro do Monte, o que é causado pela falta de um índice agrupado
Como uma tarefa separada, em algum outro momento, você pode querer contemplar a implementação de um índice cluster permanente, que elimina completamente a fragmentação ... mas isso é separado do problema postado.
Resposta ao Comentário
Ainda não. Não lhe chamaria "limitação".sqlryan:
Enquanto isto não dá me uma solução mágica para o meu problema, deixa bem claro que o meu problema é resultado de uma limitação do servidor SQL e adicionar um índice agrupado é a única maneira de "desfragmentar" o Monte.
O método que dei para eliminar a fragmentação no Heap é criar um índice agrupado, e depois largá-lo. Ie. a título temporário, cuja única finalidade é corrigir o Fragmentacao.
-
Implementar um índice agrupado na tabela (permanentemente) é uma solução muito melhor, porque reduz a fragmentação global (a infra-estrutura de dados ainda pode ficar fragmentada, consulte informações detalhadas em links abaixo), o que é muito menos do que a fragmentação que ocorre em um monte.
Cada tabela em um banco de dados relacional (exceto tabelas "pipe" ou "fila") deve ter um índice agrupado, a fim de tirar proveito de seus vários beneficio.
O índice agrupado deve estar em colunas que distribuem os dados (evitando conflitos de inserção), nunca ser indexado em uma coluna monotonicamente crescente, como o ID de registro 1, o que garante a inserção de um ponto quente na última página.
1. Os IDs de registro em cada arquivo tornam seu" banco de dados " um sistema de arquivo de registro não-relacional, usando SQL meramente para conveniência. Tais arquivos não têm nenhuma da integridade, potência, ou velocidade de Banco.
Andrew Hill:
você seria capaz de comentar mais sobre "Note que há três níveis de fragmentação; isto trata apenas do Nível III" -- quais são os outros dois níveis de fragmentação?
Em MS SQL e Sybase ASE, existem três níveis de fragmentação, e dentro de cada nível, vários tipos diferentes . Tenha em mente que ao lidar com a fragmentação, devemos nos concentrar em infra-estruturas de dados, não está em tabelas (uma tabela é uma coleção de estruturas de dados, como explicado acima). Os níveis são:
Nível I * Infra-Estruturas Extra-Datais
Fora da infra-estrutura em causa, através ou dentro da base de dados.Nível II • infra-estruturas de Dados
Na infra-estrutura em causa, acima das páginas (em todas as páginas)
Este é o nível mais frequentemente abordado pelo DBAs.Nível III • Página
No Infra-estrutura de dados em causa, dentro das páginas
Definição De Fragmentação
Impacto Da Fragmentação
Tipo De Fragmentação
Note que o método que eu dei é o Nível II, corrige o Nível II e III fragmentação.
O índice agrupado remove a fragmentação ordenando a chave do conjunto, mas você diz que esta chave não seria possível para uso futuro. Isto levanta a questão: por que desfragmentar usando esta chave em tudo?
{[[2]} faria sentido criar esta chave agrupada e mantê-la, como você obviamente quer/precisa dos dados ordenados dessa forma. Você diz que as alterações de dados iriam incorrer penalidades do movimento de dados que não podem ser suportadas; você já pensou em criar o índice com um valor inferiorFILLFACTOR do que o valor padrão? Dependendo dos padrões de mudança de dados, você pode se beneficiar de algo tão baixo quanto 80%. Você então tem 20% de espaço 'não usado' por página, mas o benefício de páginas mais baixas se divide quando os valores chave agrupados são alterados.
Isso pode ajudar-te?
Você pode talvez compactaro heap executando DBCC SHRINKFILE com NOT truncado.
Com base em comentários, vejo que não testaste com um índice de cluster permenente. Para colocar isso em perspectiva, temos uma base de dados com 10 milhões de novas linhas por dia com índices agrupados em todas as tabelas. As "lacunas" apagadas serão removidas através de escalonamento do Índice ALTER (e também de encaminhamento de ponteiros/separadores de páginas).A sua tabela de 12GB pode ser de 2GB após indexação: tem apenas 12GB mas também está fortemente fragmentado.
Outra desvantagem de ter o heap é o IO extra associado a quaisquer filas de aves.
Achei o artigo abaixo eficaz quando me perguntaram se havia alguma prova de que precisávamos de um índice de clusted permanentemente na tabela
O problema de que ninguém está a falar é a fragmentação dos ficheiros de dados ou dispositivos de registo no(S) próprio (s) Disco (s)!! Todos falam sobre a fragmentação dos índices e como evitar/limitar essa fragmentação.
Para que saibas: quando criares uma base de dados, especificas o tamanho inicial da .MDF junto com o quanto crescerá quando precisa crescer. Faz o mesmo com o ... Ficheiro LDF. NÃO HÁ NENHUMA GARANTIA DE QUE QUANDO ESTES DOIS ARQUIVOS CRESCEM QUE O ESPAÇO EM DISCO ALOCADO PARA O ESPAÇO EXTRA EM DISCO NECESSÁRIO SERÁ FISICAMENTE CONTÍGUO COM O ESPAÇO EM DISCO EXISTENTE ATRIBUÍDO!!
Cada vez que um desses dois arquivos de dispositivo precisa se expandir, há a possibilidade de fragmentação do espaço em disco rígido. Isso significa que as cabeças no disco rígido precisam trabalhar mais (e levar mais tempo) para se mover de uma seção do disco rígido para outra seção para acessar os dados necessários na base de dados. É análogo a comprar um pequeno terreno e construir uma casa que cabe naquela terra. Quando você precisa expandir a casa, você não tem mais terras disponíveis a menos que você comprar o lote vazio ao lado - exceto - e se alguém, entretanto, já comprou essa terra e construiu uma casa sobre ela? Então não podes expandir a tua casa. A única possibilidade é comprar outro terreno no "bairro" e construir outra casa sobre ele. O problema torna-se-você e dois dos seus filhos viveriam na casa A e sua esposa e terceiro filho viveriam em Casa B. Isso seria uma dor (desde que você ainda fosse casado). A solução para remediar esta situação é "comprar um terreno muito maior, pegar na casa existente (base de dados), movê-la para o terreno maior e, em seguida, expandir a casa para lá". Como se faz isso com uma base de dados? Faça um backup completo, deixe cair o banco de dados (a menos que você tenha bastante espaço em disco livre para manter tanto o banco de dados fragmentado antigo-apenas no caso -, bem como o novo banco de dados), criar uma marca novo banco de dados com abundância de espaço em disco inicial alocado (sem garantia de que o sistema operacional irá garantir que o espaço que você solicitar será contíguo) e, em seguida, restaurar o banco de dados no novo espaço de banco de dados recém criado. Sim-é uma dor de fazer, mas eu não sei de qualquer software "Desfragmentador de disco automático" que irá trabalhar em arquivos de banco de dados SQL.