Size table in workspace

[madinugraha]

Hi, One of my customer asking how to identify size of table in the workspace ID of Fabric.

They already tried to use powershell and onelake explorer, but haven't got any luck

Perhaps anyone can share how to identify the size of table?

thank you

[v-veshwara-msft]

Hi @madinugraha ,
Thanks for using Microsoft Fabric Community Forum to post your question.
To get the size of tables in Fabric Warehouse, the SQL query provided by @NandanHegde should work.
You can also modify the query to get the size in MB or KB.
If size needed for only a specific table, replace WHERE clause with the following:

WHERE

    t.is_ms_shipped = 0

    AND i.object_id > 255

    AND t.NAME = 'Table Name'

Hope it works. If any further assistance needed please reach out.
If this post helps please accept whichever solution met your query and a Kudos would be appreciated.

Best Regards,
Vinay.

[madinugraha]

Hi

My customer tried the query and it works to describe the table. But the question is that from the workspace size is almost 400GB (please find the ss as below), but when he do the query, he can see all of the size each table, but it doesn't representing the size of 400GB from the workspace. May I know how why it's not correlated?

[v-veshwara-msft · Accepted solution]

Hi @madinugraha ,
The difference between workspace size reported in the Microsoft Fabric Capacity Metrics app (around 400 GB) and the actual sizes of the warehouse tables you've queried is because:

The total workspace size includes more than just the raw data from warehouse tables. It includes all data assets within your workspace such as:

• Datasets

• Dataflows

• Reports

• Models

• Temporary files

• Caches

• Log files

• Metadata and indexes
  

Additionally, temporary data, intermediate storage, and version histories contribute to the overall workspace size.

Billable Storage vs. Current Storage:

You might have noticed terms like "current storage" and "billable storage" in the Fabric Capacity Metrics app:

• Billable Storage : Represents the storage being billed based on usage at the start of the reporting period. It may be lower if the storage usage was less at that start.

• Current Storage : Reflects real-time storage usage, including all current data assets, caches, and temporary files. This can be dynamic and change more frequently.

For detailed information please refer the following link:
metrics-app-storage-page 

I hope this clarifies the situation. Please let me know if you have any further questions!

If this post helps, please accept as solution to help others find easily.

Best regards,
Vinay.

[madinugraha]

thanks for the reference. But my customer is asking if the condition is fabric warehouse, is it different method?

[NandanHegde]

Below query should work :

SELECT

    t.NAME AS TableName,    

    p.rows AS RowCounts,

    CONVERT(DECIMAL,SUM(a.total_pages)) * 8 / 1024 / 1024 AS TotalSpaceGB,

    SUM(a.used_pages)  * 8 / 1024 / 1024 AS UsedSpaceGB ,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 / 1024 AS UnusedSpaceGB

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

LEFT OUTER JOIN

    sys.schemas s ON t.schema_id = s.schema_id

WHERE

     t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255

GROUP BY

    t.Name, s.Name, p.Rows

ORDER BY

    UsedSpaceGB DESC, t.Name

[NandanHegde]

Assuming this is the size of table in fabric lakehouse,

Plz refer the below link :

https://community.fabric.microsoft.com/t5/Fabric-platform/How-to-check-the-size-of-data-in-lakehouse...