none
Rebuild/Reorder Index RRS feed

  • Pergunta

  • Pessoal, 

    Sou novo nesta área de Banco e estou com uma dúvida onde não sei mais o que fazer.

    Me pediram para executar uma rotina de Rebuild/Reorder no nosso banco de dados, eu executei o script conforme orientado, porém as fragmentações dos nosso index ainda continua alta. 

    Por Exemplo:

    Index A (Antes da Execução da Rotina): 94,425...

    Index A (Após a Execução da Rotina): 71,4285...

    Ou Seja, mesmo executando a rotina de Rebuild/Reorder, vários index continuam com uma alta fragmentação.

    Alguém sabe me dizer alguma coisa sobre esse assunto?

    quarta-feira, 17 de maio de 2017 20:26

Respostas

Todas as Respostas

  • Anderson,

    Poderia nos apresentar a maneira que você realizou o Rebuild/Reindex?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 19 de maio de 2017 00:21
    Moderador
  • Boa noite,

    Anderson, qual o tamanho dos seus índices ?

    Talvez tenho alguns posts que possa te ajudar.

    https://blogdojamal.wordpress.com/2017/01/08/rebuild-nao-zera-a-fragmentacao/

    Para visualizar facilmente os seus índices:

    https://blogdojamal.wordpress.com/2017/02/03/sp_showindex-no-sql-azure-e-outras-novidades/

    Att

    sexta-feira, 19 de maio de 2017 02:44
  • Pessoal,

    Eu executo o script abaixo para realizar o rebuild/reorder dos index!

    /**********************************************************************/

    Script

    /**********************************************************************/

    DBCC UPDATEUSAGE ('Banco')
    GO

    /**************************************************************************************************/
    /* Usando sys.dm_db_index_physical_stats em um script para reconstruir ou reorganizar índices  */
    /**************************************************************************************************/

    -- 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);   
    -- 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('Banco'), NULL, NULL , NULL, 'DETAILED')  
    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.  
    PRINT N'FIM';

    -- Drop a tabela temporária

    IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
    GO  


    EXEC sp_updatestats
    GO

                
    sexta-feira, 19 de maio de 2017 16:00
  • Anderson,

    Faça o seguinte, utilize ao final deste script o comando Update Statistics com a opção FullScan, a Stored Procedure atualiza as estatísticas das tabelas de sistema, mas não realiza a atualização em todos os níveis, provavelmente esse pode ser um indicador que poderá estar apresentando fragmentação alta.

    Veja se este exemplo te ajuda:

    /*
      Sr.Nimbus - OnDemand
      http://www.srnimbus.com.br
    */
    
    USE NorthWind
    GO
    
    /* 
      Manutenção das Estatísticas
    */
    
    -- Problema com estatísticas desatualizadas
    -- AUTO_UPDATE_STATISTICS
    
    ALTER DATABASE NorthWind SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
    GO
    
    UPDATE TOP (50) PERCENT Order_DetailsBig SET Quantity = CHECKSUM(NEWID()) / 10000
    GO
    
    -- Estimativa incorreta, pois as estatisticas estão desatualizadas
    SELECT * FROM Order_DetailsBig
    WHERE Quantity = 100
    OPTION (RECOMPILE)
    GO
    
    ALTER DATABASE NorthWind SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
    GO
    
    -- Estimativa correta, pois o AUTO_UPDATE_STATISTICS é disparado
    -- automaticamente
    SELECT * FROM Order_DetailsBig
    WHERE Quantity = 100
    OPTION (RECOMPILE)
    
    /*
      Quando um auto update statistics é disparado?
      AUTO_UPDATE_STATISTICS
      RowModCtr
      
      - Se a cardinalidade da tabela é menor que seis e a tabela esta no 
      banco de dados tempdb, auto atualiza a cada seis modificações na tabela
    
      - Se a cardinalidade da tabela é maior que seis e menor ou igual a 500,
      então atualiza as estatísticas a cada 500 modificações na tabela
      
      - Se a cardinalidade da tabela é maior que 500,
      atualiza as estatísticas quando 500 + 20% da tabela for alterada.
      
      No Profiler visualizar os evento SP:StmtCompleted e SP:StmtStarting
    */
    
    
    
    -- Exemplo sp_updatestats
    -- Runs UPDATE STATISTICS against all user-defined and internal tables in 
    -- the current database
    EXEC sp_updatestats
    GO
    
    
    -- Linhas modificadas por coluna, antiga rowmodctr
    IF OBJECT_ID('Tab1') IS NOT NULL
      DROP TABLE Tab1
    GO
    CREATE TABLE Tab1 (ID Int IDENTITY(1,1) PRIMARY KEY, Col1 Int, Col2 Int, Col3 Int, Col4 Int, Col5 Int)
    GO
    CREATE STATISTICS StatsCol1 ON Tab1(Col1)
    CREATE STATISTICS StatsCol2 ON Tab1(Col2)
    CREATE STATISTICS StatsCol3 ON Tab1(Col3)
    CREATE STATISTICS StatsCol4 ON Tab1(Col4)
    CREATE STATISTICS StatsCol5 ON Tab1(Col5)
    GO
    INSERT INTO Tab1(Col1, Col2, Col3, Col4, Col5) VALUES(1, 1, 1, 1, 1)
    GO 100
    CHECKPOINT
    GO
    SELECT * FROM Tab1
    GO
    
    -- Quantidade de modificações na tabela
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('Tab1')
    GO
    
    -- Zerando rowmodctr
    UPDATE STATISTICS Tab1 WITH FULLSCAN
    GO
    
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('Tab1')
    GO
    
    -- Atualizando 5 linhas
    UPDATE TOP (5) Tab1 SET Col1 = 2
    GO
    
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('Tab1')
    GO
    
    -- E as estatísticas com 2 colunas?
    CREATE STATISTICS StatsCol1_Col2 ON Tab1(Col1, Col2)
    GO
    
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('Tab1')
    GO
    
    UPDATE TOP (5) Tab1 SET Col2 = 2
    GO
    -- Update na "segunda" coluna não atualiza rowmodctr
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('Tab1')
    GO
    
    -- Apagando 5 linhas
    DELETE TOP (5) FROM Tab1
    GO
    
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('Tab1')
    GO
    
    -- Zerar rowmodctr para seguir os testes
    UPDATE STATISTICS Tab1 WITH FULLSCAN
    GO
    
    /*
    "From BOL"
    In SQL Server 2000 and earlier, the Database Engine maintained row-level 
    modification counters. 
    Such counters are now maintained at the column level. 
    Therefore, the rowmodctr column is calculated and produces results that 
    are similar to the results in earlier versions, but are not exact. 
    */
    
    -- Consultando modificações por coluna
    -- DMV sys.system_internals_partition_columns mostra modificações por coluna, 
    -- independente de terem índice ou estatística
    CHECKPOINT
    SELECT partitions.object_id,
           partitions.index_id,
           columns.name,
           system_internals_partition_columns.partition_column_id,
           system_internals_partition_columns.modified_count
      FROM sys.system_internals_partition_columns
     INNER JOIN sys.partitions
        ON system_internals_partition_columns.partition_id = partitions.partition_id
     INNER JOIN sys.columns
        ON partitions.object_id = columns.object_id
       AND system_internals_partition_columns.partition_column_id = columns.column_id 
     WHERE partitions.object_id = OBJECT_ID('Tab1')
    GO
    -- system_internals_partition_columns só é atualizada depois do checkpoint
    
    
    -- Atualizando algumas colunas
    UPDATE TOP (10) Tab1 SET Col4 = 5, Col5 = 5
    GO
    
    CHECKPOINT
    SELECT OBJECT_NAME(partitions.object_id) AS objName,
           partitions.object_id,
           partitions.index_id,
           columns.name,
           system_internals_partition_columns.partition_column_id,
           system_internals_partition_columns.modified_count
      FROM sys.system_internals_partition_columns
     INNER JOIN sys.partitions
        ON system_internals_partition_columns.partition_id = partitions.partition_id
     INNER JOIN sys.columns
        ON partitions.object_id = columns.object_id
       AND system_internals_partition_columns.partition_column_id = columns.column_id 
     WHERE partitions.object_id = OBJECT_ID('Tab1')
    GO
    
    -- Apagando 10 linhas
    DELETE TOP (10) FROM Tab1
    GO
    
    CHECKPOINT
    SELECT OBJECT_NAME(partitions.object_id) AS objName,
           partitions.object_id,
           partitions.index_id,
           columns.name,
           system_internals_partition_columns.partition_column_id,
           system_internals_partition_columns.modified_count
      FROM sys.system_internals_partition_columns
     INNER JOIN sys.partitions
        ON system_internals_partition_columns.partition_id = partitions.partition_id
     INNER JOIN sys.columns
        ON partitions.object_id = columns.object_id
       AND system_internals_partition_columns.partition_column_id = columns.column_id 
     WHERE partitions.object_id = OBJECT_ID('Tab1')
    GO
    
    -- AUTO_UPDATE_STATISTICS
    -- Quanto tempo demora para disparar um auto_update em uma tabela grande?
    
    ALTER DATABASE NorthWind SET AUTO_UPDATE_STATISTICS ON
    GO
    
    -- Criando índice para testes
    CREATE INDEX ixOrderDate ON OrdersBig(OrderDate)
    GO
    -- Consultando quantidade de modificações no índice desde sua criação/update
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('OrdersBig')
    GO
    
    -- Gerando update de várias linhas para disparar auto_update_statistics
    -- 15 segundos
    UPDATE TOP (50) PERCENT OrdersBig SET OrderDate = CONVERT(Date, GETDATE() - (CheckSUM(NEWID()) / 100000))
    GO
    
    -- Consultando quantidade de modificações no índice desde sua criação/update
    SELECT name, id, rowmodctr
      FROM sysindexes
     WHERE id = OBJECT_ID('OrdersBig')
    GO
    
    -- Disparando auto update statsitics
    SELECT *
      FROM OrdersBig
     WHERE OrderDate = '20121221' -- Maias estavam errados!
    GO
    
    
    -- Mas quanto tempo demorou?
    
    -- Profiler SP:StmtCompleted e SP:StmtStarting
    -- Ou TraceFlags 3604/3605 e 8721
    DBCC TRACEON(3605)
    /*
      Error Log:
      Message
      AUTOSTATS: Tbl: OrdersBig Objid:1637580872 Rows: 1000070.000000 Threshold: 200514 Duration: 1055ms
      Message
      AUTOSTATS: UPDATED Stats: OrdersBig..ixOrderDate Dbid = 5 Indid = 3 Rows: 315415 Duration: 1061ms
    */
    
    DBCC TRACEON(3604) -- Resultado para Console (painel de results do SSMS)
    DBCC TRACEON(8721)
    
    
    -- AUTO_UPDATE_STATISTICS_ASYNC
    -- E se eu não quiser esperar pelo update?
    
    ALTER DATABASE Northwind SET AUTO_UPDATE_STATISTICS_ASYNC ON
    GO
    
    -- Gerando update de várias linhas para disparar auto_update_statistics
    -- 15 segundos
    UPDATE TOP (50) PERCENT OrdersBig SET OrderDate = CONVERT(Date, GETDATE() - (CheckSUM(NEWID()) / 100000))
    GO
    
    -- Disparando auto update statsitics
    SELECT *
      FROM OrdersBig
     WHERE OrderDate = '20121221' -- Maias estavam errados!
    GOMessage
    AUTOSTATS: CREATED Dbid = 5 Tbl: TestAutoUpdateStatistics(Value) Rows: 315481  Dur: 1591ms
    
    -- Consultando tempo gasto... 
    SELECT * FROM sys.dm_exec_background_job_queue_stats
    GO
    
    ALTER DATABASE Northwind SET AUTO_UPDATE_STATISTICS_ASYNC OFF
    GO
    
    -- Ou desabilita o auto update/create statistics para a tabela 
    EXEC sp_autostats 'OrdersBig', 'OFF' 
    GO
    
    
    -- AUTO_CREATE_STATISTICS
    -- Quanto tempo demora para disparar um auto_create em uma tabela grande?
    -- Criando tabela com 1 milhão de linhas
    -- DROP TABLE TestAutoUpdateStatistics
    SELECT TOP 1000000 *
      INTO TestAutoUpdateStatistics
      FROM OrdersBig
    GO
    
    -- Gera auto create statistics pois estatística ainda não existe
    SELECT *
      FROM TestAutoUpdateStatistics
     WHERE Value BETWEEN 1000 AND 1100
    GO
    -- Message
    -- AUTOSTATS: CREATED Dbid = 5 Tbl: TestAutoUpdateStatistics(Value) Rows: 315481  Dur: 1591ms
    
    -- TFs para verificar tempo gasto
    DBCC TRACEON(3605) -- Resultado para Console (painel de results do SSMS)
    DBCC TRACEON(8721)
    
    
    -- EDGE Cases
    -- E colunas blob ?
    
    IF OBJECT_ID('TestBlobTab') IS NOT NULL
      DROP TABLE TestBlobTab
    GO
    CREATE TABLE TestBlobTab (ID Int IDENTITY(1,1) PRIMARY KEY, Col1 Int, Foto VarBinary(MAX))
    GO
    
    -- 51 mins e 39 segundos para rodar
    INSERT INTO TestBlobTab (Col1, Foto)
    SELECT TOP 30000
           CheckSUM(NEWID()) / 1000000, 
           CONVERT(VarBinary(MAX),REPLICATE(CONVERT(VarBinary(MAX), CONVERT(VarChar(250), NEWID())), 5000))
    FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d 
    GO
    
    INSERT INTO TestBlobTab (Col1, Foto)
    SELECT CheckSUM(NEWID()) / 1000000, 
           NULL
    GO 100
    
    
    -- Consulta quantidade de páginas LOB
    SELECT t.name, au.*
      FROM sys.system_internals_allocation_units au
     INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
     INNER JOIN sys.tables t
        ON p.object_id = t.object_id
     WHERE t.name = 'TestBlobTab'
    GO
    
    
    -- Demora uma eternidade para criar a estatística na coluna Foto...
    -- 20 mins e 17 segundos para criar a estatística
    -- Consulta roda em 0 segundos
    SELECT COUNT(*)
      FROM TestBlobTab
     WHERE Foto IS NULL
    
    -- Message
    -- AUTOSTATS: CREATED Dbid = 5 Tbl: Tab1(Foto) Rows: 70100  Dur: 1217604ms
    
    -- Mais informações aqui...
    http://blogs.msdn.com/b/psssql/archive/2009/01/22/how-it-works-statistics-sampling-for-blob-data.aspx
    
    
    
    -- NO_RECOMPUTE
    
    -- Identificando as estatísticas criadas automaticamente
    SELECT * 
      FROM sys.stats
     WHERE Object_ID = OBJECT_ID('TestBlobTab')
    GO
    
    DROP STATISTICS TestBlobTab._WA_Sys_00000003_03F0984C
    GO
    
    -- Criando manualmente com clausula NORECOMPUTE
    -- DROP STATISTICS TestBlobTab.StatsFoto
    CREATE STATISTICS StatsFoto ON TestBlobTab(Foto) WITH NORECOMPUTE, SAMPLE 0 PERCENT
    GO
    
    SELECT COUNT(*)
      FROM TestBlobTab
     WHERE Foto IS NULL
    OPTION (RECOMPILE)


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 24 de maio de 2017 16:34
    Moderador
  • Seguem os tamanhos dos Index (TOP 10)

    Referencia

    Database

    Tabela

    Indice

    Fragmentacao (%)

    Qtd Páginas

    Fill Factor (%)

    Compressão de Dados

    2017-05-23 23:00:16

    BASE

    SRF010

    SRF010_PK

    99.92

    2592

    0

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    SR9010

    SR9010_PK

    99.85

    1297

    0

    Sem Compressão

    2017-05-23 23:00:00

    BASE

    SX5010

    SX5010_PK

    99.77

    2191

    0

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    SD1010

    SD10109

    99.56

    2699

    100

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    SD3010

    IX_SD3010_02

    99.49

    6897

    100

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    FK7010

    FK7010_UNQ

    99.38

    6098

    100

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    SD1010

    SD10102

    99.38

    4198

    100

    Sem Compressão

    2017-05-23 23:00:00

    BASE

    FK1010

    FK10101

    99.36

    1725

    100

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    SD1010

    SD1010C

    99.33

    3306

    100

    Sem Compressão

    2017-05-23 23:00:16

    BASE

    FK7010

    FK70101

    99.28

    5969

    100

    Sem Compressão

    quarta-feira, 24 de maio de 2017 17:12
  • Deleted
    quinta-feira, 25 de maio de 2017 17:00