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?
A.
sys.pdw_nodes_column_store_segments
B.
sys.dm_db_column_store_row_group_operational_stats
C.
sys.pdw_nodes_column_store_row_groups
D.
sys.dm_db_column_store_row_group_physical_stats
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
"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
D. sys.dm_db_column_store_row_group_physical_stats is most appropriate. This view provides information about the physical state of columnstore row groups, including the percentage of deleted rows.
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.
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
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
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
This section is not available anymore. Please use the main Exam Page.DP-203 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
greenlever
Highly Voted 2 years, 9 months agohypersam
Most Recent 5 months, 2 weeks agode_examtopics
7 months, 2 weeks agoe56bb91
1 year agoAlongi
1 year, 3 months agoAlongi
1 year, 3 months agod046bc0
1 year, 7 months agokkk5566
1 year, 10 months agokkk5566
1 year, 10 months agoandie123
1 year, 11 months agoandie123
1 year, 11 months agovctrhugo
2 years agodimbrici
2 years, 8 months agoanks84
2 years, 10 months ago