Muitas vezes sentimos uma performance terrível em nosso banco de dados.

Um dos fatores que pode fazer isso acontecer é a defasagem dos índices das tabelas de nosso banco.

Isso acontece devido a  grande quantidade de manipulações dos dados, principalmente com os comandos Insert e Delete.

Para isso podemos criar um script para reorganizar, e dependendo dos casos recriar os índices do nosso SQL Server…
Essas ações melhoram o desempenho do banco.

Vou explicar os passos para montar esse Script que vai te ajudar muito.

Primeiramente, vamos criar algumas variáveis de controle que serão utilizadas no momento da leitura dos índices…

DECLARE @tableName nvarchar(500) 
DECLARE @indexName nvarchar(500) 
DECLARE @percentFragment decimal(11,2)
DECLARE @page_count int

Na sequência vamos definir nosso Cursor, que servirá para armazenar a lista de índices que iremos realizar a manutenção.

Dentro da criação do cursor, escrevemos um comando Select que vai cruzar dados entre a tabela de status dos índices (sys.dm_db_index_physical_stats), a tebela de tabelas(sys.tables), a tabela de Schemas(sys.schemas) e a tabela de Índices(sys.indexes).

Definimos um parâmetro de busca de fragmentação onde o % de fragmentação for maior que 5% e a quantidade de páginas do índice for maior que 10.

Com isso começamos a pegar os índices que já começam a apresentar lentidão nas consultas…

DECLARE FragmentedTableList cursor for 
SELECT  dbtables.[name] AS 'Table',
    dbindexes.[name] AS 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables 
  ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas 
  ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes dbindexes 
  ON dbindexes.[object_id] = indexstats.[object_id]
  AND indexstats.index_id = dbindexes.index_id
  AND dbindexes.[name] IS NOT NULL
WHERE indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent > 05
    AND indexstats.page_count  > 10 
ORDER BY indexstats.page_count DESC, indexstats.avg_fragmentation_in_percent DESC
 
OPEN FragmentedTableList

Por último, vamos abri o cursor, fazer a leitura dos índices e executar as devidas manutenção:

Quando o percentual de fragmentação estiver entre 5% e 30%, vamos realizar o reorganize do índice, ou seja, vamos reorganizar este índice.

Agora, se o percentual for maior que 30% vamos realizar o rebuild do índice, ou seja, realizar a reconstrução do mesmo, pois a defasagem do mesmo já esta bem critica.

FETCH NEXT FROM FragmentedTableList  
INTO @tableName, @indexName, @percentFragment, @page_count
 
WHILE @@FETCH_STATUS = 0 
  BEGIN 
    PRINT 'Processando ' + @indexName + ' na tabela ' + @tableName + ' com ' + CAST(@percentFragment AS NVARCHAR(50)) + '% fragmentado' 
       
    IF(@percentFragment BETWEEN 05 AND 30) 
      BEGIN 
        
        EXEC( 'ALTER INDEX ' +  @indexName + ' ON ' + @tableName + ' REORGANIZE;') 
        PRINT 'Concluindo a reorganização do índice ' + @indexName + ' da tabela ' + @tableName 
      END 
    ELSE IF (@percentFragment > 30)
      BEGIN 
        EXEC( 'ALTER INDEX ' +  @indexName + ' ON ' + @tableName + ' REBUILD; ') 
        PRINT 'Concluindo a recriação do índice ' + @indexName + 'da tabela ' + @tableName 
    END  
    FETCH NEXT FROM FragmentedTableList  
    INTO @tableName, @indexName, @percentFragment,@page_count
  END 
CLOSE FragmentedTableList 
DEALLOCATE FragmentedTableList

Agora vejam o exemplo completo do nosso script:

DECLARE @tableName nvarchar(500) 
DECLARE @indexName nvarchar(500) 
DECLARE @percentFragment decimal(11,2) 
DECLARE @page_count int
 
DECLARE FragmentedTableList cursor for 
SELECT  dbtables.[name] AS 'Table',
    dbindexes.[name] AS 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables 
  ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas 
  ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes dbindexes 
  ON dbindexes.[object_id] = indexstats.[object_id]
  AND indexstats.index_id = dbindexes.index_id
  AND dbindexes.[name] IS NOT NULL
WHERE indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent > 05
    AND indexstats.page_count  > 10 
ORDER BY indexstats.page_count DESC, indexstats.avg_fragmentation_in_percent DESC
 
OPEN FragmentedTableList 
FETCH NEXT FROM FragmentedTableList  
INTO @tableName, @indexName, @percentFragment, @page_count
 
WHILE @@FETCH_STATUS = 0 
  BEGIN 
    PRINT 'Processando ' + @indexName + ' na tabela ' + @tableName + ' com ' + CAST(@percentFragment AS NVARCHAR(50)) + '% fragmentado' 
       
    IF(@percentFragment BETWEEN 05 AND 30) 
      BEGIN 
        
        EXEC( 'ALTER INDEX ' +  @indexName + ' ON ' + @tableName + ' REORGANIZE;') 
        PRINT 'Concluindo a reorganização do índice ' + @indexName + ' da tabela ' + @tableName 
      END 
    ELSE IF (@percentFragment > 30)
      BEGIN 
        EXEC( 'ALTER INDEX ' +  @indexName + ' ON ' + @tableName + ' REBUILD; ') 
        PRINT 'Concluindo a recriação do índice ' + @indexName + 'da tabela ' + @tableName 
    END  
    FETCH NEXT FROM FragmentedTableList  
    INTO @tableName, @indexName, @percentFragment,@page_count
  END 
CLOSE FragmentedTableList 
DEALLOCATE FragmentedTableList

Agora o mesmo pode ser executado periodicamente, ajudando a melhorar a performance do banco de dados.

Espero que gostem!!!

Até a próxima !!!