sp_spaceused


Continuando a série dos comandos históricos, vou apresentar o comando sp_spaceused. Embora esse seja um comando antigo, essa é uma das procedures que mais uso no dia a dia.

Nos posts anteriores comentei sobre a importância do SET STATISTICS IO e do uso correto do DBCC DROPCLEANBUFFERS. Comentei sobre o DBCC SHOWCONTIG para visualizar a fragmentação e o famoso DBCC PAGE em ação. É uma saga de otimização ao contrário!

Nesse post vamos fazer uma mágica! Sim, vou mostrar uma query que é um tanto lenta… talvez você a conheça:

SELECT * FROM produtos WHERE id = 1234

Dessa vez ela conseguiu bater o recorde de lentidão e sem uma explicação aparente! E cuidado porque essa “mágica” pode estar ocorrendo no seu ambiente SQL.

Hora do Show!

Eu tenho uma tabela vazia chamada “produtos”. Se você leu os artigos anteriores, então deve conhecer bem. Ela é simples, tem dois campos e nenhum índice.

image

Para garantir que não há registro na tabela, vou rodar o comando DELETE sem nenhuma condição WHERE. Isso é para garantir que qualquer informação seja eliminada antes de começar o truque.

image

Deixo mostrar que a tabela está completamente vazia (saída no modo texto):

image

Vou inserir um único registro e vou colocar um nome aleatório baseado na função NEWID.

image

Preparem-se para o grande momento! Vamos limpar a memória usando o DBCC DROPCLEANBUFFER…

image

E rodamos a consulta!

image

A consulta de uma tabela com um único registro demorou 3 segundos. Vou repetir essa última execução com a saída dos resultados do SET STATISTICS TIME E SET STATISTICS IO.

image

image

Mas eu juro que a tabela só tem um registro!

 

Revelando o Truque

A parte principal da mágica é a preparação da tabela “produtos”, pois é ela quem causa o alto tempo de execução.

Passo 1: Criar a tabela “produtos” – Criamos uma tabela usando os campos [id] e [nome]. Usamos o tipo CHAR para aumentar o número de páginas da tabela sem precisar adicionar uma quantidade muito grande de registros. Entretanto, qualquer tipo de dado poderia ser usado. A única restrição é não criar índices ou colunas com chaves primárias.

image

Nesse primeiro momento, a tabela está realmente vazia:

image

 

Passo 2: Populamos a tabela com registros – O número de registros não é importante. A tabela deve ocupar um grande número de páginas em disco. A forma mais rápida de popular uma tabela é usando INSERT SELECT, que insere um número exponencial de registros.

image

Ao final do passo 2, a tabela estará ocupando 30MB. Se você quiser criar impacto, pode continuar adicionando registros até chegar na casa dos GB.

image

 

Passo 3. O grande segredo é apagar registros sem desalocá-los – Nas estruturas Heap, existem algumas condições necessárias para que as páginas sejam desalocadas durante o processo de remoção de registro. Isso significa que os registros serão apagados, mas a tabela continua ocupando espaço.

O espaço em disco é desalocado nas seguintes condições:

  1. Truncate Table
  2. DELETE WITH (TABLOCK)
  3. DELETE com Lock Escalation para TABLOCK
  4. ALTER TABLE … REBUILD
  5. CREATE CLUSTERED INDEX

Podemos apagar os registros usando DELETE TOP(n) WITH (PAGLOCK).

image

Dessa forma, evitamos a possibilidade de ocorrer um Lock Escalation durante a remoção de registros.

image

No final, nossa tabela está pronta! Possui 30MB de espaço alocado e nenhum registro.

Passo 4: Preparativos finais – Podemos deixar o efeito mais visível com as seguintes ações:

  • Habilitar o Trace Flag Global 652 para desligar as operações de read-ahead
  • Limpeza da Buffer Pool usando o CHECKPOINT + DBCC DROPCLEANBUFFERS

Como a tabela não possui registro, então podemos realizar operações de DELETE sem o risco de Lock Escalation. Pronto!

 

Conclusão

Esse é um problema muito comum das HEAPS e o diagnóstico não é trivial. Muitas pessoas realizam a desfragmentação de índice, mas não incluem rotinas para desfragmentar tabelas sem índice clustered. Como recomendação geral, todas as tabelas deveriam ter índice clustered.

No próximo post, vou falar sobre a estrutude do índice.

Comments (4)

  1. Catae, há situações onde os valores retornados pelo sp_spaceused estão desatualizados, onde é necessário usar o parâmetros @updateusage para atualizar e ver os dados corretos.

    Minha pergunta é em relação a qual processo é disparado internamente para atualizar estes dados (se isso ocorre) e o que faz disparar (threshold) esse processo. Gostaria de entender a razão destes dados ficarem desatualizados.

    Valeu. Abraço.

    1. Oi Erickson, como vai? Existia um comportamento engraçado do SQL7.0/2000 que fazia o controle de alocação de espaço usando tabelas de sistema (sysindexes). Essa estrutura mudou completamente com o SQL2005 (lembre-se dos HoBts). Eu não lembro de nenhuma situação recente que precisei atualizar os valores do sp_spaceused usando o DBCC UPDATEUSAGE. Oficialmente, não sei dizer se ainda é necessário rodar esse comando.

      Procurei em todos os lugares e a única referência que achei foi essa:
      https://technet.microsoft.com/en-us/magazine/2007.07.sqlqa.aspx

      Pelo que sei, a contagem de página atual baseada em HoBts e Partitions é precisa e não precisa de atualização. Mas é sempre bom deixar esse comando na manga para aqueles dias que as coisas não parecem funcionar direito.

      Sobre o processo: antigamente o SQL7/2000 usava as páginas GAM/SGAM/PFS para alocação, ao mesmo tempo que controlava o número de páginas alocados via sysindexes. Havia algumas condições que deixavam esses números fora de sincronia e, por isso, era necessário rodar o DBCC UPDATEUSAGE. Havia um comportamento semelhante também com a coluna de “rowmodcnt”. Não conheço muitos detalhes disso.

      Espero que tenha esclarecido um pouco.

      Abraços, Fabricio

  2. Jonne T. disse:

    Olá, tudo bem??
    Primeiro parabéns pelos seus artigos meu amigo, tem me ajudado muito. Obrigado mesmo!!

    Porém as imagems deste artigo nao estão aparecendo.. Poderia verificar por gentileza?
    Obrigado!

    1. De novo aconteceu de sumir imagens no blog? Vou ver isso agora. Obrigado pelo aviso.

Skip to main content