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!!!