Exam DP-203 topic 4 question 8 discussion

The right answer is D, I tested it in Synapse and it's the only one that actually runs without an error

-- Find data skew for a distributed table DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

dm_pdw_nodes_db_partition_stats because we need to verify it on Pool1 (not built-in pool!)

dm_pdw_nodes_db_partition_stats

Use sys.dm_pdw_nodes_db_partition_stats to analyze any skewness in the data.

Correct answer is D. A quick way to check for data skew is to use DBCC PDW_SHOWSPACEUSED, but DBCC PDW_SHOWSPACEUSED is not supported by serverless SQL pool in Azure Synapse Analytics. So A option can't be performed. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute The only correct option here is to check sys.dm_pdw_nodes_db_partition_stats using dedicated SQL pool. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/cheat-sheet

A quick way to check for data skew is to use DBCC PDW_SHOWSPACEUSED. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

This has been explained by others, but not clear enough to get it. I certainly had to look around and ponder for a bit. So, to give a more lucid explanation for why this is D and why the later question is DBCC PDW_SHOWSPACEUSED , it comes down to the small differences. You can use DBCC PDW_SHOWSPACEUSED to find the skew, however only on dedicated pools. Well if you are like me, you would be shouting WELLL THE QUESTION SAID DEDICATED POOL DUH. But if you read it carefully, it says connect to the "built-in pool" AKA serverless pool and run DBCC PDW_SHOWSPACEUSED. Well, we ain't in a serverless pool are we? so that leaves D as the solution. in the other question the given answers are so A. Connect to Pool1 and run DBCC PDW_SHOWSPACEUSED. B. Connect to the built-in pool and run DBCC PDW_SHOWSPACEUSED. C. Connect to Pool1 and run DBCC CHECKALLOC. D. Connect to the built-in pool and query sys.dm_pdw_sys_info. Here we see that db_partition_stats is in a built in, which is a no go, so obviously we use PDW_SHOWSPACEUSED. Hopefully this help any airheaded kindred spirits.

A is a quicker way, but you can run DBCC in a serverless SQL pool, the built-in pool.

Right answer is A. DBCC PDW_SHOWSPACEUSED. google it

D is correct

I think that first we need to connect to Pool 1, this excludes the first two options (and especially DBCC PDW_SHOWSPACEUSED). In the other two options, after connecting to Pool1, we execute query sys.dm_pdw_nodes_db_partition_stats.

For dedicated SQL Pool this is the correct answer. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/cheat-sheet

Use sys.dm_pdw_nodes_db_partition_stats to analyze any skewness in the data. ref: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/cheat-sheet

DBCC PDW_SHOWSPACEUSED

Firstly, this is for DEDICATED SQL Pool. Here is what both likely outputs give you: sys.dm_pdw_nodes_db_partition_stats: object_id, partition_id, in_row_data_page_count, in_row_used_page_count These columns are not useful in identifying skew However, if you're using PDW_SHOWSPACEUSED: ROWS, RESERVED_SPACE, DATA_SPACE, INDEX_SPACE, UNUSED_SPACE These columns are definitely useful in identifying skew as you can calculate the Space allocation per row and look at any unused space

A does not work as in this answer we connect to the build in pool NOT the dedicated pool. This leaves D as valid option