Analisando tabelas utilizando DBCC ShowContig

Conheça através deste artigo a ferramente DBCC ShowContig.

por Diego Nogare

Antes de começar a fundo o artigo, seria interessante nivelarmos os conhecimentos. Alguns termos que serão apresentados podem ser desconhecidos para alguns leitores.

Fill Factor: É a porcentagem de espaço em branco utilizado na criação de cada página de dados. Página de Dados: É onde ficam armazenados os dados das tabelas, cada página tem exatamente 8Kb e podem ter diversos registros, um registro deve iniciar e terminar na mesma página.

Heap: É um conceito que representa o agrupamento de todas as páginas de uma tabela.

Extent: É um conjunto de 8 páginas, totalizando 64Kb (8 páginas de 8Kb).

Page Split: Ocorre quando os dados armazenados em uma página são divididos em duas, deixando aproximadamente metade de cada página preenchida.

Index Clustered: É um índice físico que ordena os dados fisicamente na tabela, os dados são colocados em ordem dentro do Heap. Uma analogia muito utilizada para exemplificar o index clustered, é pensar em um dicionário. Os dados estão ordenados fisicamente.

Fragmentação: Quantidade de espaço em branco, existente no contexto que está sendo utilizado. Se falarmos de Fragmentação de Tabela, é o espaço em branco da tabela, se for Fragmentação de Página é o espaço em branco na página, e assim por diante.

Agora que já apresentamos os termos que poderiam ser novidades, vamos analisar a densidade de uma tabela com 20.000 registros. Para isso, vamos acompanhar o código que utilizaremos para analisar esta tabela.

Primeira coisa cria-se uma tabela com um campo INT e dois campos CHAR(100). Utilizei o tipo de dados CHAR para ele ocupar realmente as 100 posições pré-definidas, substituindo por espaços os caracteres que faltam para completar as 100 posições.

A segunda coisa foi criar duas variáveis INT para utilizar no WHILE que populará a tabela com os 20.000 registros. A primeira variável é @CONT e a segunda é @TOTAL, iniciadas respectivamente com 0 (zero) e 20000 (vinte mil). Depois é feito um WHILE para inserir dados na tabela.

Para analisar a tabela, utilizamos a ferramenta DBCC ShowContig. Esta ferramenta resulta na analise das tabelas do banco. Caso a tabela tenha sido criada em um banco no qual exista outra tabelas, o resultado demonstrará a analise de todas as tabelas. Para chamar esta ferramenta, digite DBCC SHOWCONTIG e execute esta instrução. Vamos entender agora o que significam os principais resultados obtidos.

Pages Scanned é a quantidade de páginas que foram consultadas, isso significa que esta tabela possui 541 páginas, mas não significa que todas as páginas estão preenchidas completamente com 8Kb de informações.

Avg. Pages per Extent representa a média de páginas em cada Extent, lembrando que um Extent possui 64Kb.

Extent Scan Fragmentation é a média de porcentagem de fragmentação em cada Extent.

Avg. Bytes Free per Page apresenta a média da quantidade de Bytes livres em cada página. Neste resultado são 221.7 Bytes.

Avg. Page Density é a porcentagem média de densidade nas páginas. Densidade pode ser entendida neste ponto como a quantidade ocupada pelos dados. O texto (FULL) é uma analise que o SQL Server fez baseado a porcentagem, este texto pode variar de acordo com a porcentagem apresentada.

A conclusão que conseguimos chegar, baseado na analise feita nos resultados da ferramenta DBCC ShowContig é que a nossa tabela praticamente não está fragmentada e está com sua densidade de dados bem alta. Este tipo de analise não segue um roteiro pré-definido especificando que um resultado é bom ou ruim. A ferramenta apresenta os resultados da analise e o DBA ou o analista que está solicitando os dados chega a uma conclusão tomando como base o que pretende alcançar. Se o objetivo desta tabela é realizar diversas consultas, uma tabela pouco fragmentada é ótima, pois são menos páginas que devem ser consultadas para recuperar todos os dados. Já para tabelas que necessitam muitas inserções, uma tabela fragmentada é mais rápida, pois sempre possui espaço em páginas e não é necessário ocorrer Page Split antes da inserção. O Page Split só ocorre quando trabalhamos com Índice Físico (index clustered) na tabela. Quando a tabela não possui um índice, o dado é armazenado em qualquer página dentro do Heap. Para evitar um Page Split em uma página, uma saída seria criar a tabela com Fill Factor de 50%. Isso assegura que quando houver a necessidade de inserir um registro em uma tabela, existe grande chance de já ter uma página com espaço suficiente para armazenar este registro.