Ao trabalhar com bancos de dados do SQL Server, é frequentemente útil ter insights sobre os tamanhos das tabelas e índices. Essas informações podem ajudar a otimizar seu banco de dados e identificar possíveis gargalos de desempenho. Neste artigo, exploraremos uma consulta que fornece uma lista das 100 maiores tabelas por tamanho, incluindo o tamanho de seus índices.

Antes de mergulharmos na consulta, é importante observar que o SQL Server faz um ótimo trabalho em manter os metadados e as visualizações de catálogo atualizados. Essas visualizações fornecem informações precisas sobre os tamanhos das tabelas e contagens de linhas. No entanto, em casos raros, você pode querer corrigir manualmente quaisquer imprecisões executando o seguinte comando:

DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;

Tenha em mente que executar este comando em um banco de dados grande pode levar algum tempo para ser concluído, portanto, geralmente não é necessário executá-lo regularmente.

Agora, vamos dar uma olhada na consulta:

SELECT TOP 100
    s.Name AS nome_esquema,
    tbl.NAME AS nome_tabela,
    SUM(SUM(au.total_pages) * 16 / 1024) OVER(PARTITION BY ind.object_id) AS tamanho_total_tabela_mb,
    SUM(SUM(au.used_pages) * 16 / 1024) OVER(PARTITION BY ind.object_id) AS tamanho_total_usado_mb,
    ind.name AS nome_indice,
    ind.type_desc AS tipo_indice,
    INDEXPROPERTY(ind.object_id, ind.name, 'IsUnique') AS unico,
    (SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.object_id = ind.object_id AND ic.index_id = ind.index_id AND ic.is_included_column = 0) AS colunas_indexadas,
    (SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.object_id = ind.object_id AND ic.index_id = ind.index_id AND ic.is_included_column = 1) AS colunas_incluidas,
    SUM(p.rows) AS contagem_linhas_indice,
    SUM(au.total_pages) * 16 / 1024 AS tamanho_indice_mb,
    SUM(au.used_pages) * 16 / 1024 AS tamanho_usado_indice_mb,
    SUM(au.used_pages) * 16 / SUM(p.rows) AS tamanho_medio_linha_kb,
    (SUM(au.total_pages) - SUM(au.used_pages)) * 16 / 1024 AS espaco_nao_utilizado_mb,
    INDEXPROPERTY(ind.object_id, ind.name, 'IndexDepth') AS profundidade_indice,
    ind.index_id
FROM sys.tables tbl
INNER JOIN sys.indexes ind ON tbl.OBJECT_ID = ind.OBJECT_ID
INNER JOIN sys.partitions p ON ind.OBJECT_ID = p.OBJECT_ID AND ind.index_id = p.index_id
INNER JOIN sys.allocation_units au ON p.PARTITION_ID = au.container_id
INNER JOIN sys.schemas s ON tbl.SCHEMA_ID = s.SCHEMA_ID
-- WHERE tbl.NAME = ''
GROUP BY s.Name, ind.object_id, ind.index_id, tbl.Name, ind.name, ind.type_desc
HAVING SUM(p.rows) > 0
ORDER BY tamanho_total_usado_mb DESC;

Esta consulta recupera as 100 maiores tabelas por tamanho, ordenadas da maior para a menor. Ela fornece informações como o nome do esquema, nome da tabela, tamanho total da tabela em megabytes, espaço total usado em megabytes, nome do índice, tipo de índice, unicidade, número de colunas indexadas, número de colunas incluídas, contagem de linhas, tamanho do índice em megabytes, tamanho usado do índice em megabytes, tamanho médio da linha em kilobytes, espaço não utilizado em megabytes, profundidade do índice e ID do índice.

Você pode descomentar a cláusula WHERE e especificar um esquema e nome de tabela específicos para restringir os resultados a uma tabela específica.

Ao executar esta consulta, você pode obter informações valiosas sobre os tamanhos de suas tabelas e índices, permitindo que você tome decisões informadas sobre a otimização do banco de dados e ajuste de desempenho.

Lembre-se, é importante monitorar e analisar regularmente seu banco de dados para garantir um desempenho ideal. Compreender os tamanhos de suas tabelas e índices é apenas uma peça do quebra-cabeça.

Boas consultas!