Em artigo anterior, mostrei como fazer manutenção de índices, hoje vamos avançar mais no assunto, aprendendo como fazer manutenção de índices por prioridade, ou seja, as tabelas serão reconstruídas de acordo com a ordem de importância que você definir.
Esse vídeo foi motivado por uma ótima notícia que recebi de uma de minhas alunas, a Regiane. Apesar de ter entrado no curso Transact-SQL do Zero ao Master a pouco tempo, ela já conseguiu uma vaga como DBA, antes mesmo de terminar o treinamento!
E mais, ela hoje é responsável por um banco com mais de 7 mil tabelas!!
Naturalmente com um banco desse tamanho, certas tarefas de manutenção, como a reconstrução e reorganização de índices, precisam ser feita em múltiplas etapas, e é exatamente isso que faremos hoje.
Porque priorizar tabelas na hora do REBUILD
Se você administra bancos de dados pequenos, talvez não faça sentido dividir o trabalho de rebuild dos índices em múltiplas etapas, porém, tem sido cada vez mais comum a existência de bancos com muitos dados e/ou muitos objetos.
Uma de minhas alunas, por exemplo, relatou estar trabalhando com um banco que possui mais de 7 mil tabelas, e nesse caso torna-se bastante relevante definirmos quais objetos devem ter prioridade no processo de reconstrução e reorganização de índices.
Como priorizar o REBUILD / REORGANIZE das tabelas
Para você definir uma ordem de prioridade para REBUILD e/ou REORGANIZE das suas tabelas não há muita escapatória além de criar uma tabela onde você possa organizar os objetos a serem priorizados.
Por isso, nossa solução começa criando uma tabela chamada REBUILDS_PRIORITARIOS, onde você cadastrará a ordem de execução dos REBUILDS. Em nosso exemplo, definimos as tabelas: clientes, vendas e produtos.
-------------------------------------------------- -- Segregação de Rebuilds - Requisitos -------------------------------------------------- -- Tabela contendo nomes das tabelas prioritárias para rebuild if object_id('rebuilds_prioritarios') is not null drop table rebuilds_prioritarios create table rebuilds_prioritarios (prioridade int primary key clustered, nome_tabela varchar(300)) insert into rebuilds_prioritarios values (1, 'clientes'), (2, 'vendas'), (3, 'produtos') select * from rebuilds_prioritarios
Por surpreendente que pareça, a parte mais difícil e mais chata está terminada nesse exato ponto!
O que temos a fazer agora são apenas 2 passos:
1) Criar um JOB que reconstrua os índices das tabelas prioritárias.
2) Criar um JOB que reconstrua os índices das demais tabelas do banco (não prioritárias).
O melhor de tudo vem agora! Os dois scripts que criei, funcionam para qualquer banco que você estiver trabalhando!! Ou seja, basta criar a tabela REBUILDS_PRIORITARIOS, inserir as tabelas prioritárias, e depois rodar os scripts abaixo, ou pelo SQL ou através de JOBs do SQL Agent!
Só isso! Você cumpriu a tarefa de executar reorganização e reconstrução de índices por prioridade!
Abaixo o script completo da aula de hoje:
------------------------------------------------------------------ -- Rebuild de índices em ordem de prioridade ------------------------------------------------------------------ -------------------------------------------------- -- Banco de testes -------------------------------------------------- use master if db_id('curso') is not null drop database curso go create database curso go use curso go create table colaboradores ( id smallint identity(1,1) primary key, nm varchar(100) not null, ) create table produtos ( id smallint identity(1,1) primary key, nm varchar(100) unique, vl decimal(10,2), ic_ativo bit not null default (1) ) create table clientes ( id smallint identity(1,1) primary key, nm varchar(100) unique, dt_nascimento date ) create table vendas ( id int not null identity(1, 1) primary key, dt datetime not null, id_produto smallint not null foreign key references produtos(id), id_vendedor smallint foreign key references colaboradores(id), id_cliente smallint foreign key references clientes(id), qt int check (qt > 0), vl_unitario decimal (10,2), vl_venda as qt * vl_unitario ) create table feriados ( dt date primary key clustered, ds_dia_semana varchar(100), ds_feriado varchar(1000) ) -------------------------------------------------- -- Segregação de Rebuilds - Requisitos -------------------------------------------------- -- Tabela contendo nomes das tabelas prioritárias para rebuild if object_id('rebuilds_prioritarios') is not null drop table rebuilds_prioritarios create table rebuilds_prioritarios (prioridade int primary key clustered, nome_tabela varchar(300)) insert into rebuilds_prioritarios values (1, 'clientes'), (2, 'vendas'), (3, 'produtos') select * from rebuilds_prioritarios -------------------------------------------------- -- Job 1: Rebuilds das tabelas prioritárias -------------------------------------------------- declare @table_name varchar(300) declare @index_name varchar(300) declare @fragmentacao numeric(10, 8) declare @script varchar(500) declare @c cursor set @c = cursor for select nome_tabela = object_name(b.object_id), nome_indice = name, fragmentacao_media = avg_fragmentation_in_percent, script = case when avg_fragmentation_in_percent >= 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' rebuild with (online = on)' when avg_fragmentation_in_percent >= 5 and avg_fragmentation_in_percent <= 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' reorganize' else '-- Fragmentação inferior a 5% não necessita atuação.' end from sys.dm_db_index_physical_stats (db_id('curso'), null, null, null, null) as a -- (Parâmetros da função: banco de dados, tabela, indice, partição física, modo de analise: default, null, limited (limitado), sampled (amostra), detailed (detalhado)) join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id join rebuilds_prioritarios p on object_name(b.object_id) = p.nome_tabela order by p.prioridade asc open @c fetch next from @c into @table_name, @index_name, @fragmentacao, @script while @@fetch_status = 0 begin print 'Dados do rebuild: Tabela: ' + @table_name + ' | Índice: ' + @index_name + ' | Fragmentação média: ' + convert(varchar, @fragmentacao) print 'Script: ' + @script execute (@script) print '' fetch next from @c into @table_name, @index_name, @fragmentacao, @script end close @c deallocate @c go -------------------------------------------------- -- Job 2: Rebuilds das tabelas restantes -------------------------------------------------- -- Mesmo código acima, apenas o select que lista as tabelas a serem reconstruídas precisa ser alterado na última linha (cláusula where): declare @table_name varchar(300) declare @index_name varchar(300) declare @fragmentacao numeric(10, 8) declare @script varchar(500) declare @c cursor set @c = cursor for select nome_tabela = object_name(b.object_id), nome_indice = name, fragmentacao_media = avg_fragmentation_in_percent, script = case when avg_fragmentation_in_percent >= 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' rebuild with (online = on)' when avg_fragmentation_in_percent >= 5 and avg_fragmentation_in_percent <= 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' reorganize' else '-- Fragmentação inferior a 5% não necessita atuação.' end from sys.dm_db_index_physical_stats (db_id('curso'), null, null, null, null) as a -- (Parâmetros da função: banco de dados, tabela, indice, partição física, modo de analise: default, null, limited (limitado), sampled (amostra), detailed (detalhado)) join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id where object_name(b.object_id) not in (select nome_tabela from rebuilds_prioritarios) open @c fetch next from @c into @table_name, @index_name, @fragmentacao, @script while @@fetch_status = 0 begin print 'Dados do rebuild: Tabela: ' + @table_name + ' | Índice: ' + @index_name + ' | Fragmentação média: ' + convert(varchar, @fragmentacao) print 'Script: ' + @script execute (@script) print '' fetch next from @c into @table_name, @index_name, @fragmentacao, @script end close @c deallocate @c -- Referências: -- Curso SQL do Zero ao Master: -- Módulo 2 Aulas 03 e 06 -- Módulo 3 Aulas 16, 23 e 26 -- Módulo 4 Aulas 35 -- Canal youtube dba-pro: -- Vídeo sobre manutenção de índices - https://www.youtube.com/watch?v=GRHaxEcYmCYCONCLUSÃO
Como o crescimento dos bancos de dados muitas vezes é necessário dividir cargas de processamento, inclusive aquelas relativas a manutenção do seu banco de dados, como por exemplo, a reorganização e/ou reconstrução de índices.
Tenha o seu banco muitos dados ou muitas tabelas, certamente saber como fazer a manutenção dos índices por prioridade é uma excelente carta na manga para você, não só para resolver esse problema, mas também outros semelhantes, os quais exigem divisão do trabalho em múltiplas etapas.
Espero que também tenha gostado desse conteúdo.
Abraço do seu amigo Josué 🙂