Nova Página
[SPIKE] melhores rotinas de manutenção para o sei (avaliar fragmentação e periodicidade)
O objetivo do estudo é criar rotinas de manutenção dos índices e estatísticas com periodicidade de suas ativações no banco de dados do SEI, buscando manter boa performance nas consultas executadas nessa instância do SQL Server, bem como monitorar a estrutura de índices antes da execução da tarefa de desfragmentação e consequentemente após o término da desta tarefa, assim como manter um histórico para posterior consulta sobre quais índices fragmentam mais rápido.
Um breve resumo das tecnologias utilizadas para atingir o objetivo proposto:
-
Traces → Ferramenta gratuíta de monitoramento de bancos de dados SQL Server, que é composta de um banco de dados chamado traces, tabelas de histórico da instância, bem como procedures que implementam as funcionalidades que são acionadas por um conjunto de jobs. Boa parte das funcionalidades usadas na ferramenta é baseada na procedure sp_whoisactive (https://www.brentozar.com/) muito conhecida pela nos fóruns sobre SQL Server. Funcionalidade da ferramenta usada no estudo:
stpLoad_Index_Fragmentation → É um procedimento armazenado da ferramenta Traces responsável em armazenar em tabela um “retrato” de como está toda a estrutura de fragmentação dos índices do banco de dados num determinado momento. Configuração de execução:
--Configuração
USE Traces
EXECUTE dbo.stpLoad_Index_Fragmentation -
SQL Server Maintenance Solution → Ferramenta gratuíta desenvolvida pela uma comunidade de DBAs chamada Hallengren(https://ola.hallengren.com/), muito referenciado nos fóruns, que é especializada em administração de bancos de dados SQL Server. Funcionalidades usadas durante o estudo:
IndexOptimize → É o procedimento armazenado do SQL Server Maintenance Solution para reconstruir e reorganizar índices e atualizar estatísticas.
A configuração 1, faz analise individualizada dos índices do banco SEI segundo critérios padronizados microsoft, podendo assim efetuar rebuild ou reorganize ou nada fazer no índice de acordo com cada caso:
--Configuração 1
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SEI',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
Na configuração 2, a procedure IndexOptimize faz analise individualizada dos índices do bancos de Sistema segundo critérios padronizados microsoft, podendo assim efetuar rebuild ou reorganize ou nada fazer no índice de acordo com cada caso:
--Configuração 2
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SYSTEM_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
Na configuração 3, a procedure IndexOptimize faz a atualização completa de todas as estatísticas do banco SEI:
--Configuração 3
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SEI',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'
Na configuração 4, a procedure IndexOptimize faz somente a atualização das estatísticas no banco SEI, onde alguma modificação de registros de dados:
--Configuração 4
USE master
EXECUTE dbo.IndexOptimize
@Databases = 'SEI',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
Caso de uso prático dos recursos abordados aplicados ao banco de dados do SEI de Produção (172.16.0.87) para efeitos de demonstração
Tirando uma “Retrato” da estrutura de fragmentação dos índices:
|
|
Executando a configuração 1, para efetuar rebuild ou reorganize ou nada fazer no índice de acordo com cada caso:
|
|
Executando a configuração 2, para efetuar rebuild ou reorganize ou nada fazer no índice nas bases de sistema de acordo com cada caso:
|
Executando a configuração 3, para atualização completa de todas as estatísticas do banco SEI:
|
Executando a configuração 4, para atualização somente das estatísticas modificadas do banco SEI:
|
Implementação da política de manutenção do histórico fragmentação de índices, manutenção de estatísticas e manutenção de índices no banco de dados do SEI de Produção (172.16.0.87)
JOBs implementados baseados nas técnicas abordadas:
-
MANUTENCAO - SEI Rebuild ou Reorganize de indexes fragmentados e Atualização completa de Statistics do SEI → Agendado para ser executado toda sexta feita as 15 horas. Composto de 3 passos, cito: 1 - Tira “Retrato” da estrutura dos índices, 2 - Rebuild or reorganize e 3 – Update full de estatisticas;
-
MANUTENCAO - SEI Update Somente Statistics Modificadas → Agendado para ser executado todo dia as 21 horas. Composto apenas de 1 passo, onde faz a atualização das estatísticas onde houve modificação de registros;
-
MANUTENCAO - SYSTEM Rebuild ou Reorganize e Update full Statistics → Agendado para ser executado toda quinta feita às 16 horas. Composto de 2 passos, cito: 1 - Rebuild or reorganize e 2 – Update full de estatisticas;
-
DBA - Load Index Fragmentation → Agendado para ser executado de segunda a sexta as 02 horas da madrugada. Tira um “retrato” de toda a estrutura de fragmentação dos índices, armazena na tabela: Traces.dbo.Index_Fragmentation_History para posterior acompanhamento da percentagem de fragmentação de cada índice.
Conclusão
As ferramentas abordadas no estudo e implementação são muito poderosas e completas quando se fala de monitoramento e manutenção de instâncias de bancos de dados SQL Server. Os recursos usados durante o estudo foi uma pequena parte do que está contido nestas. Existem uma variedade de configurações e recursos que podem ser abordados de acordo com cada cenário existente. Assim sendo, ainda existe muita coisa a ser feita valendo dos recursos que a ferramenta oferece.
Referencias: