Getting insight into partitions of a table in Azure Synapse (similar to SQL Server)

Question

I'm using Dedicated SQL-Pools AKA Azure Synapse (which is different from serverless/on-demand Synapse that comes with Azure Synapse Analytics). And According to Azure:

While the syntax of partitioning may be slightly different from SQL Server, the basic concepts are the same.

• I have a table that receives ~33 million new rows per day.
• Column named version indicates the day on which that row arrived. E.g. all 33 million rows arriving on 2021-05-22 have same version value (2021-05-22).
• Currently it has ~300 million rows (9 days worth of data).
• Table has daily partitions.

Here is the order of things I did:

• create table with 345 daily partitions (from 22-May-2021 to 30-Apr-2022).
• insert 9 days (33 million x 9 = ~300 million rows) worth of data into this table. I used INSERT INTO mytable SELECT * FROM some_other_table_with_300_million_rows)
• update statistics mytable

Question

• Expectation: I have 9 partitions with ~33 million rows and 336 partitions with 0 rows.
• Reality: All partitions have equal amount of data.

When I run this SQL statement:

SELECT 
    partition_id, index_id, partition_number, rows, data_compression_desc 
FROM 
    sys.partitions 
WHERE 
    object_id = OBJECT_ID('mytable')

I get this result:

partition_id        index_id    partition_number    rows    data_compression_desc
72057597508452352   1           1                   868784  COLUMNSTORE
72057597508583424   1           2                   868784  COLUMNSTORE
......
72057597553410048   1           344                 868784  COLUMNSTORE
72057597553541120   1           345                 868812  COLUMNSTORE

868784 * 345 = ~300 million

This SQL statement however:

SELECT * 
FROM sys.dm_pdw_nodes_db_partition_stats 
WHERE object_id = OBJECT_ID('mytable')

returns no rows.

Answer 1

Looks like your question may get closed - you didn't clearly ask a question, but I think this is what you are after.

The "object_id" in your query is expecting a id value of the table, not the table name. [I misread it originally, and I assume that is what the OBJECT_ID function is for. Still, there is no guarantee this table has entries for the table you are referring to]. I would instead join to sys.tables and sys.schemas to get the value:

SELECT TOP(100) s.name AS [SCHEMA_NAME], t.name AS TABLE_NAME, st.*
FROM sys.tables t
        join sys.schemas s
            on s.[schema_id] = t.[schema_id]
        join sys.dm_pdw_nodes_db_partition_stats st
            on st.object_id = t.object_id
WHERE s.name = '{yourschema}'
AND t.name = '{yourtable}'

For what it's worth, I've found the sys tables referencing Partitions to be extremely unreliable. I have Partitioned tables with lots of skew, but the system queries show them all with the exact same number of rows even though the distributions are NOT ROUND_ROBIN. Honestly, I just ignore this data. As long as you are using the partitioned columns in your WHERE clause, you should get the benefit regardless.