Free Press

O objetivo por trás da criação de índices SQL em tabelas de banco de dados é melhorar o desempenho das consultas que lêem dados da tabela base, acelerando os processos de recuperação de dados. Mas estas tabelas transacionais não são estáticas e mudam muito frequentemente ao longo do tempo. Estas alterações que são realizadas na tabela do banco de dados incluem a adição de novos registros, modificando ou apagando os registros existentes. Como estas alterações devem ser replicadas para os índices das tabelas relacionadas, o índice da tabela ficará fragmentado ao longo do tempo.

SQL Server Index Fragmentation Overview

Existem dois tipos principais de fragmentação do índice SQL; Fragmentação Interna e Externa. A Fragmentação Interna é causada pela inserção de um novo registro ou pela atualização de um registro existente com valores que não cabem no espaço livre atual na página de dados e resulta na divisão da página em duas páginas para caber no novo valor. Neste caso, o SQL Server Engine moverá cerca de 50% dos dados da página atual para a nova página de dados que é gerada a partir da operação de divisão, a fim de manter a árvore de índice SQL equilibrada. A fragmentação interna pode ser causada também pelas operações de exclusão aleatória que resultam em espaço vazio nas páginas de dados. Este tipo de operações deixa as páginas de dados por preencher e resulta em um índice SQL Server maior e de desempenho mais lento devido à necessidade de ler mais páginas de dados para recuperar os dados solicitados.

A Fragmentação Externa é causada quando as páginas de dados do índice do SQL Server estão dispersas no arquivo físico do banco de dados, devido a ter a ordem lógica do índice do SQL Server não corresponder à ordem física na unidade de disco subjacente. A fragmentação externa pode ser causada por operações de exclusão aleatórias que deixam algumas das páginas de extensão vazias, tendo a própria extensão reservada. Este tipo de fragmentação resulta em degradação do desempenho, pois levará mais tempo e consumirá mais recursos ao saltar entre as páginas de dados dispersas, a fim de recuperar os dados solicitados do drive de disco subjacente.

A razão pela qual nos preocupamos muito com a fragmentação do índice SQL é que o índice que você criou para melhorar o desempenho do sistema, pode degradar o desempenho de suas consultas após ter sido altamente fragmentado. Imagine a diferença de tempo e número de leituras em disco entre a leitura de um número específico de linhas de um índice SQL saudável no qual os dados são armazenados em páginas de dados contíguas dentro do arquivo do banco de dados, e a leitura do mesmo número de linhas de um índice SQL Server altamente fragmentado, no qual os dados são espalhados em páginas de dados não contíguas dentro do arquivo do banco de dados.

Se for descoberto que a tabela SQL index está fragmentada, você precisa corrigir o problema de fragmentação executando a Reorganização do Índice SQL Server ou a Reconstrução do Índice, com base na porcentagem de fragmentação do índice SQL. Por exemplo, um índice SQL Server com uma percentagem de fragmentação inferior a 30% pode ser reorganizado, onde o índice SQL com um nível de fragmentação superior a 30% deve ser reconstruído.

• Nota: Verifique Maintaining SQL Server Indexes para mais informações.

Você pode proativamente superar a fragmentação do índice do SQL Server e problemas de divisão de páginas, desenhando o índice corretamente e configurando as opções de criação do Fator de Preenchimento e índice pad_index com os valores apropriados.

• Nota: Verifique Operações de Índices do SQL Server para mais informações.

SQL Server Index Fragmentation Report

Não é considerado uma melhor prática apenas criar um índice SQL na tabela do seu banco de dados para tirar vantagens do aprimoramento da operação de recuperação de dados e deixar esse índice do SQL Server para sempre sem monitorar continuamente a sua fragmentação e mantê-lo.

SQL Server nos fornece vários métodos que podem ser usados para verificar a porcentagem de fragmentação do índice SQL.

SQL Server Index Properties

O primeiro método é a página Fragmentação da janela SQL Server Index Properties. Clique com o botão direito do mouse sobre o índice que você precisa verificar e escolha a opção Propriedades. A partir da página Propriedades de Índice do SQL Server, navegue na página Fragmentação, e você verá a porcentagem de fragmentação do índice e quanto as páginas de índice do SQL Server estão cheias sob a seção Fragmentação, como mostrado abaixo:

Em alguns passos, você pode verificar facilmente a porcentagem de fragmentação do índice do SQL Server selecionado a partir da janela de propriedades do índice. Mas e se você precisar ter uma visão geral da porcentagem de fragmentação para todos os índices da tabela ou todos os índices das tabelas da base de dados? Neste caso, você precisa ir e verificar um a um. Como resultado, uma única semana de trabalho não será suficiente para um relatório de banco de dados!

sys.dm_db_index_physical_stats

Outro método que pode ser usado para verificar a porcentagem de fragmentação para os índices do banco de dados é consultar a função de gerenciamento dinâmico sys.dm_db_index_physical_stats, que foi introduzida pela primeira vez no SQL Server 2005, como um substituto para o comando deprecated DBCC SHOWCONTIG. Ele fornece informações sobre o tamanho e a percentagem de fragmentação para os índices da base de dados.

A fim de obter informações significativas sobre a percentagem de fragmentação de todos os índices sob a base de dados especificada a partir do DMF sys.dm_db_index_physical_stats, você precisa juntar-se a ele com o sys.índices DMV, como no script T-SQL abaixo:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

Com base em suas habilidades de desenvolvimento SQL, você pode realizar a filtragem que você precisa para recuperar a fragmentação do índice SQL no escopo requerido, sem opção de obter um resumo para o banco de dados da instância geral, levando em consideração que os dados mostrados são desde o último reinício do serviço do SQL Server. O resultado no nosso caso será como:

SQL Server Index Physical Statistics Standard Report

SQL Server nos fornece vários relatórios padrão que contêm informações estatísticas úteis sobre o banco de dados selecionado. O relatório Index Physical Statistics, é um dos relatórios padrão do SQL Server que retorna informações estatísticas sobre as partições de índice do SQL Server, porcentagem de fragmentação e o número de páginas em cada partição de índice SQL no nível da base de dados, desde o último reinício do serviço do SQL Server, com recomendação de como corrigir o problema de fragmentação do índice, caso exista, com base na porcentagem de fragmentação do índice do SQL Server.

Para visualizar o relatório de Estatísticas Físicas do Índice do SQL Server, clique com o botão direito do mouse no banco de dados, escolha Relatórios -> Relatórios Padrão e selecione o relatório de Estatísticas Físicas do Índice, como mostrado abaixo:

E o relatório gerado no nível do banco de dados, que não pode ser filtrado e precisa expandir o resultado de cada índice de tabela manualmente, será como mostrado abaixo:

ApexSQL Defrag

ApexSQL Defrag é uma ferramenta de desfragmentação de índices SQL de terceiros que pode ser facilmente utilizada para verificar a fragmentação de índices do SQL Server e informações de uso e executar ou agendar a correção adequada para o problema de fragmentação do índice.

A partir de um local central, você pode analisar e rever as informações de fragmentação e uso em diferentes níveis, incluindo a fragmentação do índice SQL e informações de uso no nível de índice do SQL Server, no nível da tabela, no nível do banco de dados e finalmente no nível da instância geral do SQL Server. Ele nos fornece tipos de variantes de filtros que podem ser usados para exibir e manter apenas os índices de destino.

ApexSQL Defrag também nos permite configurar a configuração do Fator de Preenchimento para os índices da tabela, criar alertas para todos os trabalhos e políticas de desfragmentação e enviar notificação por e-mail para trabalhos bem-sucedidos ou falhos.

A informação exibida fornecerá uma boa visão sobre o tamanho do índice SQL e a porcentagem de fragmentação, que não será afetada por nenhuma reinicialização do serviço do SQL Server ou reinicialização do servidor. Isto porque esta informação será armazenada em um banco de dados de repositório central para a ferramenta ApexSQL Defrag.

Instalando o ApexSQL Defrag

ApexSQL Defrag pode ser facilmente baixado do ApexSQL Download Center e instalado no seu servidor seguindo o assistente de instalação direta, como mostrado abaixo:

Primeiro, você será solicitado a especificar a conta de serviço que será utilizada como um contexto de execução para o agente ApexSQL Defrag e transmite comandos para a instância do SQL Server, como segue:

Após especificar a conta de serviço ApexSQL Defrag, você será solicitado a especificar onde instalar a ferramenta ApexSQL Defrag e se deve criar um ícone de atalho para a ferramenta na área de trabalho, como mostrado abaixo:

Verificando que o caminho fornecido contém um mínimo de 127MB de espaço livre, o ApexSQL Defrag será instalado com sucesso no seu servidor. E a seguinte notificação será exibida, fornecendo a você a capacidade de iniciar o ApexSQL Defrag diretamente:

Se você executar o ApexSQL Defrag pela primeira vez, você será solicitado a confirmar a criação da base de dados do repositório central, onde esses dados históricos e dados de configuração serão armazenados, como a seguir:

Adicionar novo servidor

Após criar o banco de dados do repositório, o ApexSQL Defrag será iniciado sem instância SQL. Para verificar a porcentagem de fragmentação do índice SQL em uma instância SQL Server específica, você precisa adicionar essa instância a essa ferramenta. Para isso, clique no botão Add, na aba Home, como mostrado abaixo:

Na janela Connect to SQL Server, você será solicitado a fornecer o nome da instância do SQL Server que será utilizada e as credenciais necessárias para se conectar a esse servidor, como a seguir:

SQL Server Index Analysis

Quando o servidor é adicionado, você pode executar uma nova verificação para todas as informações de fragmentação de índices SQL na aba Fragmentação abaixo:

ApexSQL Defrag fornece a você a capacidade de analisar todos os índices naquela instância com base em três modos:

• DETALHADO: no qual todos os dados e páginas de índice SQL serão lidos durante o processo de scan
• AMOSTRADO: no qual apenas 1% das páginas serão lidas, levando em consideração que o índice do SQL Server contém mais de 10.000 páginas
• LIMITADO: no qual somente as páginas localizadas no nível pai da árvore b serão lidas

Para analisar a informação de índice do SQL Server da instância SQL Server conectada, clique no botão Analyze, abaixo da aba Fragmentation, e escolha realizar uma análise rápida ou profunda. Vamos realizar uma análise profunda para obter informações completas, como mostrado abaixo:

Continue monitorando a barra de progresso na parte inferior da ferramenta para verificar o progresso da análise, como mostrado abaixo:

E um relatório abrangente, que contém informações completas sobre todos os índices SQL criados sob cada banco de dados desta instância do SQL Server, será exibido como mostrado abaixo:

Com a capacidade de mergulhar entre os diferentes níveis; instância do SQL Server, banco de dados, tabela ou índice, com um único clique sob a mesma janela central, e filtrar o resultado recuperado com base em um nível ou limite específico de fragmentação, como mostrado abaixo:

Além disso, você pode arrastar qualquer coluna das colunas do relatório para agrupar o relatório geral baseado nos valores daquela coluna, como o exemplo abaixo, no qual agrupamos os dados do relatório com base no nome da tabela:

ApexSQL Defrag Reports

ApexSQL Defrag nos fornece uma série de relatórios que mostram informações estatísticas sobre os índices do banco de dados. Estes relatórios podem ser configurados e verificados na aba Relatórios abaixo:

O primeiro relatório é o relatório Total, que mostra uma representação gráfica para fragmentação do índice SQL e uso do espaço em disco no nível selecionado. Por exemplo, se você clicar no nome da instância, o relatório irá desenhar o gráfico de fragmentação percentual e o gráfico de utilização de espaço no nível da instância do SQL Server, como abaixo:

Se você clicar em qualquer banco de dados da janela do Server Explorer, o relatório irá mudar automaticamente a visualização para mostrar o gráfico no nível do banco de dados, como mostrado abaixo:

O segundo relatório, que também pode ser visualizado em diferentes níveis, é o relatório de Estatísticas. Este relatório mostra informações estatísticas sobre o tamanho total dos índices e a fragmentação e a distribuição dos tipos de índices no nível selecionado, como mostrado abaixo:

O terceiro relatório é o relatório Top 10, que visualiza os 10 principais índices agrupados e não agrupados com o maior tamanho e maior porcentagem de fragmentação do índice SQL, no nível especificado. Por exemplo, o relatório seguinte mostra os 10 índices mais caros, com base no tamanho e fragmentação, ao nível da instância do servidor SQL:

Movendo-se para uma base de dados específica, o relatório mostrará automaticamente os 10 índices mais caros, com base no tamanho e fragmentação, ao nível da base de dados selecionada, como mostrado abaixo:

Mergulhando profundamente numa tabela específica sob essa base de dados, o relatório mostrará os 10 índices mais caros, com base no tamanho e na fragmentação, como se segue:

ApexSQL Defrag Report Export

ApexSQL Defrag nos permite exportar os relatórios de fragmentação para diferentes formatos de arquivos, incluindo PDF, IMG, HTML, CS e XML, com a capacidade de personalizar a configuração do relatório antes que ele seja gerado. Na aba Relatórios, clique no botão Opções:

A partir da janela Editar opções de exportação, você pode configurar as diferentes opções, começando pela convenção de nomenclatura do arquivo gerado, o caminho onde o arquivo será salvo, a orientação da página e finalmente o conteúdo de cada relatório, como mostrado abaixo:

Por exemplo, para exportar o relatório Total gerado no nível especificado, clique no botão Exportar, sob a guia Relatórios, e escolha o tipo de arquivo, para o qual este relatório Total será exportado. Em poucos segundos, o relatório será gerado, perguntando se você deseja abrir o relatório gerado. Clique em Sim para abrir esse relatório diretamente, como mostrado abaixo:

Além disso, você pode exportar o relatório Top 10 no nível especificado, clicando no botão Exportar, sob a aba Relatórios, e especificar o tipo de arquivo de exportação, e o relatório será gerado rapidamente, perguntando se você quer ver o relatório gerado diretamente. Clique Yes para abrir o gerado, como mostrado abaixo:

É claro a partir dos exemplos anteriores, que podemos facilmente usar a ferramenta ApexSQL Defrag para rever e analisar a informação percentual de fragmentação do índice SQL e mergulhar no nível diferente a partir do nível de instância do SQL Server até o nível de índice do SQL Server e exibi-lo em diferentes formatos exportáveis, sem que a informação exibida seja afetada por qualquer reinício de servidor ou serviço, pois eles serão armazenados em um banco de dados de repositório.