1

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.

1
  • Hard to say based on information you've provided. Try to create a simper repro, perhaps with just a few partitions and post it in your question. – David Browne - Microsoft Jun 11 at 17:59
0

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.

3
  • I was one of the two folks who voted to close, after I discovered dba community. I was just hoping someone else will also come along and vote to move it so I don't have to close it here and post a new one over there. – Kashyap Jun 3 at 21:57
  • I tested with query you posted as well, same result (0 rows). OBJECT_ID() does work and provides the object_id of table. I checked by doing print(OBJECT_ID('mytable')) and the same thing does work when querying sys.partitions – Kashyap Jun 3 at 21:59
  • I have some partitioned tables that return results and some that don't, so I'm not surprised (although I don't understand it). – Joel Cochran Jun 4 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.