none
demora ao executar verficar fragmentação banco RRS feed

  • Pergunta

  • Bom dia !

    Gostaria de uma forma rápida de executar uma verificação dos índices do banco de dados utilizando a DVM sys.dm_db_index_physical_stats, utilizei com a opção 'LIMITED' e demorou quase três horas.

    Obrigado

    quarta-feira, 9 de abril de 2014 12:08

Todas as Respostas

  • Thenriquelima,

    Se você não tem muitas tabelas, porém elas são grandes, você pode verificar a fragmentação por tabela e limitando também o quanto o índice está fragmentado. Veja abaixo:

    SELECT * FROM sys.dm_db_index_physical_stats
        (DB_ID('SeuBanco'), OBJECT_ID('SuaTabela'), NULL, NULL , 'LIMITED')
    WHERE avg_fragmentation_in_percent > 15.0
    GO

    ou se você tem muitas tabelas, porém a grande maioria são pequenas, então utilize o script abaixo:

    exec sp_msforeachtable '
    SELECT * FROM sys.dm_db_index_physical_stats
        (DB_ID(''SeuBanco''), OBJECT_ID(''?''), NULL, NULL , ''LIMITED'')
    WHERE avg_fragmentation_in_percent > 15.0;'

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Sugerido como Resposta Durval Ramos terça-feira, 20 de maio de 2014 23:32
    quarta-feira, 9 de abril de 2014 13:14
  • THenrique,

    Quando nos referimos a existem vários detalhes que temos que levar em consideração, dentre eles:

    • Fragmentação;
    • Estatísticas;
    • FillFactor;
    • PadIndex.

    Vou disponibilizar para você alguns scripts que normalmente utilizo para realizar estas verificaçãoes:

    Exemplo - Utilizando - Sys.dm_db_index_physical_stats - Para reconstruir ou reorganizar índices

    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); 
    
    -- 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 Id_Objeto,
        index_id AS Id_Indice,
        partition_number AS PartitionNumber,
        avg_fragmentation_in_percent AS Frag
    INTO #CargaFragmentacaoIndices
    FROM sys.dm_db_index_physical_stats (DB_ID('WELT_23092011'), NULL, NULL , NULL, 'LIMITED')
    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 #CargaFragmentacaoIndices;
    
    -- 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 #CargaFragmentacaoIndices;
    GO


    Exemplo - Lista de Índices não Utilizados

    SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
            i.name
    FROM    sys.indexes AS i
            INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
    WHERE   i.index_id NOT IN ( SELECT  s.index_id
                                FROM    sys.dm_db_index_usage_stats AS s
                                WHERE   s.[object_id] = i.[object_id]
                                        AND i.index_id = s.index_id
                                        AND database_id = DB_ID() )
            AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

    Exemplo - Estatísticas - Leitura e Escrita - Índices

    SELECT  OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
                  i.name AS [IndexName] ,
                  i.index_id ,
                  user_seeks + user_scans + user_lookups AS [Reads] ,
                  user_updates AS [Writes] ,
                  i.type_desc AS [IndexType] ,
                  i.fill_factor AS [FillFactor]
    FROM    sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i 
                                                                             ON s.[object_id] = i.[object_id]
    WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
    ORDER BY OBJECT_NAME(s.[object_id]), Writes DESC, Reads DESC;

    Exemplo - Possíveis - Índices NonClustered - com falha - Leitura e Escrita

    SELECT  OBJECT_NAME(s.[object_id]) AS [Table Name] ,
            i.name AS [Index Name] ,
            i.index_id ,
            user_updates AS [Total Writes] ,
            user_seeks + user_scans + user_lookups AS [Total Reads] ,
            user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
    FROM  sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK )
                                                                                                         ON s.[object_id] = i.[object_id]
                                                                                                         AND i.index_id = s.index_id
    WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND user_updates > ( user_seeks + user_scans + user_lookups )
    AND i.index_id > 1
    ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC ;



    Informações sobre FillFactor e PadIndex para Tabelas e Índices

    select sys.tables.name as tabela, 
               sys.indexes.name as indice, 
               sys.indexes.type_desc as tipo, 
               sys.indexes.fill_factor, 
               sys.indexes.is_padded as padded
            from sys.indexes inner join  sys.tables
                                         on sys.indexes.object_id = sys.tables.object_id
    where sys.indexes.is_disabled =0 
    and sys.indexes.type <> 0
    order by tabela, tipo 


    Exemplo - Pesquisando - Estatísticas de Índices - Desatualizadas

    --Create a table for the outdated statistics
    CREATE TABLE Outdated_statistics
    ([Table name] sysname,
    [Index name] sysname,
    [Last updated] datetime NULL,
    [Rows modified] int NULL)
    GO
    
    --Get the list of outdated statistics
    INSERT INTO Outdated_statistics
    SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) 
    AND rowmodctr>0 
    AND id IN (SELECT object_id FROM sys.tables)
    GO
    
    Select * from Outdated_statistics
    
    --Set the thresholds when to consider the statistics outdated
    DECLARE @hours int
    DECLARE @modified_rows int
    DECLARE @update_statement nvarchar(300);
    
    SET @hours=24
    SET @modified_rows=10
    
    --Update all the outdated statistics
    DECLARE statistics_cursor CURSOR FOR
    SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name
    FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@hours,GETDATE()) 
    AND rowmodctr>=@modified_rows 
    AND id IN (SELECT object_id FROM sys.tables)
     
    OPEN statistics_cursor;
    FETCH NEXT FROM statistics_cursor INTO @update_statement;
     
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
      EXECUTE (@update_statement);
      PRINT @update_statement;
     
     FETCH NEXT FROM statistics_cursor INTO @update_statement;
     END;
     
     PRINT 'The outdated statistics have been updated.';
    CLOSE statistics_cursor;
    DEALLOCATE statistics_cursor;
    GO
    

    Exemplo - Identificando - Índices com Escrita Excessiva

    SELECT  OBJECT_NAME(s.object_id), 
                  i.name, 
                  i.type_desc
    FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK ) Inner JOIN sys.indexes i WITH (NOLOCK) 
                                                                                                          ON s.index_id = i.index_id
    AND s.object_id = i.object_id
    WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
    AND s.index_id > 1
    
    -- Quantidade de Índices com Escrita Excessiva --
    SELECT  COUNT(*)
    FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )
    WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
            AND s.database_id = DB_ID()
            AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
            AND s.index_id > 1
    



    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]

    • Sugerido como Resposta Durval Ramos terça-feira, 20 de maio de 2014 23:32
    sexta-feira, 11 de abril de 2014 15:44
    Moderador