Answer: sys.pdw_nodes_column_store_row_groups

## 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.