Data

31 ago, 2016

SQL Server físico vs lógico – Parte 03

Publicidade

Este artigo é baseado na palestra “Estrutura física e fragmentação – quando e porquê desfragmentar tabelas e índices” que apresentei aos colegas DBAs da empresa RAIZEN em março de 2015.

Introdução

No segundo artigo desta série, eu apresentei algumas técnicas para minimizar a ocorrência de fragmentação em índices. Mas, uma vez que seu banco de dados vai para produção, é importante garantir uma manutenção adequada para que ele continue funcionando a todo vapor.

Neste artigo, apresentarei itens básicos da manutenção da estrutura física do banco e técnicas simples para avaliar propriedades dos índices, tais como seu nível de fragmentação e detalhes da sua estrutura B-tree.

Manutenção no seu banco de dados

O seguro morreu de velho. Melhor prevenir do que remediar. “Better safe than sorry”. Não deve ser por acaso que existem tantos ditados para dizer a mesma coisa. Eu prefiro não questionar e aceitar o conselho.

Já vi empresas se darem mal por confiar que o simples fato de fazerem backups regulares garantiria o retorno do ambiente. E se o backup foi feito num momento em que a base já estivesse corrompida?
Os planos de manutenção disponíveis no SQL Server dão uma grande ajuda para organizar as ações básicas de manutenção do banco de dados, mas aqui eu vou focar em algumas atividades que considero essenciais e apresentá-las com um pouco mais de detalhe.

Uma destas atividades essenciais para garantir a operação do banco de dados é verificar a sua integridade física periodicamente. Isso nos permite identificar e corrigir falhas antes que a base seja marcada com estado “SUSPECT” e pare de vez.

Normalmente, eu uso o comando “DBCC CHECKDB” para fazer esta verificação. Ele é bastante simples e versátil e engloba três operações que antigamente era feitas separadamente:

  1. Verificação da alocação das páginas de dados e estruturas internas;
  2. Verificação da consistência das tabelas e seus índices;
  3. Validação dos catálogos internos do SQL Server, que contêm metadados.

Existe quase uma dúzia de opções para este comando, mas eu costumo usá-lo da forma mais simples. O output é bastante detalhado e vale a pena guardá-lo para documentação. Na verdade, as duas últimas linhas do relatório trazem o resumo de todo o processo, informando se foram ou não detectados erros no banco. Veja exemplo a seguir.

Comandos:

USE AdventureWorks
GO
DBCC CHECKDB
GO

Output completo do teste CheckDB: clique aqui.

Últimas linhas:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Caso seja detectado qualquer erro, será necessário colocar a base em modo SINGLE USER para executar a reparação. Na prática, existem duas opções: a reparação com possível perda de dados (REPAIR_ALLOW_DATA_LOSS) e a com reconstrução dos objetos (“rebuild”).

A primeira é mais rápida, mas como já diz o seu nome, existe o risco de que dados sejam perdidos. O REPAIR_REBUILD é mais abrangente e seguro, mas vai demorar mais tempo para ser concluído. Portanto, esta opção deixará sua aplicação fora do ar por mais tempo.

Identificando a estrutura física, tamanho dos índices e fragmentação

Normalmente, os DBAs se preocupam em analisar propriedades do banco de dados como um todo, sem entrar no detalhe de cada objeto. Mas às vezes isso é necessário.

Conhecer o tamanho de cada objeto (tabelas e índices) pode ser muito útil para se conseguir uma sintonia fina da operação da base de dados.

Eu costumo utilizar dois objetos de sistema para conseguir estas informações:

É fácil acessar diversas informações usando apenas estes dois objetos. Vamos a alguns exemplos.

Checando tamanho de tabelas/heaps/índices

Conhecer o tamanho de tabelas, heaps (tabelas sem índice clusterizado) e índices é útil para definir uma série de práticas no banco de dados:

  • Decisão sobre quando particionar tabelas e índices;
  • Decisão de transformar heaps em tabelas com índices clusterizados;
  • Avaliação da qualidade de um índice;
  • Avaliação da política de indexação de cada tabela.

Existem várias maneiras de se obter esta informação. Aqui usaremos a visão SYS.DM_DB_PARTITION_STATS, mencionada anteriormente. Ela informa o número de páginas usadas e páginas reservadas para cada objeto do banco (tabelas com índices clusterizados, heaps e índices não-clusterizados). Estas informações estão disponíveis nos campos USED_PAGE_COUNT e RESERVERD_PAGE_COUNT, respectivamente.

Como sabemos que cada página de dados do SQL Server tem sempre 8 Kbytes, a conta para calcular espaço é simples (nesta consulta, os resultados foram convertidos para gigabytes).

Veja este exemplo:

-- 1.) tamanho de cada tabela/heap/índice em Gbyte
SELECT 
	  object_name(s.object_id) AS Tabela
	, i.name AS Indice -- será NULL se a tabela for HEAP
	, (s.[used_page_count]     * 8.0 / power(1024,2)) AS EspacoUsadoGB
	, (s.[reserved_page_count] * 8.0 / power(1024,2)) AS EspacoReservadoGB
FROM sys.dm_db_partition_stats s 
	LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
ORDER BY 1,2
GO

Output completo do teste IndiceEspacoGB: clique aqui.

Você vai notar no relatório apresentado que a escala de Gigabytes é grande demais para os índices da base AdventureWorks, que raramente tem mais que alguns poucos Megabytes. Mas deixo a cargo do leitor adaptar o script acima para escala que melhor lhe convir: basta mudar o segundo parâmetro da função POWER, que representa o expoente da operação matemática. Use expoente 1, 2 ou 3 para ter resultados em Mega, Giga ou Terabytes, por exemplo.

Fragmentação e detalhes da estrutura física dos índices

É evidente que a fragmentação pode causar problemas na sua base. Mas antes de tomarmos qualquer ação corretiva, precisamos saber quem (quais índices) precisa ser desfragmentado.

Eu uso a função tabular SYS.DM_DB_INDEX_PHYSICAL_STATS (já citada) para obter as estatísticas sobre fragmentação. Tecnicamente, este objeto não poderia ser chamado de DMV (Dynamic Management View), pois é uma função tabular e não uma visão. Porém, por conveniência, muita gente usa a sigla DMV para se referir aos dois tipos de objeto.

A SYS.DM_DB_INDEX_PHYSICAL_STATS aceita cinco parâmetros:

  • database_id: para restringir a pesquisa a um único banco. Use o valor NULL para ver informações de fragmentação dos objetos da instância toda;
  • object_id: representa o identificador da tabela a ser estudada. De novo, use o valor NULL para ver informações de todas as tabelas;
  • index_id: especifica qual índice da tabela será estudado, ou NULL para avaliar todos os índices;
  • partition_number: usada para índices particionados, especifica a partição desejada. Use NULL para avaliar todos os índices;
  • mode: determina nível de detalhamento da informação entre LIMITED, SAMPLED ou DETAILED (default é LIMITED).

A seguir, um exemplo de aplicação desta DMV para a verificação das estatísticas de todos os índices da tabela Person.Person da base demonstrativa AdventureWorks.

-- 2.) fragmentacao, tamanho médio dos registro, detalhes do B-tree
DECLARE @intObjectID INT
SELECT @intObjectID = OBJECT_ID('Person.Person')
SELECT *
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), @intObjectID , NULL, NULL, 'DETAILED') s
	LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id

Output completo do teste Fragmentacao: clique aqui.

Observe que esta listagem inclui a coluna “avg_fragmentation_in_percent” (fragmentação média em percentagem), que servirá de base para identificarmos o tratamento dado a cada índice.

Tratando a questão de fragmentação dos índices

A recomendação da MICROSOFT é de reorganizar o índice (REORG) quando a fragmentação está na faixa dos 5% a 30% e reconstruí-lo (REBUILD) para corrigir fragmentação maior que 30%. Fica implícito, portanto, que para índices com fragmentação menor que 5%, não se recomenda tratamento algum.

Um jeito prático de tratar todos os índices do banco seria gerar as instruções de tratamento. O script a seguir mostra uma alternativa para realizar esta operação:

-- 3.) tratando a fragmentação de todos os índices do banco
 
SELECT 
	  I.name AS IndexName
	, OBJECT_SCHEMA_NAME(I.object_id) as SchemaName
	, OBJECT_NAME(I.object_id) as TableName 
	, CASE WHEN Q.avg_fragmentation_in_percent > 30.0 THEN 'REBUILD' ELSE 'REORGANIZE' END AS ActionName
--	, Q.avg_fragmentation_in_percent AS Frag
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') Q
	INNER JOIN sys.indexes I  on Q.object_id = I.object_id AND Q.index_id = I.index_id
WHERE Q.avg_fragmentation_in_percent > 5.0
)
OPEN meuCursor
FETCH meuCursor INTO @INDEX, @SCHEMA, @TABLE , @ACTION
WHILE @@FETCH_STATUS >= 0 
	BEGIN
	SET @SQL = 'ALTER INDEX [' +  @INDEX + '] ON [' + @SCHEMA + '].[' + @TABLE + '] ' + @ACTION + ';'
	PRINT @SQL
	EXEC (@SQL)
	FETCH meuCursor INTO @INDEX, @SCHEMA, @TABLE , @ACTION
	END
CLOSE meuCursor
DEALLOCATE meuCursor

O leitor mais atento perceberá que a manutenção de índices costuma apresentar uma sensível melhoria de performance nas tabelas e índices grandes (com pelo menos mais de 100 mil registros) e que apresentavam nível de fragmentação acima dos 50%. Esta melhoria nem sempre é observada em tabelas pequenas (menos de 10 mil registros) ou quando o nível de fragmentação é médio ou baixo (menor que 50%).

Recomendação

Manutenção do banco de dados evidentemente é uma boa prática. Salvo as mudanças intencionais, a manutenção garante que seu banco de dados vai funcionar sempre próximo do comportamento que ele apresentou historicamente.

Porém, não existe mágica. Uma aplicação que não adote melhores práticas de desenvolvimento ou que não tenha um planejamento adequado do uso do seu banco de dados (como, por exemplo, crescimento exagerado e imprevisto), cedo ou tarde apresentará problemas. E as técnicas de manutenção não serão solução para estes casos.

Um abraço e até a próxima!

Leituras sugeridas

  1. Principais Dicas para uma Manutenção Eficiente do Banco de Dados, por Paul Randall
  2. How Does SQL Server Store Data?, por Brent Ozar
  3. SQL SERVER – Fragmentation, por Pinal Dave
  4. Index Related Dynamic Management Views and Functions (Transact-SQL), por MICROSOFT
  5. Reorganizar e Recriar Índices, por MICROSOFT