Entrar para Votar

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.

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

0

Entrar para Votar

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

0

Entrar para Votar

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

0

Entrar para Votar

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

0

Entrar para Votar

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

0

Entrar para Votar

Alexandre,

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

quarta-feira, 17 de dezembro de 2008 15:33

Moderador

0

Entrar para Votar

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

0

Entrar para Votar

Alves, vc conseguiu alguma coisa?

quinta-feira, 18 de dezembro de 2008 18:20

0

Entrar para Votar

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

0

Entrar para Votar

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(), NULL, NULL , 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

0

Entrar para Votar

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(), NULL, NULL , 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

Produtos

Resources

Solutions

Atualizações

Avaliações

Sites relacionados

Treinamento

Certificações

Outros recursos

Por produtos

Outros links

Não é um IT Pro?

Inquiridor

Fragmentação de indices

Pergunta

Todas as Respostas

Detectando a fragmentação