Identifying Data Skew in Azure Synapse Analytics Dedicated SQL Pool

To identify data skew in Table1 within Pool1, the correct approach is to connect to Pool1 and run DBCC PDW_SHOWSPACEUSED.

Why Option A is Correct

DBCC PDW_SHOWSPACEUSED is specifically designed for Azure Synapse Analytics dedicated SQL pools (formerly SQL Data Warehouse) to analyze data distribution across the 60 distributions that make up the MPP (Massively Parallel Processing) architecture. This command provides detailed information about:

• Data distribution across all distributions
• Space usage per distribution
• Row counts per distribution
• Identification of skewed distributions where data is unevenly distributed

When executed against Table1, this command reveals which distributions contain disproportionately large amounts of data, directly indicating the extent of data skew.

Analysis of Other Options

Option B (Connect to built-in pool and run DBCC PDW_SHOWSPACEUSED): This is incorrect because Azure Synapse Analytics dedicated SQL pools don't have a "built-in pool" concept for running diagnostic commands. The built-in pool refers to the serverless SQL pool, which cannot run DBCC commands against dedicated SQL pool objects.

Option C (Connect to Pool1 and run DBCC CHECKALLOC): This is incorrect because DBCC CHECKALLOC is primarily used for checking page allocation and database consistency in traditional SQL Server, not for identifying data skew in Azure Synapse Analytics' distributed architecture.

Option D (Connect to built-in pool and query sys.dm_pdw_sys_info): This is incorrect because sys.dm_pdw_sys_info provides system-level information about the entire dedicated SQL pool instance, not detailed data distribution metrics for specific tables. It doesn't offer the granular insight needed to identify data skew in individual tables.

Best Practice Considerations

Data skew is a critical performance consideration in Azure Synapse Analytics because uneven data distribution can lead to:

• Resource contention on overloaded distributions
• Poor query performance due to data movement operations
• Inefficient resource utilization

Running DBCC PDW_SHOWSPACEUSED directly against the target dedicated SQL pool provides the most accurate and actionable information for identifying and quantifying data skew issues.