none
Fragmentação excessiva em Indice - SQL Server 2005

    Question

  • Olá pessoal, boa tarde.

    Tenho uma aplicação cuja tabela principal é bastante acessada simultaneamente, está tabela possui hoje 1.700.000 registros, possui a PK (Clustered) e mais 3 indices (Non-Clustered). O problema é que quando este indice alcança 10% de fragmentação as consultas que utilizam esta tabela simplesmente param de rodar, alocando a tabela e nao retornam para a aplicação. Utilizei um script que reorganiza ou recria o indice quando o % de fragmentação for < 30 (REORGANIZE) e >= 30 (REBUILD). Sempre após a execução do script a query retorna instantaneamente. Eu até pensei em agendar a execução deste script 2x por dia, mas analisando as consultas hoje, tive que passar o script pelo menos 4x.

    Alguem sabe o que eu posso fazer para sanar este problema??? Segue abaixo o script para quem tiver interesse:


    /*Script que analisa as estatisticas do BD e Reorganiza (REORGANIZE) ou Reconstri (REBUILD)

    os objetos utilizando o seguinte critrio:

    < 30% = REORGANIZE

    >= 30% = REBUILD

    */

    -- Ensure a USE <databasename> statement has been executed first.

    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);

    DECLARE @db_id SMALLINT;

    DECLARE @object_id INT;

    SET @db_id = DB_ID(DBName);

    SET @object_id = OBJECT_ID(N'DBName.dbo.TabName');

    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

    -- and convert object and index IDs to names.

    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')

    (@db_id, @object_id, NULL, NULL , 'LIMITED') --Troque o @object_id por NULL caso queira todos os objetos do BD

    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    -- Declare the cursor for the list of partitions to be processed.

    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

    -- Open the cursor.

    OPEN partitions;

    -- Loop through the 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;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

    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 and deallocate the cursor.

    CLOSE partitions;

    DEALLOCATE partitions;

    -- Drop the temporary table.

    DROP TABLE #work_to_do;

    GO

    --------------------------END


    Obrigado a todos!

    Ademir Morgado

     

    Wednesday, February 16, 2011 7:04 PM

All replies

  • Ademir,

    O plano de execução poderia te dar indicações de onde está esse gargalo que gera a demora quando o índice está fragmentado. Não está ocorrendo mudança de plano de execução quando o índice está fragmentado?

    Pode ser que, quando o índice está fragmentado, o SQL Server entede que ir ao índice, fazer I/O nele e depois fazer I/O na tabela seja mais custoso do que simplesmente fazer o I/O direto na tabela.

    Qual é a versão do SQL Server que você está usando? Qual o motivo desta fragmentação?

     


    Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos participantes.

    Alex M. Bastos
    http://bastosalex.spaces.live.com
    Thursday, February 17, 2011 1:08 PM
  • Boa Tarde,

    Quando isso acontece normalmente há algo errado. Os índices podem estar inapropriados, a modelagem está incorreta ou as consultas estão mal escritas. Não conseguirei analisar todos esses detalhes, mas como sugestão inicial eu avaliaria a possibilidade de criar índices com a cláusula INCLUDE de forma a evitar fragmentação excessiva (índices nonclustered sem o include são bem mais suscetíveis a grandes taxas de fragmentação especialmente nos níveis intermediários).

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    Thursday, February 17, 2011 4:08 PM
  • Se suas tabelas tem muitas alterações, sugiro verificar o fill factor dos índices e verificar o numero de pageslipts.

    Outro fator também é a atualização de estatísticas, quando ocorrem muitas modificações na tabelas, de maneira muito rápida, é interessante atualizar de forma manual.

    []'s!

     

     

     


    http://www.diaadiasql.com.br
    Friday, February 18, 2011 4:13 AM
  • Ademir,

    De que forma você esta verificando as informações sobre fragmentação dos seus índices?

    Você destacou que esta utilizando 1 índice clustered e 3 non-clustered, as colunas que possuem estes índices estão sendo utilizadas no seu Select?

    As observações dos colegas são importantes e utilizar a opção Include em conjunto com índices non-clustered, vai forçar o SQL Server e mudar sua estratégia de execução e utilização do índice, incluindo mais condições para evitar possível aumentos de fragmentação.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    Monday, February 21, 2011 7:29 PM