Olá! Neste artigo trazemos algumas verificações e scripts para manutenção do desempenho de índices do banco de dados SQL Server.
É importante ressaltar que a linguagem deste artigo é técnica e o público-alvo deste artigo são os profissionais e equipes de TI de nossos clientes, que tenham domínio em executar as instruções trazidas aqui, discernindo a necessidade da execução de cada etapa levando em conta a saúde do banco, tamanho do banco, tempo de execução e impacto que a manutenção causará nos usuários do sistema Fortes.
🔴 Faça um backup do banco de dados antes de iniciar os processos abaixo.
🔴 Todos os procedimentos mostrados aqui são executados dentro da ferramenta Microsoft SQL Server Management Studio
Índice
Etapa 1: Checagem e manutenção de integridade
Etapa 2: Configuração do Isolation Level
Etapa 3: Verificação dos índices do banco
Etapa 4: Atualização de Estatísticas
Etapa 1: Checagem e manutenção de integridade
Objetivo: Verificar a integridade do banco e caso seja identificado corrupção, realizar uma manutenção. Se não for identificado nenhum problema durante a checagem com o comando DBCC CHECKDB você pode seguir para a próxima etapa da manutenção.
Checa a integridade das estruturas de dados e páginas, detectando possíveis corrupções no banco de dados.
DBCC CHECKDB ('NOMEDOBANCO')
GO
Coloca o banco em modo de emergência, permitindo que o administrador acesse um banco de dados suspeito ou corrompido para tentar um reparo.
ALTER DATABASE NOMEDOBANCO SET EMERGENCY
GO
Muda o banco para modo de usuário único, forçando a desconexão imediata de outros usuários (ROLLBACK IMMEDIATE), o que é necessário para comandos de reparo.
ALTER DATABASE NOMEDOBANCO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Comando de reparo agressivo. Tenta corrigir a corrupção, mas pode resultar em perda de dados (use apenas se necessário e como último recurso).
DBCC CHECKDB ('NOMEDOBANCO' , REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
GO
Comando de reparo menos agressivo. Repara erros na estrutura da página e reconstrói índices não clusterizados sem risco de perda de dados.
DBCC CHECKDB ('NOMEDOBANCO' , REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS
GO
Restaura o banco para o modo multiusuário após o reparo, permitindo que os usuários se reconectem.
ALTER DATABASE NOMEDOBANCO SET MULTI_USER
GO
Reseta o status do banco de dados após uma possível mudança para o modo de emergência ou suspeito.
EXEC sp_resetstatus 'NOMEDOBANCO'
Etapa 2: Configuração do Isolation Level
Objetivo: Deixar parâmetro READ_COMMITTED setado como ON
Atenção
Este processo pode ser demorado (dependendo do tamanho do BD)16.
Pare a utilização do banco antes de executar a alteração17.
Faça um backup antes de alterar18.
Scripts de Configuração
Checar o status: Verifique o valor da coluna is_read_committed_snapshot_on. O valor 1 indica que está ativo (ON), e 0 indica que está inativo (OFF)19.
--Rodar Script para checar o status do READ_COMMITTED:
SELECT name ,is_read_committed_snapshot_on from sys.databases;
Caso o resultado seja 0(zero) - como na imagem abaixo, rodar os 3 scripts abaixo para ligar o parâmetro (EXECUTAR LINHA A LINHA):
Linha de Execução 1: Ativa o Isolation Level READ COMMITTED SNAPSHOT
ALTER DATABASE NOMEDOBANCO SET READ_COMMITTED_SNAPSHOT ON;
Linha de Execução 2:Permite que o Isolation Level SNAPSHOT seja usado. Esta é a base para o READ_COMMITTED_SNAPSHOT funcionar
ALTER DATABASE NOMEDOBANCO SET AllOW_SNAPSHOT_ISOLATION ON;
Linha de Execução 3:Define o Isolation Level padrão da sessão atual como READ COMMITTED. Não altera a configuração global do banco, mas garante que a sessão esteja usando o nível recomendado23.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Etapa 3: Verificação e Manutenção de Índices (Fragmentação)
Objetivo
Conferir a fragmentação dos índices e realizar a reorganização ou recriação (rebuild) conforme a necessidade, o que melhora a eficiência de leitura e gravação24. A Microsoft recomenda um máximo de 30% de fragmentação25.
1. Script de Checagem de Índices
Este script retorna o percentual de fragmentação (avg_fragmentation_in_percent) para todos os índices do banco26262626.
SELECT a.object_id, object_name(a.object_id) AS TableName,
a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID (N'NOMEDOBANCO') -- Nome do banco
, OBJECT_ID(N'') -- Se for buscar em uma tabela específica colocar ela entre as aspas
, NULL
, NULL
, NULL) AS a
INNER JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
Order by avg_fragmentation_in_percent desc;
GO
2. Script Otimizado de Manutenção de Índices
O script a seguir implementa a lógica da Microsoft para manutenção de índices, utilizando um cursor para processar seletivamente os índices com alta fragmentação e um número mínimo de páginas (evitando índices muito pequenos)27272727.
Melhoria: Foi adicionado o WHERE dbtables.[name] NOT LIKE '%\_Temp' à consulta do cursor para excluir tabelas temporárias, melhorando a eficiência.
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
-- Filtros de Otimização:
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name] NOT LIKE '%\_Temp' -- Exclui tabelas temporárias
AND indexstats.avg_fragmentation_in_percent > 05 -- Índices fragmentados
AND indexstats.page_count > 10 -- Índices com mais de 10 páginas (ignora índices muito pequenos)
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;') -- Reorganiza
PRINT 'Concluindo a reorganização do índice ' + @indexName + ' da tabela ' + @tableName
END
ELSE IF (@percentFragment > 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ') -- Recria
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
Etapa 4 (Adicional): Atualização de Estatísticas
Objetivo
Garantir que o otimizador de consultas do SQL Server tenha as informações mais recentes sobre a distribuição dos dados nas tabelas. Estatísticas desatualizadas podem levar a planos de execução ruins e, consequentemente, a um desempenho lento, mesmo com índices otimizados.
Script
SQL
-- Atualiza todas as estatísticas para todas as tabelas do banco de dados.
-- O FULLSCAN garante que o SQL Server examine todos os dados para criar estatísticas precisas,
-- o que é recomendado após a manutenção de índices.
EXEC sp_updatestats
GO