LLI Wiki — My Digital Knowledge Base

Size of tables and indexes per object

Query listing the total, used and data size of every index and heap in the current database, from the allocation units.

This query reports the size of each index, or heap, of the current database, from the allocation units. Run it in the database you want to measure: for example, in msdb to see the largest objects of the SQL Server Agent database.

SELECT object_name(i.object_id) AS objectName,
       i.[name] AS indexName,
       SUM(a.total_pages) AS totalPages,
       SUM(a.used_pages) AS usedPages,
       SUM(a.data_pages) AS dataPages,
       (SUM(a.total_pages) * 8) / 1024 AS totalSpaceMB,
       (SUM(a.used_pages) * 8) / 1024 AS usedSpaceMB,
       (SUM(a.data_pages) * 8) / 1024 AS dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY SUM(a.total_pages) DESC, object_name(i.object_id)
GO

Notes

Mastodon LLIMastodon SocialFosstodon