none
Fragmentação de indices RRS feed

  • Pergunta

  • Salve Srs.
    estou com uma dúvida quanto a fragmentação de indices. Possuo algumas tabelas (pequenas, com poucas páginas) que "apresentam" alta fragmentação e que mesmo organizando, fazendo o rebuild ou dropando e criando novamente os mesmo continuam altos. Tentei ajustes no fillfactor mas alguns ainda sim apresentaram elevado indice (tem um pentelho que o melhor q consigo é 50%).

    Li em um doc da Microsoft e até em foruns que isto é normal (pelo menos foi o q eu entendi) para tabelas com menos de 1000 páginas.
    sexta-feira, 19 de setembro de 2008 19:18

Todas as Respostas

  • Alves,

     

    Falar de fragmentação e índices não é fácil!!!

     

    O Mecanismo de Banco de Dados do SQL Server mantém os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). A fragmentação ocorre quando os índices têm páginas nas quais a ordem lógica, com base no valor de chave, não corresponde à ordem física do arquivo de dados. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo.

     

    O Mecanismo de Banco de Dados do SQL Server mantém os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). A fragmentação ocorre quando os índices têm páginas nas quais a ordem lógica, com base no valor de chave, não corresponde à ordem física do arquivo de dados. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo.

     

    Você pode solucionar a fragmentação de índice reorganizando ou recriando um índice. Para índices particionados criados em um esquema de partição, é possível usar qualquer um desses métodos em um índice completo ou em uma única partição de índice.

    Detectando a fragmentação

    A primeira etapa para optar pelo método de fragmentação a ser usado é analisar o índice para determinar o grau de fragmentação. Usando a função de sistema sys.dm_db_index_physical_stats, você pode detectar a fragmentação em um índice específico, em todos os índices de uma tabela ou exibição indexada, em todos os índices de um banco de dados ou em todos os índices de todos os bancos de dados. Para índices particionados, sys.dm_db_index_physical_stats também fornece informações de fragmentação por partição.

    O conjunto de resultados retornado pela função sys.dm_db_index_physical_stats inclui as colunas a seguir.

    Coluna Descrição

    avg_fragmentation_in_percent

    Porcentagem de fragmentação lógica (páginas fora de ordem no índice).

    fragment_count

    Número de fragmentos (páginas folha fisicamente consecutivas) do índice.

    avg_fragment_size_in_pages

    Número médio de páginas em um fragmento de índice.

    Depois que o grau de fragmentação for conhecido, use a tabela a seguir para determinar o melhor método para corrigir a fragmentação.

    Valor avg_fragmentation_in_percent Instrução corretiva

    > 5% e < = 30%

    ALTER INDEX REORGANIZE

    > 30%

    ALTER INDEX REBUILD WITH (ONLINE = ON)*

    * A recriação de um índice pode ser executada online ou offline. A reorganização de um índice sempre é executada online.

     

    Para atingir disponibilidade semelhante à opção de reorganização, recrie índices online. Esses valores fornecem uma diretriz elementar para a determinação do ponto em que se deve alternar entre ALTER INDEX REORGANIZE e ALTER INDEX REBUILD. Contudo, os valores reais podem variar de acordo com o caso. É importante que você experimente para poder determinar o melhor limite para um ambiente.

    Níveis muito baixos de fragmentação (menos de 5 por cento) não deveriam ser resolvidos por nenhum desses comandos, uma vez que o benefício de remover uma quantidade pequena de fragmentação é quase sempre amplamente excedido pelo custo de reorganizar ou recriar o índice.

    sexta-feira, 19 de setembro de 2008 20:28
    Moderador
  • Caro Júnior,
    justamente! venho adotando essa "regra de negocios" (excetuando-se o caso dos 5% que nunca levei em consideração). Fiz uma procedure pra realizar esta tarefa mas alguns indices não obtem "queda" da percertagem de fragmentação. O que pode justificar isto? Ouvi inclusive comentários que a desfragmentação de arquivos no disco tb pode influir (???), mas sinceramente achei bem subjetivo. Isso de certa forma é normal?

    []´s
    sexta-feira, 19 de setembro de 2008 20:49
  • Bom dia,

     

    Encontrei o mesmo problema e estou realizando alguns testes. Assim que obtiver algum sucesso lhe informo.

     

    Abraços,

     

    quarta-feira, 17 de dezembro de 2008 12:01
  • Em geral, a fragmentação em índices pequenos não é freqüentemente controlável. As páginas de índices pequenos não são armazenadas em extensões mistas. As extensões mistas são compartilhadas por até oito objetos, portanto, a fragmentação em um índice pequeno pode não ser reduzida após a reorganização ou recriação do índice. Para obter mais informações sobre eventos mistos, consulte Compreendendo páginas e extensões. Se for uma Heap Table a fragmentação também não irá reduzir ai você terá que criar um indice cluster defragmentar e exclui-lo.

     

     

    Abraços,

    quarta-feira, 17 de dezembro de 2008 12:21
  • Realmente é isso, se a tabela for pequena(aproximadamente 100000 registros) ou Heap Table a fragmentação do indice não será reduzida.

     

    Abraços,

     

    quarta-feira, 17 de dezembro de 2008 14:02
  • Alexandre,

     

    É difícil afirmar, mas basicamente isso poderá ocorrer.

    quarta-feira, 17 de dezembro de 2008 15:33
    Moderador
  • Só lembrando que algo que pode influenciar negativamente sobre a fragmentação de tabelas é a escolhas de datatypes incorretos ou mau dimencionados.

    quarta-feira, 17 de dezembro de 2008 16:52
  • Alves, vc conseguiu alguma coisa?

    quinta-feira, 18 de dezembro de 2008 18:20
  • Bom dia pessoal,

     

    Me desculpem pela intromissao nesta thread, mas eu gostaria de saber qual seria o camando/procedimento especifico no sql server 2005 para que eu possa fazer esta fragmentação de indices, pois os que estão acima não deram certo, ou eu fiz alguma coisa errada.

     

    sexta-feira, 16 de janeiro de 2009 13:50
  • Olá pessoal,

    eu costumo utilizar o código abaixo para fazer as verificações:

    use [database]  
    SET NOCOUNT ON;  
    DECLARE @objectid int;  
    DECLARE @indexid int;  
    DECLARE @partitioncount bigint;  
    DECLARE @schemaname nvarchar(130);   
    DECLARE @objectname nvarchar(130);   
    DECLARE @indexname nvarchar(130);   
    DECLARE @partitionnum bigint;  
    DECLARE @partitions bigint;  
    DECLARE @frag float;  
    DECLARE @command nvarchar(4000);   
     
       
    SELECT 
        object_id AS objectid,  
        index_id AS indexid,  
        partition_number AS partitionnum,  
        avg_fragmentation_in_percent AS frag  
    INTO #work_to_do  
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULLNULL , NULL'LIMITED')  
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  
     
     
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
     
      
    OPEN partitions;  
     
     
    WHILE (1=1)  
        BEGIN;  
            FETCH NEXT 
               FROM partitions  
               INTO @objectid, @indexid, @partitionnum, @frag;  
            IF @@FETCH_STATUS < 0 BREAK;  
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
            FROM sys.objects AS o  
            JOIN sys.schemas as s ON s.schema_id = o.schema_id  
            WHERE o.object_id = @objectid;  
            SELECT @indexname = QUOTENAME(name)  
            FROM sys.indexes  
            WHERE  object_id = @objectid AND index_id = @indexid;  
            SELECT @partitioncount = count (*)  
            FROM sys.partitions  
            WHERE object_id = @objectid AND index_id = @indexid;  
     
            IF @frag < 30.0  
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
            IF @frag >= 30.0  
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
            IF @partitioncount > 1  
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
            EXEC (@command);  
            PRINT N'Executed: ' + @command;  
        END;  
     
    CLOSE partitions;  
    DEALLOCATE partitions;  
     
    DROP TABLE #work_to_do;  
    GO  
     

    ___________
    ricardo leka

    sexta-feira, 6 de fevereiro de 2009 13:16
  • Ricardo Leka disse:

    Olá pessoal,

    eu costumo utilizar o código abaixo para fazer as verificações:

    use [database]  
    SET NOCOUNT ON;  
    DECLARE @objectid int;  
    DECLARE @indexid int;  
    DECLARE @partitioncount bigint;  
    DECLARE @schemaname nvarchar(130);   
    DECLARE @objectname nvarchar(130);   
    DECLARE @indexname nvarchar(130);   
    DECLARE @partitionnum bigint;  
    DECLARE @partitions bigint;  
    DECLARE @frag float;  
    DECLARE @command nvarchar(4000);   
     
       
    SELECT 
        object_id AS objectid,  
        index_id AS indexid,  
        partition_number AS partitionnum,  
        avg_fragmentation_in_percent AS frag  
    INTO #work_to_do  
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULLNULL , NULL'LIMITED')  
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  
     
     
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
     
      
    OPEN partitions;  
     
     
    WHILE (1=1)  
        BEGIN;  
            FETCH NEXT 
               FROM partitions  
               INTO @objectid, @indexid, @partitionnum, @frag;  
            IF @@FETCH_STATUS < 0 BREAK;  
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
            FROM sys.objects AS o  
            JOIN sys.schemas as s ON s.schema_id = o.schema_id  
            WHERE o.object_id = @objectid;  
            SELECT @indexname = QUOTENAME(name)  
            FROM sys.indexes  
            WHERE  object_id = @objectid AND index_id = @indexid;  
            SELECT @partitioncount = count (*)  
            FROM sys.partitions  
            WHERE object_id = @objectid AND index_id = @indexid;  
     
            IF @frag < 30.0  
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
            IF @frag >= 30.0  
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
            IF @partitioncount > 1  
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
            EXEC (@command);  
            PRINT N'Executed: ' + @command;  
        END;  
     
    CLOSE partitions;  
    DEALLOCATE partitions;  
     
    DROP TABLE #work_to_do;  
    GO  
     

    ___________
    ricardo leka



    olá Ricardo,

    sua query tá dando erro:

    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near '('.

    Grato

    Bruno H.
    quinta-feira, 12 de fevereiro de 2009 18:27