Script para reconstruir índices no SQL Server

Script para reconstruir índices no SQL Server | SQL Server Blog – André Guerreiro Neto.

IF OBJECT_ID('dbo.PD_ReconstruirIndicesFragmentados', 'P') IS NOT NULL
  DROP PROCEDURE dbo.PD_ReconstruirIndicesFragmentados;
GO

CREATE PROCEDURE dbo.PD_ReconstruirIndicesFragmentados
(
  @Tabela SYSNAME = NULL,
  @FragmentacaoMinima TINYINT
)
AS
BEGIN
  DECLARE @Comando VARCHAR(800),
          @NomeIndice SYSNAME,
          @NomeTabela SYSNAME,
          @TotalIndices INT,
          @LinhasAfetadas INT;

  SET NOCOUNT ON;

  -- A tabela informada existe?
  IF @Tabela IS NOT NULL
  BEGIN
    IF (SELECT COUNT(*) FROM sys.tables WHERE name = @Tabela) = 0
    BEGIN
	  RAISERROR('A tabela ''%s'' não existe no banco de dados.',
	            16, 1, @Tabela);
	  RETURN(-1);
    END;
  END;

  -- O percentual de fragmentação deve estar entre 1 e 100
  IF NOT @FragmentacaoMinima BETWEEN 1 AND 100
  BEGIN
    RAISERROR('O percentual de fragmentação deve ser entre 1 e 100.',
              16, 1);
    RETURN(-1);
  END;

  SELECT OBJECT_NAME(STA.object_id) AS objeto_nome,
         OBJECT_SCHEMA_NAME(STA.object_id) AS schema_nome,
         IDX.name AS indice_nome,
         IDX.type AS indice_tipo,
         STA.avg_fragmentation_in_percent AS percentual_fragmentado,
         STA.page_count,
         IDX.is_unique,
         IDX.is_primary_key
    INTO dbo.#InfoIndices
    FROM sys.dm_db_index_physical_stats(DB_ID(),
                                        NULL,
                                        NULL,
                                        NULL,
                                        NULL) AS STA
   INNER JOIN sys.indexes AS IDX
      ON IDX.index_id = STA.index_id AND IDX.object_id = STA.object_id
   WHERE STA.avg_fragmentation_in_percent >= @FragmentacaoMinima
     AND STA.index_type_desc <> 'HEAP'
     AND STA.page_count > 80
     AND (@Tabela IS NULL OR OBJECT_NAME(STA.object_id) = @Tabela);

  SELECT indice_nome,
         indice_tipo,
         objeto_nome,
         'ALTER INDEX ' +
         CASE
           WHEN indice_nome IS NULL THEN
             'ALL'
           ELSE
             QUOTENAME(indice_nome)
         END + ' ON ' + QUOTENAME(schema_nome) + '.' +
         QUOTENAME(objeto_nome) +
         ' REBUILD WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);' AS Comando
   INTO dbo.#AlterIndex
   FROM dbo.#InfoIndices;

  SELECT @LinhasAfetadas = @@ROWCOUNT,
         @TotalIndices = 0;	

  WHILE @LinhasAfetadas > 0
  BEGIN
    SELECT TOP(1)
           @Comando = Comando,
           @NomeIndice = indice_nome,
           @NomeTabela = objeto_nome,
           @TotalIndices += 1
      FROM dbo.#AlterIndex
     ORDER BY indice_tipo, indice_nome;

    EXECUTE(@Comando);

    RAISERROR('O índice ''%s'' do objeto ''%s'' foi desfragmentado com sucesso.',
              0, 1, @NomeIndice, @NomeTabela) WITH NOWAIT; 

    DELETE Tabela
      FROM (SELECT TOP(1) *
              FROM dbo.#AlterIndex
             ORDER BY indice_tipo, indice_nome) AS Tabela;

    SET @LinhasAfetadas = @@ROWCOUNT;
  END;

  RAISERROR('Total de índices desfragmentados: %d', 0, 1, @TotalIndices);
END;
GO

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em ORACLE 11gR2, PLSQL SCRIPTS, PLSQL TUNING e marcado , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s