Ao trabalhar com o SQL Server, é importante ter um bom entendimento do tamanho das suas tabelas. Saber o tamanho das suas tabelas pode ajudar a otimizar o seu banco de dados e melhorar o desempenho. Neste artigo, discutiremos um script que pode ser usado para calcular o tamanho das suas tabelas no SQL Server.

O script fornecido abaixo calcula o tamanho de cada tabela em um banco de dados, incluindo o número de linhas, tamanho de página reservado, tamanho de dados, tamanho de índice e espaço não utilizado. Ele também fornece o tamanho total do banco de dados e do arquivo de log.

DECLARE @dbsize DECIMAL(19,2),
        @logsize DECIMAL(19,2)

SET NOCOUNT ON

/*
**  Dados de resumo.
*/
BEGIN
        SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN size ELSE 0 END)) * 8/1024,
                @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN size ELSE 0 END)) * 8/1024
        FROM sys.database_files
END

;WITH FirstPass AS (
        SELECT object_id,
                ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
                UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
                PageCnt = SUM(
                CONVERT(DECIMAL(19,2),CASE
                        WHEN (index_id < 2)
                                THEN (used_page_count)
                        ELSE lob_used_page_count + row_overflow_used_page_count
                        END
                )) * 8/1024,
                RowCnt = SUM(
                CASE
                        WHEN (index_id < 2)
                                THEN row_count
                        ELSE 0
                END
                )
        FROM sys.dm_db_partition_stats
        GROUP BY object_id
)
,InternalTables AS (
        SELECT ps.object_id,
                ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
                UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
        FROM sys.dm_db_partition_stats  ps
                INNER Join sys.internal_tables it
                        ON it.object_id = ps.object_id
                        And it.internal_type IN (202,204,211,212,213,214,215,216)
        WHERE it.parent_id = ps.object_id
        GROUP BY ps.object_id
)
,Summary AS (
        SELECT
                ObjName = OBJECT_NAME (F.object_id),
                NumRows = MAX(F.RowCnt),
                ReservedPageMB = SUM(ISNULL(F.ReservedPage,0) + ISNULL(i.ReservedPage,0)),
                DataSizeMB = SUM(F.PageCnt),
                IndexSizeMB = SUM(CASE WHEN (F.UsedPage + ISNULL(i.UsedPage,0)) > F.PageCnt
                                                THEN ((F.UsedPage + ISNULL(i.UsedPage,0)) - F.PageCnt) ELSE 0 END),
                UnusedSpace = SUM(CASE WHEN (F.ReservedPage + ISNULL(i.ReservedPage,0)) > (F.UsedPage + ISNULL(i.UsedPage,0))
                        THEN ((F.ReservedPage + ISNULL(i.ReservedPage,0)) - (F.UsedPage + ISNULL(i.UsedPage,0))) ELSE 0 END),
                dbsizeMB = @dbsize,
                LogSizeMB = @logsize
        FROM FirstPass F
                LEFT Outer Join InternalTables i
                ON i.object_id = F.object_id
        GROUP BY F.object_id
),TotalUnused AS (
SELECT SUM(UnusedSpace) AS UnusedSpace
FROM Summary
)
SELECT ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, S.UnusedSpace, dbsizeMB, LogSizeMB,
        dbsizeMB - TU.UnusedSpace AS TotalDataFreeSpace,
        PercentofDBPhysFile = ((IndexSizeMB + DataSizeMB) / @dbsize) * 100,
        PercentofDBUsedSpace = ((IndexSizeMB + DataSizeMB) / (@dbsize - TU.UnusedSpace)) * 100
FROM Summary S
CROSS APPLY TotalUnused TU
ORDER BY PercentofDBUsedSpace DESC

Este script calcula o tamanho de cada tabela consultando as visualizações e funções do sistema no SQL Server. Ele usa a visualização sys.dm_db_partition_stats para obter informações sobre o número de linhas e contagem de páginas para cada tabela. Ele também usa a visualização sys.database_files para obter o tamanho do banco de dados e do arquivo de log.

O script então calcula o tamanho de página reservado, tamanho de página usado, tamanho de dados, tamanho de índice e espaço não utilizado para cada tabela. Ele também calcula o tamanho total do banco de dados e do arquivo de log.

Ao executar este script, você pode obter uma melhor compreensão do tamanho das suas tabelas e identificar quaisquer tabelas que possam estar ocupando uma quantidade significativa de espaço. Essas informações podem ajudar a otimizar o seu banco de dados e melhorar o desempenho.

Vale ressaltar que este script é apenas uma maneira de calcular o tamanho das suas tabelas no SQL Server. Existem outros scripts e ferramentas disponíveis que podem fornecer informações semelhantes. É sempre uma boa ideia explorar diferentes opções e escolher aquela que melhor atenda às suas necessidades.

Isso é tudo por agora! Fique ligado para mais dicas e truques do SQL Server.