Analysis of System Views for Identifying Deleted Rows in Azure Synapse Analytics

Context and Requirements

This question involves identifying tables with a high percentage of deleted rows in an Azure Synapse Analytics dedicated SQL pool. The key distinction here is that we're working with Azure Synapse Analytics (formerly SQL Data Warehouse), which uses a massively parallel processing (MPP) architecture with distributed data across multiple compute nodes.

Evaluation of Options

Option C: sys.pdw_nodes_column_store_row_groups ✅ CORRECT

• This is the optimal choice for Azure Synapse Analytics dedicated SQL pools
• Provides detailed row group information across all distributions (pdw_nodes)
• Contains columns specifically for tracking deleted rows:
  • total_rows: Total number of rows in the row group
  • deleted_rows: Number of rows marked as deleted
  • state_description: Current state of the row group
• Enables calculation of deletion percentage: (deleted_rows * 100.0 / total_rows)
• Designed specifically for the distributed architecture of Azure Synapse Analytics

Option D: sys.dm_db_column_store_row_group_physical_stats ❌ INCORRECT

• This DMV is designed for traditional SQL Server, not Azure Synapse Analytics
• Does not account for the distributed nature of Synapse Analytics
• May not provide accurate results across all compute nodes
• Missing the distributed context required for MPP systems

Option A: sys.pdw_nodes_column_store_segments ❌ INCORRECT

• Focuses on column store segments rather than row groups
• Does not provide direct information about deleted rows
• More granular than needed for this specific requirement

Option B: sys.dm_db_column_store_row_group_operational_stats ❌ INCORRECT

• Designed for operational statistics in traditional SQL Server
• Focuses on performance metrics rather than physical row deletion tracking
• Not suitable for Azure Synapse Analytics dedicated SQL pools

Technical Rationale

Azure Synapse Analytics dedicated SQL pools use a distributed architecture where data is partitioned across multiple compute nodes. The sys.pdw_nodes_* system views are specifically designed to provide visibility into this distributed environment. The sys.pdw_nodes_column_store_row_groups view gives you the necessary information across all distributions to accurately calculate deletion percentages and identify tables that would benefit from index maintenance operations like ALTER INDEX REORGANIZE or ALTER INDEX REBUILD.

Best Practice Recommendation

When working with columnstore indexes in Azure Synapse Analytics, regularly monitoring row group health using sys.pdw_nodes_column_store_row_groups is a recommended practice. High deletion percentages (typically > 20%) indicate that row groups should be rebuilt to maintain optimal query performance and storage efficiency.