SQL: Index and Table Sizes
Author: jason
Date: 2014-01-22 10:55:54
Category: Musing

It is often very helpful to know what size a particular index or table is. The below two scripts help shed light on the mystery of where all the space is going.

Indexes




Code

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)',
(8 * SUM(a.used_pages))/1024/1024 AS 'Indexsize(GB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY SUM(a.used_pages) desc, OBJECT_NAME(i.OBJECT_ID),i.index_id



Tables




Code

SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
t.schema_id,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB,
(SUM(a.used_pages) * 8)/1024/1024 AS UsedSpaceGB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
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
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY t.Name, t.schema_id, p.Rows ORDER BY SUM(a.used_pages) DESC




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009