Synapse SQL pool performance issue

Question

Synapse SQL pool performance issue

Dejan Ivanović 20

We have been experiencing performance issues with Synapse over the past 30 days. Below, you will find screenshots with comments to help illustrate our situation.

At the beginning of March, we noticed that although no changes were made on our end, we were no longer hitting our DWU limit, which had previously been the case in February.

User's image

Upon investigation, we discovered that we are not the only ones facing this issue (Azure Synapse - slow performance issues - Microsoft Q&A). It appears that changes were made on the backend. After a restart, we observed a slight improvement in performance, but we still do not see the same DWU limit utilization as before.

User's image

Last night, we encountered a significant issue where, for a period of 30 minutes, only 5% of resources were allocated to a single active query.

User's image

The last thing we noticed is that after restarting Synapse, the adaptive cache usage percentage did not recover to its previous highs.

Last 7 days:

User's image

Last 30 days:

User's image

All this led to significant increase in sql queris execution time while none of the querries were changed.

Are you aware of any recent backend changes that may have affected Synapse settings? Could you help us understand how to optimize resource utilization?

Ganesh Gurram 7,235 Reputation points Moderator

2025-04-07T01:25:12.6933333+00:00

@Dejan Ivanović

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer accepted by question author

0 additional answers

Your answer

Ganesh Gurram 7,235 Reputation points Moderator

2025-04-07T01:25:12.6933333+00:00

@Dejan Ivanović

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Smaran Thoomu 32,530 Microsoft External Staff Moderator

Hi @Dejan Ivanović

I understand that you've been experiencing performance issues with your Synapse SQL pool over the past 30 days, including reduced DWU utilization, slower queries, and lower adaptive cache usage after restarts.

To assist you better, I have a few clarifying questions:

Recent Backend Changes:

While Microsoft continuously optimizes the Azure platform, we do not have direct visibility into backend infrastructure changes. However, I can check if there are any known incidents or updates affecting Synapse SQL pool performance.
Could you confirm your Azure region where the Synapse workspace is deployed?

DWU & Resource Utilization:

Since you noticed a drop in DWU utilization, have you checked resource class settings for your queries?
Are queries running with the same distribution strategy and statistics updates as before?

Query Performance Debugging:

Could you try running the following diagnostic query and share the results?

SELECT * FROM sys.dm_pdw_nodes_exec_requests
WHERE status NOT IN ('Completed', 'Failed')
ORDER BY start_time DESC;

This will help us analyze active queries and identify potential bottlenecks.

Cache Behavior & Restart Impact:

Adaptive caching behavior can depend on workload patterns. Did query execution plans change significantly after restarts?
Are there specific queries or tables where you observed a higher cache eviction rate?

I appreciate your patience, and I look forward to your response!

Dejan Ivanović 20 Reputation points

2025-04-03T07:39:34.73+00:00

Hi Smaran!

Our Synapse workspace is deployed in West Europe region.

There were no change in resource class settings for our querries. Slower performance is noticed for largerc workload.

Are queries running with the same distribution strategy and statistics updates as before?
How to check if there were changes in distribution and statistics?

Querry results are in attachment. 20250403_dm_pdw_nodes_exec_requests.txt

We did not change querry definitions nor we change execution plans.

There were no specific table or querry that has issue. Every of 227 querries last longer.

Thanks for support.
Smaran Thoomu 32,530 Reputation points Microsoft External Staff Moderator

2025-04-03T08:08:56.41+00:00
Hi @Dejan Ivanović

Thank you for sharing the details. Based on your responses, here are some steps to further investigate and optimize performance:

Since you mentioned no changes were made to queries, resource classes, or execution plans, the next step is to verify if distribution or statistics have changed:

Check Table Distribution:

Run the following query to review how tables are distributed:

SELECT name, distribution_policy_desc FROM sys.pdw_table_distribution_properties;

If any tables have changed from hash-distributed to round-robin, it could impact performance.

Check Statistics Updates:

Run the following query to check when statistics were last updated:

SELECT name AS TableName, stats_date(object_id, stats_id) AS LastUpdated FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.tables);

If statistics have not been updated recently, consider running:

UPDATE STATISTICS <TableName>;

Analyze Query Performance:

Since all 227 queries are running slower, check query wait times using:
SELECT * FROM sys.dm_pdw_exec_requests ORDER BY start_time DESC;

Look for queries stuck in a waiting state.

If queries are queued, consider scaling DWU temporarily to see if it resolves resource contention.

Adaptive Cache & Backend Changes:

If adaptive cache usage dropped, it might indicate changes in workload patterns or backend behavior.

Try running queries after manually warming up frequently accessed tables using:
SELECT COUNT(*) FROM <TableName>;

Further Actions:

If scaling DWU does not restore performance, consider raising a support ticket with Microsoft.

Let us know if these steps help or if you'd like further analysis on the execution plan results you attached.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Smaran Thoomu 32,530 Reputation points Microsoft External Staff Moderator

2025-04-04T09:46:52.8433333+00:00

@Dejan Ivanović We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Synapse SQL pool performance issue

0 additional answers

Your answer