Como identificar e resolver a fragmentação de índice do SQL Server

Como identificar e resolver a fragmentação de índice do SQL Server

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Neste artigo, aprenderemos como identificar e resolver a fragmentação de índice no SQL Server. A identificação da fragmentação do índice e a manutenção do índice são partes importantes da tarefa de manutenção do banco de dados. O Microsoft SQL Server continua atualizando as estatísticas do índice com a atividade Inserir, Atualizar ou Excluir sobre a tabela. A fragmentação do índice é o valor do desempenho do índice em porcentagem, que pode ser buscado pelo SQL Server DMV. De acordo com o valor de desempenho do índice, os usuários podem levar os índices em manutenção revisando a porcentagem de fragmentação com a ajuda da operação Reconstruir ou Reorganizar.

Por que a porcentagem de fragmentação do índice varia?

A porcentagem de fragmentação do índice varia quando os pedidos lógicos de página não se coordenam com o pedido físico da página na alocação de página de um índice. Com a modificação dos dados na tabela, as informações podem ser redimensionadas na página de dados. A página estava cheia antes da operação de atualização na tabela. No entanto, foi possível encontrar espaço livre na página de dados com uma operação de atualização na tabela. Os usuários podem observar a ordem das páginas perturbadoras com a enorme operação de exclusão em cima da mesa. Juntamente com as operações de atualização e exclusão, a página de dados não será uma página cheia no máximo ou vazia. Portanto, o espaço livre não utilizado aumenta a incompatibilidade de pedidos entre a página lógica e a página física com o aumento da fragmentação, o que pode causar o pior desempenho da consulta e consumir mais recursos do servidor.

É mais importante esclarecer que a fragmentação do índice afeta o desempenho da consulta apenas com a verificação da página. Nesses casos, também aumenta as chances de desempenho ruim de outras solicitações SQL, porque a consulta com o índice fragmentado alto sobre a tabela leva mais tempo para ser executada e consome mais recursos, como cache, CPU e E / S. Portanto, o restante das solicitações SQL acha difícil concluir a operação com os recursos inconsistentes do servidor. Mesmo o bloqueio pode ocorrer pela operação Atualizar e excluir, porque o otimizador não coleta as informações da fragmentação do índice enquanto gera o plano de execução para a consulta.

Pode haver vários índices criados em uma única tabela com a combinação de várias colunas e cada índice pode ter uma porcentagem de fragmentação diferente. Agora, antes de torná-lo apropriado ou obter um índice em manutenção, os usuários precisam encontrar esse valor limite no banco de dados. A instrução T-SQL abaixo é uma maneira eficiente de encontrá-la com detalhes do objeto.

Leia Também  Como alterar rapidamente os objetivos de nível de serviço do Banco de Dados SQL do Azure - Uma visão do SQLEspresso

Encontre o status de fragmentação de índice usando a instrução T-SQL

Encontrar fragmentação de índice

Aqui, podemos ver que a porcentagem média máxima de fragmentação é perceptível como 99%, que deve ser acoplada a uma ação para reduzir a fragmentação com as opções de REBUILD ou REORGANIZE. REBUILD ou REORGANIZE é o comando de manutenção de índice que pode ser executado com a instrução ALTER INDEX. Os usuários também podem executar este comando usando o SSMS.

Reconstruir e reorganizar o índice usando o SQL Server Management Studio (SSMS)

Localize e expanda a tabela em Pesquisador de objetos >> Abrir índices >> Clique com o botão direito do mouse no índice de destino >> Reconstruir ou reorganizar.

Índice de reconstrução

Como visível na imagem acima, REBUILD e REORGANIZE são as duas opções disponíveis para executar a operação de recorte na página. Idealmente, essa operação deve ser realizada fora do horário de pico para evitar seu impacto em outras transações e usuários. O Microsoft SQL Server Enterprise Edition suporta os recursos online e offline do índice com o índice REBUILD.

ÍNDICE RECONSTRUÍDO

INDEX REBUILD sempre baixa o índice e o reproduz com novas páginas de índice. Esta atividade pode ser executada em paralelo usando uma opção online (Enterprise Edition) com o comando ALTER INDEX, que não afeta as solicitações e tarefas em execução de uma tabela semelhante.

O índice REBUILD pode ser definido online ou offline usando os comandos SQL abaixo:

Se um usuário executar o REBUILD INDEX offline, o recurso de objeto (Tabela) do índice não estará acessível até o final da conclusão do processo de REBUILD. Também afeta inúmeras outras transações, associadas a esse objeto. A operação de reconstrução do índice recria o índice. Portanto, ele gera novas estatísticas e anexa os registros de log do índice no arquivo de log de transações do banco de dados também.

Leia Também  Uso do espaço de arquivos e grupos de arquivos do SQL Server - Parte 1

Por exemplo, antes de recriar o índice, tomemos a atribuição atual de páginas para o índice do banco de dados AdventureWorks, tabela Sales.OrderTracking e índice nomeado IX_OrderTracking_CarrierTrackingNumber.

Alocação de Página

Operação de reconstrução

Aqui, existem 1961 páginas no arquivo de banco de dados para esse índice e as 5 primeiras páginas são 861, 862, 1627, 1628 e 1904 na ordem do número da página. Agora, vamos reconstruir o índice usando o SSMS.

A operação Index REBUILD foi concluída com êxito e obtém referências de alocação de página para o mesmo índice com a ajuda da mesma consulta T-SQL novamente.

Alocação de página

Após a reconstrução do índice, a contagem de páginas atualizadas é 1457, que era 1961 antes. Se você verificar as 5 primeiras páginas alocadas do mesmo índice, elas foram alteradas com as novas referências de página. Ele pressupõe que o índice seja descartado e criado novamente. Devemos verificar a porcentagem de fragmentação atualizada para o mesmo índice e, como pode ser visto abaixo, agora é de 0,1%.

Porcentagem de fragmentação de índice atualizada

O índice em cluster REBUILD sobre a tabela também afeta outros índices da tabela, porque o índice em cluster REBUILD reconstrói também o índice não em cluster da tabela. Execute a operação de reconstrução em todos os índices da tabela ou banco de dados juntos; um usuário pode usar DBCC DBREINDEX () comando.

ÍNDICE REORGANIZADO

O comando REORGANIZE INDEX reordena a página de índice expulsando o espaço livre ou não utilizado na página. Idealmente, as páginas de índice são reordenadas fisicamente no arquivo de dados. REORGANIZAR não elimina e cria o índice, mas simplesmente reestrutura as informações na página. REORGANIZE não tem nenhuma opção offline e REORGANIZE não afeta as estatísticas em comparação com a opção REBUILD. REORGANIZAR sempre executa online.

Por exemplo, antes de executar REORGANIZE sobre o índice, vamos fazer a leitura de fragmentação do banco de dados ‘AdventureWorks’, tabela ‘Sales.OrderTracking’ e índice chamado ‘IX_OrderTracking_SalesOrderID’.

Fragmentação de Índice

Aqui, a porcentagem de fragmentação do índice é 98,39 antes de REORGANIZAR. A lista abaixo na imagem são as páginas de alocação para o índice.

Leia Também  Como filtrar objetos no SSMS 2016

Alocação de páginas

Aqui, um total de 459 páginas está listado na imagem acima e as cinco primeiras são 1065, 1068, 1069, 1944 e 1945. Agora, vamos executar o comando REORGANIZE no índice usando a instrução T-SQL abaixo e veja na alocação de página novamente.

Alocação de página de postagem

Aqui, a contagem total de páginas é reduzida para 331, que era 459 antes. Além disso, não vemos novas páginas na lista das cinco primeiras, o que implica que os dados são apenas reestruturados – não são recarregados novamente. Até você pode ver novas páginas também, isso acontece na situação em que o grande índice está fortemente fragmentado e a reorganização dos dados usa uma nova página.

Para executar a operação de índice REORGANIZE em todos os índices da tabela ou banco de dados juntos, o usuário pode usar o comando DBCC INDEXDEFRAG ():

Como visto, há uma diferença substancial entre o Índice REBUILD e REORGANIZE. Aqui, os usuários podem escolher uma das alternativas conforme o percentual de fragmentação do índice. Podemos entender que não há padrões documentados; no entanto, o administrador do banco de dados segue a equação padrão conforme o requisito do tamanho e tipo de informação do índice.

Determinação usual do uso da equação:

  • Quando a porcentagem de fragmentação estiver entre 15 e 30: REORGANIZAR

  • Quando a fragmentação for maior que 30: REBUILD

A opção REBUILD é mais útil com a opção ONLINE quando o banco de dados não está disponível para realizar manutenção de índice fora do horário de pico.

Conclusão

Fragmentação de índice é uma fragmentação interna no arquivo de dados. Os principais parâmetros de desempenho rápido do seu banco de dados são a Arquitetura do Banco de Dados, o Design do Banco de Dados e a gravação de Consultas. Um bom design de índice com manutenção sempre aumenta o desempenho da consulta no mecanismo de banco de dados.

Jignesh Raiyani
Últimas mensagens de Jignesh Raiyani (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br