|

Identificando as Maiores Tabelas do seu Banco no SQL Server

PorRenato Sanches
Compartilhe :

Descobrir quantos registros existem em cada tabela e quais são as maiores tabelas de um banco parece, à primeira vista, uma tarefa complicada. A solução óbvia seria rodar um COUNT(*) em cada tabela, mas isso é lento e pesado, principalmente em bancos com tabelas grandes.

A boa notícia é que o próprio SQL Server já mantém essas informações nas tabelas e views de sistema, então não é preciso contar linha por linha. Neste post você vai ver a forma recomendada de fazer isso nas versões atuais do SQL Server, além da abordagem mais antiga, para quem ainda mantém instâncias legadas.

Forma recomendada (SQL Server 2005 em diante): sys.partitions e sys.allocation_units

Desde o SQL Server 2005, a Microsoft substituiu a antiga sysindexes por um conjunto de catalog views mais completas e confiáveis: sys.tables, sys.indexes, sys.partitions e sys.allocation_units. Elas continuam guardando a contagem de linhas e o espaço ocupado por cada tabela, sem precisar de COUNT(*).

SELECT
    t.name AS tabela,
    p.rows AS total_linhas,
    SUM(a.total_pages) * 8 AS tamanho_kb,
    SUM(a.total_pages) * 8 / 1024 AS tamanho_mb
FROM sys.tables t
JOIN sys.indexes i
    ON t.object_id = i.object_id
JOIN sys.partitions p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
WHERE i.index_id IN (0, 1) -- 0 = heap (sem índice clustered), 1 = índice clustered
GROUP BY t.name, p.rows
ORDER BY tamanho_kb DESC;

O motivo do filtro i.index_id IN (0, 1) é o mesmo da abordagem antiga: cada tabela pode ter vários índices, e cada índice gera uma linha própria nessas views. Pegando apenas 0 (heap) ou 1 (índice clustered), garantimos uma linha por tabela, sem contar o mesmo dado várias vezes por causa dos índices não clustered.

Assim como no exemplo original, você pode usar TOP para listar só as maiores:

SELECT TOP 20
    t.name AS tabela,
    p.rows AS total_linhas,
    SUM(a.total_pages) * 8 AS tamanho_kb
FROM sys.tables t
JOIN sys.indexes i
    ON t.object_id = i.object_id
JOIN sys.partitions p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
WHERE i.index_id IN (0, 1)
GROUP BY t.name, p.rows
ORDER BY tamanho_kb DESC;

Alternativa mais simples: sp_spaceused

Para uma tabela específica, também é possível usar a procedure sp_spaceused, que já entrega o total de linhas e o espaço ocupado em um formato pronto:

EXEC sp_spaceused 'NomeDaTabela';

Ela é mais prática para checar uma tabela pontual, mas para comparar todas as tabelas do banco de uma vez a consulta com sys.partitions continua sendo a opção mais flexível, já que permite ordenar, filtrar e usar TOP como quiser.

Abordagem antiga: a tabela sysindexes

Em versões mais antigas do SQL Server (2000 e anteriores), a forma de resolver esse mesmo problema era consultar a tabela de sistema sysindexes, que guarda um campo rowcnt com o número de linhas de cada objeto, além de dpages, o total de páginas de dados ocupadas pela tabela.

Como sysindexes guarda uma linha para cada índice da tabela, era preciso filtrar apenas as linhas referentes à tabela em si:

  1. O campo indid deve ser 0 ou 1, valores referentes à tabela sem índice (heap) ou ao índice clustered.
  2. A função OBJECTPROPERTY garante que o objeto listado seja realmente uma tabela, e não outro tipo de objeto.
SELECT object_name(id), rowcnt, dpages * 8 AS tamanho
FROM sysindexes
WHERE indid IN (1, 0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY tamanho DESC;

Por que não usar mais essa abordagem

A sysindexes está marcada como deprecated desde o SQL Server 2005 e é mantida apenas por compatibilidade com versões antigas. A Microsoft recomenda não usá-la em bancos novos, já que ela pode ser removida em versões futuras e não reflete corretamente estruturas mais modernas, como tabelas particionadas. Se você está em uma versão atual do SQL Server, vale a pena migrar essas consultas para sys.partitions e sys.allocation_units.

Conclusão

Tanto para levantar o total de linhas quanto o espaço ocupado por cada tabela, o SQL Server já guarda essa informação pronta em suas views de sistema, sem necessidade de COUNT(*). Em versões atuais, prefira sys.tables, sys.indexes, sys.partitions e sys.allocation_units; use sysindexes apenas se estiver preso a uma versão bem antiga, sabendo que se trata de uma funcionalidade legada.

Posts Similares