Dedicated SQL Pool In Azure - Cannot use System Views to calculate size for tables

Question

I know that it's a repeated question but I've tried all solution and workaround suggested from every possible question present on stackoverflow.com

I have inherited a dedicated SQL Pool with a SQL Server version 2012.0.2000.8 on which there are several tables.
We're looking to understand how much retention should we set and to do that we're analyzing the size of every table.

I've tried

• Show data and disk use breakdown by table
• space usage on sys.allocation_units and sp_spaceused
• Generating report by clicking on the right of database, but I don't have that option in SSMS

Tried to write this query

SELECT 
  OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id) AS TableName,    
  SUM(p.rows) AS RowCounts,
  CONVERT(DECIMAL,SUM(p.reserved_page_count)) * 8 / 1024 AS TotalSpaceMB, 
  CONVERT(DECIMAL,SUM(p.used_page_count)) * 8 / 1024 AS UsedSpaceMB , 
  CONVERT(DECIMAL,SUM(p.reserved_page_count - p.used_page_count)) * 8 / 1024 AS 
  UnusedSpaceMB
FROM 
   sys.partitions p
WHERE 
 p.index_id <= 1
GROUP BY 
 p.object_id
ORDER BY 
 UsedSpaceMB DESC, TableName;

or this one

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.NAME = 'mytable'
  AND t.is_ms_shipped = 0
  AND i.OBJECT_ID > 255 
GROUP BY 
  t.Name, s.Name, p.Rows
ORDER BY 
  UsedSpaceGB DESC, t.Name

Everytime there is a use of Sys Views (sys.schemas / sys.partition / sys.allocation_units etc. etc.) it says
Catalog view 'X' is not supported in this version.

I think that mostly there could be a problem with some permission since I cannot use system views and they should be present in my version of SQL Pool.
So, my question is,

how I can fix using system views ?

Answer 1

When using the DMVs, you should always lookup their docs which list at the top the Microsoft products that support those DMVs. For example, your second query uses sys.allocation_units which is not supported in Azure Synapse Analytics.

According to Microsoft employee David Browne in this help post, this query (which uses sys.dm_pdw_nodes_db_partition_stats*) can be used to fetch what is considered the size of the database in Azure Synapse Analytics:

select sum(used_page_count)  / 8.0 / 1024 total_size_mb
from sys.dm_pdw_nodes_db_partition_stats

Running that DMV wide open will give you the size breakdown per partition which includes every table and index, like so:

SELECT 
    partition_id, 
    OBJECT_NAME(object_id) AS ObjectName,
    object_id,
    index_id, 
    used_page_count  / 8.0 / 1024 AS total_size_mb
FROM sys.dm_pdw_nodes_db_partition_stats

You should then be able to group it by object_id to essentially get the total size of each table object:

SELECT 
    object_id,
    OBJECT_NAME(object_id) AS ObjectName, 
    SUM(used_page_count)  / 8.0 / 1024 AS total_size_mb
FROM sys.dm_pdw_nodes_db_partition_stats
GROUP BY 
    object_id,
    OBJECT_NAME(object_id)

---

_{*Do note that sys.dm_pdw_nodes_db_partition_stats is the Azure Synapse Analytics version of the sys.dm_db_partition_stats DMV, as mentioned in the docs.}