synapse dedicated pool database size

Question

What is the right way to find the database size, table's size, and other objects, the db size that is being billed by azure? Also where can be find the space occupied by the external tables(meta data)?

Answer 1

The documented DMV queries allow you to get a report of all table sizes. similar thread :https://stackoverflow.com/questions/63620317/query-the-size-of-all-tables-in-synapse-analytics Note : the external tables do not physically store the data but refernce it from other sources, so ideally there is no size associated with them within db

Answer 2

Hi Vinodh247,

<In additon to Nandan's answer>

Synapse dedicated SQL Pool DB storage is an independent component. DB can grow up to 240TB(This is the max limit for DB storage).

Autogrowth is enabled by default on the azure SQL pools. When your data size grows, the DB size automatically increases up to max 240TB.

Dedicated SQL pool leverages Azure Storage. Since your data is stored and managed by Azure Storage, there is a separate charge for your storage consumption.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-service-capacity-limits#workload-management

<copied from the documention page>

Dedicated SQL pool size:

Gen1: 240 TB compressed on disk. This space is independent of tempdb or log space, and therefore this space is dedicated to permanent tables. Clustered columnstore compression is estimated at 5X. This compression allows the database to grow to approximately 1 PB when all tables are clustered columnstore (the default table type).

Gen2:

Unlimited storage for columnstore tables. Rowstore portion of the database is still limited to 240 TB compressed on disk.

https://learn.microsoft.com/en-us/answers/questions/928940/database-storage-space