Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
How can I fix performance not improving when querying a partitioned table?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(179.20000000000002, 93%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEA%3C/text%3E%3C/svg%3E)
Efstathiadou Androniki
5
Reputation points
Hello,
I need some help with partitioned tables in Dedicated SQL Pool.
Here are some details: Pool performance level is Gen2: DW300c. I have a large (67 columns) distributed Hash table with CLUSTERED_COLUMNSTORE index. The distribution column has been chosen according to design considerations and there is no data skew (Skewed Table Percentage: 0.18822). The table size is 214,680,458 rows and the data per year is as follows:
PYear NoRows
2016 8,928
2017 20,890,081
2018 103,877
2019 141,308
2020 14,560,292
2021 58,548,703
2022 75,665,750
2023 44,080,789
I have created a new Partitioned table with the same structure as the non-partitioned table (Hash Distributed including clustered columnstore). Partitions are created on a datetime column using CTAS statement and right range partitioning strategy. Following this, I executed the UPDATE statistics command. The number of rows per partition is:
Partition 1 (2016-2020): 35,704,486
Partition 2 (2021): 58,548,703
Partition 3 (2022): 75,665,750
Partition 4 (2023): 44,080,789
The table skew percentage to the partitioned table is 53.00877.
I ran the same query in both tables. In partitioned table I use the partitioned column as a filter (WHERE clause). Unfortunately, there is no improvement in the execution time in the query with partitioned table. The execution time in both queries was the same.
Also, I have tried to create a new workload group (wgUserQueries), with more resources to handle the queries, but that didn't make any difference either. I have advised best practices for partitioning tables and tuning queries. I have tried different combinations for indexes (clustered and non-clustered) but the issue still exists. In the execution plan, there is a 100% cost in the BroadcastMoveOperation."
Please advise what is happening and why there is no improvement after switching to partition table and what else should I consider?
Thank you!
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 9%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator2023-08-16T23:47:28.9733333+00:00 @Efstathiadou Androniki Welcome to Microsoft Q&A forum and thanks for reaching out here.
To better assist on this requirement, could you please share the query that you are using?
Since you highlighted that there is a 100% cost in the
BroadcastMoveOperation, I suspect data movement may be a bottleneck. Would recommend to go through this document and see if those suggestions help to improve the performance: Investigate data movement on the distributed databases -
Efstathiadou Androniki 5 Reputation points
2023-08-17T07:30:06.5833333+00:00 Dear KranthiPakala,
Thank you for your response.
For the query I will have to get permission from my client to share it. Please find attached the execution plan from the query with the Partitioned Table :
According to Microsoft’s recommendations:
I tried to increase the resources by creating a new workload group, but there is no improvement in the query. I also created partitions on the problematic table and for maintenance I did REBUILD INDEXES using the "xlargerc" management workload group for each partition separately, but there is no improvement.
Do you think the cause might be the insufficient service level(DWH Units)?
In the meantime, I will check the link you provided.Thank you.
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-08-29T04:49:45.9133333+00:00 @Efstathiadou Androniki Thanks for the additional context. So far, looks like you have tried all possible options.
If this is still a blocker, I recommend filing a support ticket for deeper investigation as this is more of Performance related problem. Please do share the SR# with me here once it is filed so that I can track it internally. In case if you don't have a support plan, please le me know so that I can work with you offline.
Thanks
-
Efstathiadou Androniki 5 Reputation points
2023-09-01T07:23:24.3633333+00:00 Dear KranthiPakala,
Thank you, of course, I will open a request from the customer account. I will share it with you.
Thank you,
-
Efstathiadou Androniki 5 Reputation points
2023-09-01T13:01:55.8333333+00:00 Dear KranthiPakala,
Please check, the following Support request number: 2309010050002077.
Thank you
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-09-01T19:09:15.2466667+00:00 @Efstathiadou Androniki Thank you so much for sharing the details! Appreciate much!
Sign in to comment
Sign in to answer