DBCC SHOWCONTIG


Tudo começou no artigo sobre a vantagem de usar o SET STATISTICS IO e depois falei sobre o uso correto do DBCC DROPCLEANBUFFERS (existe uso correto?). Dessa vez vamos explorar o antigo companheiro DBCC SHOWCONTIG.

Vamos começar analisando a query que demorava 1ms:

image

Esse tempo de 1 milissegund era possível porque todas os dados estavam previamente em memória. Limpando o cache e rodando a query novamente, observamos que o tempo subia para 846ms.

image

As páginas foram carregadas usando o mecanismo de read-ahead e isso pode ser observado através da visualização do Buffer Pool. Na figura abaixo, é percebe-se que algumas páginas foram carregadas no mesmo instante (veja a coluna read_microsec).

image

Com base na figura anterior, observamos que há conjuntos de páginas carregando em 57634 microssegundos, enquanto que outro conjunto carrega em 107984 microssegundos. Podemos inclusive verificar que as páginas 8728 a 8791 abrangem exatamente 64 páginas.

Esse comportamento ilustra bem a operação de “read-ahead”. Ao invés de requisitar ao storage as páginas individualmente, o SQL Server agregou as solicitações em uma única requisição. Isso significa que o servidor enviou um único I/O de 512Kb ao invés de enviar 64 I/O de 8Kb.

Entretanto, resolvemos desligar temporariamente o read-ahead usando o Trace Flag 652.

Observamos que a query realizou leituras físicas e teve um aumento significativo no tempo de execução:

image

Aquela mesma visualização do Buffer Pool se modifica e as páginas são carregadas em tempos diferentes (coluna read_microsec). Os tempos de disco ficaram em torno de 1ms por página.

image

Agora podemos fazer a matemática:

  • O tempo de leitura da página é de aproximadamente 1ms
  • Cada página contém 8 registros (coluna row_count)
  • Portanto, conseguimos fazer a leitura de 8 registros/ms.

A tabela de 10.000 registros poderá ser lida em aproximadamente 1250ms.

 

DBCC SHOWCONTIG

Ao longo do tempo, as operações de INSERT, DELETE e UPDATE causam buracos na tabela e fragmentam as páginas. O problema é que as tabelas podem crescer por conta da fragmentação!

Tenho certeza que aqueles da velha guarda vão se lembrar do DBCC SHOWCONTIG. Sua sintaxe simplificada é:

DBCC SHOWCONTIG(<table>)

image

A análise não tem muito segredo:

  • Há 1257 páginas agrupadas em 160 extents
  • Há aproximadamente 7,9 páginas por Extents (recomendado é 8)
  • Scan Density: 98,75% (quanto maior, melhor)
  • Avg Page Density: 79,88% (quanto maior, melhor)

O principal indicador de fragmentação é a última linha “Avg Page Density”, que fala sobre o preenchimento da página.

 

Fragmentação de Dados

Rodamos o seguinte script para simular a fragmentação ao longo do tempo:

image

ANTES: Sem fragmentação.

image

DEPOIS: Com fragmentação.

image

Reparamos que o Page Density despencou de 79,88% para apenas 42,49% de preenchimento. Isso reflete em um aumento do tempo da query, que estava em 1360ms, para 2437ms.

image

 

Conclusão

Estamos conseguindo piorar o tempo da query a cada artigo! Dessa vez, forçamos a fragmentação de dados para aumentar o tempo gasto acessando o disco. O diagnóstico foi feito usando o DBCC SHOWCONTIG.

Entretanto, desde o SQL Server 2005, o comando SHOWCONTIG foi substituído pela DMF sys.dm_db_index_physical_stats.

image

Existem enormes vantagens para usar essa nova sintaxe.

Para quem não se lembra, o comando DBCC SHOWCONTIG causava concorrência e bloqueios durante a sua execução, ficando quase que impraticável rodar em produção. A nova sintaxe permite especificar uma análise LIMITED ou SAMPLED ao invés de DETAILED.

No próximo artigo, vamos falar sobre o DBCC PAGE e tentaremos piorar ainda mais o desempenho da nossa query.

Comments (2)

  1. Pronto, coloquei a leitura dos artigos em dia.
    Fico feliz em ver o Mestre escrevendo novamente.
    Saudades das conversas.
    Um abraço

  2. Grande Alberto! Espero que esteja tudo bem por aí na Europa... quando você volta para o Brasil? Abraços, Fabricio

Skip to main content