Forum Discussion

madinugraha's avatar
madinugraha
Microsoft 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 can share how to identify the size of table?

 

thank you

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.

     

6 Replies

  • madinugraha's avatar
    madinugraha
    Microsoft 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
      Super 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
  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

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

      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's avatar
        v-veshwara-msft
        Community Support
        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.