Disk consumption is something that DBAs frequently investigate. It’s a process that checks the database’s growth and disk space use. It is often necessary to filter it down to see which table is eating the most disk space. There are numerous ways to determine the number of rows, data space, and index space used, including querying the system tables, using the built-in function, or using the built-in reports in SSMS.
Method-1:
Right-click the database and select Reports, then Standard Reports, then Disk Usage or Disk Usage by Top Tables.
This report provides detailed data on the utilization of disk space by the top 1000 tables within the database. However, the report doesn’t provide data for memory-optimized tables.
Method-2:
sp_spaceused ‘Person.Person’
GO
sp_spaceused ‘Sales.SalesOrderDetail’
GO
The function sp_spaceUsed returns the table name, number of rows, total reserved space for it, disk space utilized, disk space consumed by the index, and unused space allocation for that table. Unless you call this function in a loop, it will only return disk utilization information for the specified table.
Method-3:
SELECT
t.NAME AS TableName,
i.name AS indexName,
SUM(p.rows) AS RowCounts,
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.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
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
OBJECT_NAME(i.object_id)
I always prefer to run this statement since it provides me with a complete picture of the disk consumption of the table. Total number of rows in each table, total number of pages assigned to each table, total number of pages utilized by the table, total number of data pages, total space in megabytes, total space used in megabytes, and so on. It is not confined to a single table, as stated.
Hope you find this article helpful.
Happy learning!!
Big Data and SQL 