You have an Azure Synapse Analytics dedicated SQL pool named SA1 that contains a table named Table1.
You need to identify tables that have a high percentage of deleted rows.
What should you run?
You have an Azure Synapse Analytics dedicated SQL pool named SA1 that contains a table named Table1.
You need to identify tables that have a high percentage of deleted rows.
What should you run?
To identify tables with a high percentage of deleted rows in a dedicated SQL pool in Azure Synapse Analytics, you should use sys.dm_db_column_store_row_group_physical_stats. This dynamic management view provides detailed information about the physical characteristics of row groups in columnstore indexes, including the number of deleted rows. By analyzing this data, you can calculate the ratio of deleted rows to total rows for each table, which helps in identifying tables with a high percentage of deleted rows.
has a column for the total number of rows physically stored (including those marked as deleted) and a column for the number of rows marked as deleted. Use sys.pdw_nodes_column_store_row_groups to determine which row groups have a high percentage of deleted rows and should be rebuilt
C is the correct Answer !
C is the correct Answer !
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-pdw-nodes-column-store-row-groups-transact-sql?view=aps-pdw-2016-au7
D is corrct
change to C https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-pdw-nodes-column-store-row-groups-transact-sql?view=aps-pdw-2016-au7
Use sys.pdw_nodes_column_store_row_groups to determine which row groups have a high percentage of deleted rows and should be rebuilt. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-pdw-nodes-column-store-row-groups-transact-sql?view=aps-pdw-2016-au7
ChatGPT 4o code: WITH RowGroupStats AS ( SELECT OBJECT_NAME(t.object_id) AS TableName, t.name AS IndexName, p.partition_number AS PartitionNumber, rg.row_group_id AS RowGroupID, rg.total_rows, rg.deleted_rows, rg.deleted_rows * 1.0 / NULLIF(rg.total_rows, 0) AS DeletedRowPercentage FROM sys.dm_db_column_store_row_group_physical_stats AS rg JOIN sys.partitions AS p ON rg.partition_id = p.partition_id JOIN sys.indexes AS t ON p.object_id = t.object_id AND p.index_id = t.index_id ) SELECT TableName, IndexName, PartitionNumber, AVG(DeletedRowPercentage) AS AvgDeletedRowPercentage FROM RowGroupStats GROUP BY TableName, IndexName, PartitionNumber HAVING AVG(DeletedRowPercentage) > 0.1 -- Adjust this threshold as needed ORDER BY AvgDeletedRowPercentage DESC;
The system views starting with "sys.dm_db_" are specific to SQL Server and provide information about the database and server activities, while the system views starting with "sys.pdw_nodes_" are specific to Azure Synapse and provide information about the distribution and performance in the Parallel Data Warehouse distributed storage environment. According to that, C is correct.
Sorry, D is Correct !
The sys.dm_db_column_store_row_group_physical_stats dynamic management view provides information about the physical characteristics of row groups in columnstore indexes, including the number of deleted rows in each row group. You can use this view to identify tables that have a high percentage of deleted rows by calculating the ratio of deleted rows to total rows for each table. -> D is the answer
D is correct answer