Monitorando a fragmentação dos índices
Um dos grandes problemas que temos com relação a performance é devido a fragmentação de nossos índices. Com o grande número de inserções, alterações e exclusões que ocorrem em nossas tabelas, os índices se fragmentam cada vez mais, ocasionando uma lentidão na manipulação dos dados desses índices.
Como você resolveria esse problema de fragmentação?
R: Essa é fácil Fabrício, criando uma rotina periódica que executa os procedimentos de REBUILD ou REORGANIZE para os meus índices.
Exato, mas com que frequencia você rodaria essa rotina? Quais as tabelas que precisam ter seus índices desfragmentados com uma maior frequência?
Imagina que você possua muitas tabelas com milhares de registros e só tenha uma janela de 1 hora para desfragmentar todos os seus índices. Voce terá que dividir a desfragmentação dos índices em vários dias além de também definir a frequência com que os índices serão reorganizados ou reconstruídos.
Esse é exatamente o ambiente que possuo, com muitas tabelas grandes e com apenas uma hora de janela para realizar esse procedimento.
Digamos que eu possua 100 tabelas em uma determinada database, desfragmentar as 80 menores tabelas demoram 40 minutos, outras 10 tabelas juntas demoram 50 min, outras 5 tabelas juntas demoram 45 minutos, 3 tabelas demoram 40 minutos e 2 tabelas demoram 50 minutos.
Nessa situação, eu criaria 5 jobs de manutenção dos índices:
- Job 1 – 80 tabelas
- Job 2 – 10 tabelas
- Job 3 – 5 tabelas
- Job 4 – 3 Tabelas
- Job 5 – 2 Tabelas
Mas como eu saberia com que frequência rodar cada um desses jobs?
Para definir essa frequência, eu utilizo uma rotina que armazena diariamente a fragmentação de todos os índices de minhas principais databases. Como a query para verificar a fragmentação dos índices é um pouco custosa, eu utilizo um servidor D-1 que recebe um restore diário das bases de produção para executar esse procedimento. Caso você não possua um servidor D-1 você deve agendar um job para executar esse procedimento em um horário de pouco movimento.
Inicialmente, devemos criar uma tabela que armazenará nosso histórico de fragmentação.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE [dbo].[Hitorico_Fragmentacao_Indice]( [Id_Hitorico_Fragmentacao_Indice] [int] IDENTITY(1,1) NOT NULL, [Dt_Referencia] [datetime] NULL, [Nm_Servidor] [varchar](50) NULL, [Nm_Database] [varchar](50) NULL, [Nm_Tabela] [varchar](50) NULL, [Nm_Indice] [varchar](70) NULL, [Avg_Fragmentation_In_Percent] [numeric](5, 2) NULL, [Page_Count] [int] NULL, [Fill_Factor] [tinyint] NULL) |
Para popular essa tabela, basta rodar a query abaixo que retorna a fragmentação de todos os índices da database em que ela está sendo executada.
|
1 2 3 4 5 6 |
INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent, Page_Count,Fill_Factor) SELECT getdate(), @@servername, db_name(db_id()), object_name(B.Object_id), B.Name, avg_fragmentation_in_percent,page_Count,fill_factor FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) A join sys.indexes B on a.object_id = B.Object_id and A.index_id = B.index_id ORDER BY object_name(B.Object_id), B.index_id |
OBS: Lembrando que essa é a query demorada e deve ser executada em um horário de pouco movimento do banco de dados.
Com essa importante informação sendo armazenada no banco de dados, recebo no meu CheckList diário do banco de dados um relatório de todos os índices que estão com uma fragmentação maior que 5% e consigo identificar os índices que mais estão se fragmentando para analisar um possível aumento na frequência do job de desfragmentação.
Segue abaixo a query que gera os dados para a planilha:
|
1 2 3 4 5 6 7 8 |
declare @Dt_Referencia datetime set @Dt_Referencia = cast(floor(cast( getdate() as float)) as datetime) SELECT Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice, Avg_Fragmentation_In_Percent, Page_Count, Fill_Factor FROM Hitorico_Fragmentacao_Indice (nolock) WHERE Avg_Fragmentation_In_Percent > 5 AND page_count > 1000 -- Eliminar índices pequenos AND Dt_Referencia >= @Dt_Referencia |
Essa query retorna o seguinte resultado:
Quando a fragmentação de algum índice ultrapassa 15%, essa linha ja vem vermelha e em negrito na minha planilha de CheckList.
Devido a facilidade de implementação dessa rotina (para quem possui uma janela de execução), acredito que seria bem interessante guardar essa informação e aumentar a baseline do seu banco de dados.
Além disso, no meu próximo post mostrarei como essa informação me ajuda a definir o valor do FILL FACTOR de minhas tabelas.
Gostou dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Aprenda a criar essa e outras rotinas para administrar melhor seu SQL Server no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Grande Fabricio,
Parabens pelo post. Mas me ficou uma duvida aqui, depois voce executa manualmente um rebuild ou reorganize nessas tabelas, ou você possui alguma procedure na qual consegues fazer um controle de quais tabelas serao realizadas o procedimento?
Abraços e mais uma vez… meus parabens.
Marcos Freccia
@SqlFreccia
Valeu Marcos
Não executo os REBUILDs manualmente, mas criei procedures e defini manualmente quais tabelas serão reindexadas por cada procedure.
stpReindex_NomeDatabase_1
stpReindex_NomeDatabase_2
stpReindex_NomeDatabase_3
Entretanto, você também pode ser criar uma lógica dentro das procedures para definir as tabelas que serão reindexadas com alguma tabela física de controlando. Ainda não fiz isso mas posso fazer no futuro.
Abraços
Fala Fabricio,
excelente artigo, e posso dizer: facilitou bastante a adm dos meus indices…
pra melhorar, coloquei a query no send_dbmail e agora recebo um email com o relatorio dos indices mais criticos…
abcs,
Kleber.
Valeu kleber!!!
Abraços.
Parabéns pelo Artigo Fabrício!
Esse script ficou excelente. Só tenho uma dúvida: Porque que em menos de 1 hora meus índices estão todos fragmentados novamente? Algumas vezes ao ponto de ter de fazer REBUILD.
Obrigado.
Obrigado Afrânio.
Os seus índices devem sofrer muito update/delete/insert e os novos dados devem estar sendo incluídos no meio dos índices e não no final, como acontece em um indice de uma coluna identity.
Verifica como está o FILLFACTOR dos seus índices. Talvez será necessário algum ajuste (cada caso é um caso).
Abraços
Posta o código do rebuild tambem, por favor.
Parabens pelo sua publicação, muito boa, me ajudou muito.
Boa Tarde! Estou recebendo um erro quando executo o INSERT ( String or binary data would be truncated ). Será que poderia me ajudar? Muito Obrigado e Parabéns por ser um grande profissional e dividir suas informações com a gente!
Boa Tarde Anderson,
Aumenta o tamanho das colunas da tabela para não dar o erro.
Obrigado.
Resolvido, Obrigado!
Boa Tarde, Parabéns pelo Post, muito bem explicado.
Poderia postar um exemplo de Rebuild e Reorganize.
Como saber qual a melhor opção a ser usada?
Obrigado.
Obrigado Renato.
Segue um artigo com uma explicação boa da diferença entre os dois:
http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/
Olá boa tarde
Os índices que estão com a coluna “avg_fragmentation_in_percent” como “0” ou “0,15….” significam que não seria necessário fazer a desfragmentação ?
Obrigado
Thiago
Boa Tarde Thiago,
Só se preocupe com índices com mais de 1000 páginas e uma fragmentação maior que 10,0%.
Obrigado
Bom dia Fabrício,
Porque devemos nos preocupar apenas com índices que tenham mais de 1000 páginas?
Paulo Assis
Olá Paulo,
Índices com menos de 1000 páginas são muito pequenos e não fariam muita diferença. Por isso, se preocupe apenas com índices com mais de 1000 páginas e com uma fragmentação maior do que 10%.
Abraço e bons estudos.
Fabrício
Muito Obrigado Fabrício!
Paulo Assis
Obrigado pela Ajuda !!!
Boa noite Fabrício!
Ao executar o comando para inserção dos registros na tabela criada está sendo apresentada a seguinte mensagem de erro:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘)’.
Já realizei diversas alterações e a mensagem de erro continua sendo apresentada.
Pode me ajudar?!
Atenciosamente,
Olá Rodrigo,
Fiz um teste novamente e executou com sucesso. Enviei um e-mail para você também.
Abraço.
Fabrício.
Estou tendo o mesmo problema para o Insert
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘)’.
Teria alguma dica para me ajudar?
Obrigado
Rodrigo
Bom Dia,
A dica é debugar passo a passo o código para descobrir onde esta dando a mensagem e ver uma correção. É o que eu teria que fazer no seu ambiente.
Olá
Por acaso o nível de compatibilidade da base não é 80 não né ?
Olá Fabrício
Muito legal…como você costuma fazer com a fragmentação das heaps?
Sei que o ideal é ter um índice Clustered, mas nem sempre é possível.
Abraço
Infelizmente na heap vai ter que dar um rebuild nela. Se for grande, bem custoso.
Fabrício, terial algum script semelhante para rodar em SQL com compatibilidade do 2000?
Estou apanhando para criar algum job para isso.
Ola Regiane,
Infelizmente eu não tenho. =(
Mas talvez tenha alguma forma de achar isso.
Obrigado mais uma vez Fabricio.
O post é antigo mas mas eficaz até os dias de hoje.
SHOW!
Ola fabricio,
Sigo seu blog e adoro suas dicas, e utilizo algumas, porem tenho uma instancia aqui que vem me causando problemas já algum tempo, esta comendo memoria que nem um come come ( velhos tempos de fliperama) bom, estava hoje fazendo verificação dos meus indices fragmentados, e existem muito 4010 indices com fragmetação acima dos 30%, fiz alguns manualmente mas quase instantaneamente ou passado algums minutos ja volta a 90% 95% do que estava antes, há alguma query ou algo que consiga fazer uma verificação se há algum vicio disso, visto que algumas dessas tabelas tem cerca de 430 registros.. estou achando muito estranho.
Obrigado Bruno, valida se esses índices possuem menos de 1000 páginas.
Se tiver, ignore da sua rotina de rebuild. São tão pequenos que não tem que se preocupar com isso para eles.