Get Table and Index storage size in SQL Server

sql

performance-tuning

storage-management

database-optimization

byNikita Barsukov·Dec 21, 2024

To quickly spot table and index storage sizes in SQL Server, we use a stored procedure called sp_spaceused. For each table, execute it individually and it will fetch detailed stats on space usage:

EXEC sp_spaceused 'TableName'; -- Replace 'TableName' with your table name. No table feelings will be hurt.

Now, if you want to automate this for all tables, we insert the results into a temporary table. We'll use a stored procedure named sp_MSforeachtable to gather stats for all tables in a single run:

CREATE TABLE #SpaceSizes (name NVARCHAR(128), rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18));

INSERT INTO #SpaceSizes EXEC sp_MSforeachtable 'EXEC sp_spaceused "?"'; -- "?" is all table names' secret code name. Shh...

SELECT * FROM #SpaceSizes;

DROP TABLE #SpaceSizes; -- After the party, we clean up. Bye bye, temp table.

This script collects size data for all tables and indexes, shows the compilation, and cleans up afterwards.

Delve deeper with dynamic management views

For a finer understanding of storage usage, we can use SQL Server's dynamic management views (DMVs). They provide insights into data distribution, index usage, and storage efficiency.

Using dynamic management views to gather space usage

SELECT 
    t.NAME AS TableName,
    SUM(s.used_page_count) * 8 AS UsedSpaceKB, -- UsedSpace in KB because we like to watch big numbers.
    (SUM(s.used_page_count) * 8) / 1024.0 AS UsedSpaceMB -- UsedSpace in MB. Less impressive than KB, but easier on the eyes.
FROM 
    sys.dm_db_partition_stats s
JOIN 
    sys.tables t ON s.object_id = t.object_id
WHERE 
    s.index_id IN (0, 1) -- We care only about Heap and Clustered Indexes here. Others can take a coffee break.
GROUP BY 
    t.NAME
ORDER BY 
    UsedSpaceMB DESC; -- Largest at the top, because the big ones always want the leading role.

This query reveals the used storage by heaps and clustered indexes.

Special note for Non-clustered indexes

Non-clustered indexes, which also consume space, require separate computation:

SELECT 
    t.NAME AS TableName,
    i.NAME AS IndexName,
    SUM(s.used_page_count) * 8 AS IndexSpaceKB, -- IndexSpace in KB because... see previous reasons.
    (SUM(s.used_page_count) * 8) / 1024.0 AS IndexSpaceMB -- IndexSpace in MB for those who prefer lighter reading.
FROM 
    sys.dm_db_partition_stats s
JOIN 
    sys.tables t ON s.object_id = t.object_id
JOIN 
    sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE 
    s.index_id > 1 -- Only Non-Clustered Indexes are invited to this party.
GROUP BY 
    t.NAME, i.NAME
ORDER BY 
    IndexSpaceMB DESC; -- The more space you take, the higher you are! (Unfair, right?)

Understanding the space used by non-clustered indexes assists in performance tuning and storage management.

Essential considerations when measuring space

Unused space: Factor in space allocated but not currently in use. Extract this from dynamic management view sys.dm_db_partition_stats.
Internal objects: Be mindful of internal tables that SQL Server uses. These include those used for XML storage or spatial indexes.
Table partitioning: If your tables are partitioned, your query will need an upgrade to accurately total the sizes across partitions.
Database snapshots: Working with snapshots? Remember, size computations only reflect the changes made since the snapshot was taken.