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.
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.
Deixo mostrar que a tabela está completamente vazia (saída no modo texto):
Vou inserir um único registro e vou colocar um nome aleatório baseado na função NEWID.
Preparem-se para o grande momento! Vamos limpar a memória usando o DBCC DROPCLEANBUFFER…
E rodamos a consulta!
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.
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.
Nesse primeiro momento, a tabela está realmente vazia:
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.
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.
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:
- Truncate Table
- DELETE WITH (TABLOCK)
- DELETE com Lock Escalation para TABLOCK
- ALTER TABLE … REBUILD
- CREATE CLUSTERED INDEX
Podemos apagar os registros usando DELETE TOP(n) WITH (PAGLOCK) .
Dessa forma, evitamos a possibilidade de ocorrer um Lock Escalation durante a remoção de registros.
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.