I am trying to derive the table size in SQL DW and Initially we were using below SQL to get the size.
SELECT
two_part_name , SUM(row_count) as table_row_count , SUM(reserved_space_GB) as table_reserved_space_GB , SUM(data_space_GB) as table_data_space_GB , SUM(index_space_GB) as table_index_space_GB , SUM(unused_space_GB) as table_unused_space_GB FROM dbo.vTableSizes GROUP BY two_part_name ;
The output of this table for a particular table is two_part_name table_row_count table_reserved_space_GB table_data_space_GB table_index_space_GB table_unused_space_GB Dim_Inventory_Hierarchy 48024330 6.781504 6.77496 0 0.006544
Which is 6.7GB for 48M records
But while I queried the table size using below query
select sum(size_in_bytes)/1024/1024/1024 size_in_GB,sum(total_rows),sum(deleted_rows) FROM sys.pdw_nodes_column_store_row_groups rg JOIN sys.pdw_nodes_tables pt ON rg.object_id = pt.object_id AND rg.pdw_node_id = pt.pdw_node_id AND pt.distribution_id = rg.distribution_id JOIN sys.pdw_table_mappings tm ON pt.name = tm.physical_name INNER JOIN sys.tables t ON tm.object_id = t.object_id WHERE t.name = 'Dim_Inventory_Hierarchy'
For the same table, it is returning (~1GB )
size_in_GB (No column name) (No column name) 1 48024330 0
which is ~1GB for the same 48M records in pdw_nodes_column_store_row_groups table?
So - I am trying to understand, which one is accurate and needs to be considered for estimating the table size ?
Regards, Aravind
Have you considered using sp_spaceused?
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql