Por: Brady Upton | Atualizado em: 2016-06-22 | Comentários (5) | Relacionados: Mais > Fragmentação e a Manutenção do Índice
Problema
todos Nós já ouvimos falar de banco de dados/índice de fragmentação (e se você não tiver, continuereading), mas o que é isso? É um problema? Como sei se está na minha base de dados?Como posso consertá-lo? Estas perguntas podem ser uma dica, mas vou tentar dar-te uma ideia de cada uma delas nesta dica.
Solution
sem entrar em muitos detalhes, o servidor SQL armazena dados em páginas 8KB. Quando inserimos dados em uma tabela, o servidor SQL irá alocar uma página para armazenar que sem dados os dados inseridos são mais de 8KB em que ele iria abranger várias páginas.Cada página é atribuída a uma tabela. Se criarmos 10 tabelas então teremos 10 páginas diferentes.
à medida que introduz dados numa tabela, os dados Irão primeiro para o ficheiro de registo de operações. O ficheiro de registo de tradução é um registo sequencial, na medida em que introduz, actualiza e apaga registos.o registo irá registar estas transacções do início ao fim. Por outro lado, o ficheiro de dados não éequencial. O arquivo de log irá enviar os dados para o arquivo de dados criando páginas em todo o lugar.
Agora que temos uma ideia de como os dados são armazenados, o que isso tem a ver com a fragmentação?
existem dois tipos de fragmentação: fragmentação interna e fragmentação externa.
fragmentação interna do servidor SQL
fragmentação interna do servidor SQL é causada por páginas que têm muito espaço livre.Vamos fingir que no início do dia temos uma tabela com 40 páginas que estão 100% cheias, mas até o final do dia temos uma tabela com 50 páginas que estão apenas 80% cheias por causa de várias declarações de apagar e inserir durante todo o dia.Isto causa uma questão porque agora, quando precisamos ler a partir desta tabela, Temos toscan 50 páginas em vez de 40, o que pode resultar em uma diminuição no desempenho.Vamos ver um exemplo rápido e sujo.
digamos que tenho a seguinte tabela com uma chave primária e um índice não agrupado no primeiro nome e no último nome:
vou falar sobre formas de analisar a fragmentação mais tarde nesta dica, mas por agora podemos clicar com o botão direito no índice, clicar em Propriedades e fragmentação para ver a fragmentação e a plenitude da página. Este é um índice novinho em folha, por isso está a 0% de fragmentação.
Vamos INSERIR 1000 linhas nesta tabela:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
Agora, vamos verificar nosso índice novamente:
pode ver que o nosso índice se torna 75% fragmentado e a percentagem média de respostas completas (plenitude da Página) aumenta para 80%. Esta tabela ainda é tão pequena que 75% de fragmentação provavelmente não causaria quaisquer problemas de desempenho, mas como a tabela aumenta em tamanho e contagem de páginas aumenta você pode ver desempenho degradar. Você também pode ver a partir da imagem acima que esta tabela foi de 0 Páginas para 4.
SQL Server External Fragmentation
External Fragmentation is caused by pages that are out of order. Vamos falar no início do dia, temos uma mesa perfeitamente ordenada. Durante o dia, emitimos centenas de declarações de atualização, possivelmente deixando algum espaço vazio em uma página e tentando encaixar espaço em outras páginas. Isto significa que o nosso armazenamento tem que saltar em torno de obter os dados necessários em vez de ler em uma direção.
Analisar a fragmentação do servidor SQL
então a fragmentação é um problema? Acredito que sim. Se você pode armazenar sua base de dados inteira em memória ou se sua base de dados é lido apenas então eu não me preocuparia com isso, mas a maioria de nós não tem esse luxo.Trabalhei em milhares de servidores e analisar níveis de fragmentação é uma das primeiras coisas que vejo. Na verdade, apenas corrigindo a fragmentação, eu vi até 200% de melhorias no desempenho da consulta.falando de analisar os níveis de fragmentação você pode estar se perguntando como podemos fazer isso. Bem, existem algumas maneiras…
DBCC SHOWCONTIG – esta funcionalidade é antiga e será removida em versões futuras do servidor SQL, mas se ainda estiver a usar o servidor sql 2000 ou em baixo, isto irá ajudar. Em vez de escreveres sobre isso, eu digo-te onde Ou podes verificar a ponta do Chad Boyd. Ambos são bons recursos.
sys.dm_db_index_physical_stats – Introduzido no SQL Server 2005, esta dinâmica managementview (DMV) retorna o tamanho e a fragmentação de informações para os dados e índices da tabela ou exibição especificada.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
Este é provavelmente o mais amplamente utilizado o método de análise de fragmentação. Você pode ver a partir da imagem acima que eu tenho um índice chamado IX_RPCust_1 na tabela theRPCust que é 98,83% fragmentado. Pode ver mais informações sobre esta DGV do Arshad Ali.software de terceiros-software de terceiros irá ajudar a identificar fragmentado do mesmo modo que você não precisa. Greg Robidoux oferece uma boa dica sobre a gerência SQL Defrag da Idera aqui.
Fixing SQL Server Fragmentation
Now that we have found fragmentation in the database, how do we fix it? Como os índices de análise, existem várias maneiras.
SQL Server Maintenance plans – Maintenance plans are shipped with SQLServer and are nice for some tasks. Na minha opinião, a manutenção do índice não é uma delas. Você pode adicionar uma tarefa de índice de reconstrução ou reorganizar a tarefa de índice no maintenanceplan, mas o problema é que você realmente não pode aplicar qualquer lógica ao plano. Sem logic, o plano irá reconstruir/reorganizar cada índice. Alguns índices podem não estar fragmentados para que eles não precisam ser reconstruídos. Alguns índices podem ter uma fragilidade mínima, de modo que ficariam bem com uma reorganização. Não há forma de especificar isto num maintenanceplan. Servidor InSQL 2016 existem novas opções para gerenciar a fragmentação.
Script Personalizado-scripts personalizados são provavelmente o caminho a seguir porque você pode aplicar lógica para verificar a fragmentação do índice e, em seguida, decidir se esquivar o índice, reorganizar ou reconstruir. Eu useOla Hallengren scripts e eles fazem exatamente o que eu preciso que eles façam. MSSQLTips.a com também tem as dicas de fragmentação do servidor de fontes-SQL e manutenção de índices.software de terceiros-software de terceiros vai ajudar a desfragment indexesso você não tem que.
próximas etapas
- Esta ponta dá-lhe uma ideia de alguns dos componentes envolvidos na indexação. MSSQLTips.com oferece dicas mais abrangentes focadas em cada um dos assuntos aqui mencionados.certifique-se de verificar o tutorial de fragmentação do servidor SQL de Boyd, que oferece grande insight em inúmeros tópicos sobre fragmentação
última atualização: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…