Getting Azure Synapse Analitics Tables Size - Query on documentation does not return correct data

F N 20 Reputation points
2023-03-20T14:37:18.55+00:00

Hello,
I've a Synapase Workspace with a Sql Pool that has a SQL Server version 12.0.2000 .
My requirement is to get the phisical space storage for each table in my database.
Many peoples pointed me at https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview#table-size-queries

But the query, when I do the sum in GB of all, it doesn't match with the total size shown from Azure portal or SSMS-> Click Database Properties -> See Total size in GB.

The difference is around 80 - 100 GB, so if my sql pool is 200 GB as total size, the data returned from these queries doesn't match and the sum is like around 112 GB.

Can someone help or tell me where to open a ticket ?

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,252 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 32,716 Reputation points MVP
    2023-03-21T14:28:35.8433333+00:00

    Probably there are other system internal objects consuming the rest of the space. Remove the following part on the view:

    AND t.is_ms_shipped = 0

    0 comments No comments

  2. GeethaThatipatri-MSFT 26,537 Reputation points Microsoft Employee
    2023-04-05T21:13:46.3266667+00:00

    @F N This is a difference between how the view in this document and how SMO look at the dedicated SQL pool. They have different intentions. As these calculations have existed in their current state for years, we may not change them in the interest of stability and expected outcomes, but the product group will consider these outcomes for future investment. Thanks again for submitting your feedback in github . Your suggestions help improve our documentation.

    0 comments No comments