There are multiple way of finding size of a table and its indexes in a database. First and foremost, lets understand that table size contains multiple components – Size of data, size of index and unused space. If we add all of them, we could derive the value of reserved size, which is consumed by a table at disk.
The easiest way to find size of a Table is from the UI(User Interface) at SSMS(SQL Server Management Studio). Right click on desired table and get into Properties and click on the Storage tab of the same and you would see something like below:
As you can see here – Data space is 925.602 MB and Index space is 582.648 MB. So, the table size on disk will be approximately 1508 MB (Approximately because reserved space is not considered).
You may also find size of table running below command :
sp_spaceused ‘schema_name.table_name’
Find the screenshot of result for above command:
Alternatively, you may execute below command to get size of individual indexes in Tables:
SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
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 AS Indexsize_MB
FROM sys.indexes i
JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
JOIN sys.objects o on i.object_id = o.object_id
WHERE is_ms_shipped = 0
GROUP BY i.OBJECT_ID, i.index_id, i.[name]
ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id
Also note that, if the table is having clustered index defined as a Primary key then, size of clustered index is nothing but the size of data in a table however if it is heap then, it is the table size itself of Index id 0.
You may also get these details by running sp_BlitzIndex provided by Brent Ozar and command would be as follows:
sp_BlitzIndex @SchemaName = ‘dbo’, @TableName = ‘Users’
Please leave your comment if you liked this post or have any feedback.