Analyzing SQL Server Object Space Usage with sys.dm_db_partition_stats
When working with large databases in SQL Server, understanding how space is utilized by different objects—such as tables and indexes—is crucial for performance tuning and storage planning. One of the most useful tools for this purpose is the system dynamic management view sys.dm_db_partition_stats.
What is sys.dm_db_partition_stats?
The sys.dm_db_partition_stats view provides detailed information about row and page counts for each partition in the current database. This data is essential for determining how much space each object consumes and for differentiating between table data and index data.
You can refer to the official Microsoft documentation here:
🔗 sys.dm_db_partition_stats (Transact-SQL)
Query to List SQL Server Objects with Space Usage
The following SQL query helps you list all user-defined database objects along with the space they use. It breaks down the space into total space used, space used by the table data, and space used by nonclustered indexes.
-- Retrieve space usage details for user-defined objects in the current database
SELECT
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id) AS [Name],
CONVERT(decimal(18,2), SUM(reserved_page_count) * 8 / 1024.0) AS Total_space_used_MB,
CONVERT(decimal(18,2), SUM(CASE WHEN index_id < 2 THEN reserved_page_count ELSE 0 END) * 8 / 1024.0) AS Table_space_used_MB,
CONVERT(decimal(18,2), SUM(CASE WHEN index_id > 1 THEN reserved_page_count ELSE 0 END) * 8 / 1024.0) AS Nonclustered_index_space_used_MB,
MAX(row_count) AS Row_count
FROM
sys.dm_db_partition_stats AS p
INNER JOIN
sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
GROUP BY
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id)
ORDER BY
Total_space_used_MB DESC;
How This Helps
- Total_space_used_MB: Gives a combined view of how much space each object is using in megabytes.
- Table_space_used_MB: Shows how much of that space is allocated to table data (clustered index or heap).
- Nonclustered_index_space_used_MB: Reflects how much space is used by nonclustered indexes.
- Row_count: Indicates the number of rows in the object, which helps contextualize the space usage.
This query is particularly helpful when you're diagnosing storage issues or planning to optimize your indexes or database schema.
