Forum Discussion

madinugraha's avatar
madinugraha
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago
Solved

Size table in workspace

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 c...
workspaces
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    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's avatar
madinugraha
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago

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

NandanHegde's avatar
NandanHegde
Icon for Super User rankSuper User
1 year ago

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