Can you assist with a Synapse issue involving memory allocation in SQL DW Copy Command in Data Factory, without solely resorting to increasing DWU?

Question

I'm encountering an issue while using the Copy Activity in Data Factory with the Polybase copy option. The error message I receive is as follows:

“…..SQL DW Copy Command operation failed with error 'Unable to allocate 257707 KB for columnstore compression because it exceeds the remaining memory from total allocated for current resource class and DWU. Please rerun query at a higher resource class, and also consider increasing DWU…..”

The memory usage is close to 100%.

 The Copy Activity is transferring data from an on-premise SQL source to Azure Synapse.

 

I've come across suggestions to increase the DWU, but I believe this is not the correct solution. Not every performance issue can be solved by increasing the processing power.

 

When I "Suspend" and then "Resume" the Synapse, the memory usage drops from 94% to 5%. Why does this happen?

 

Is there a way to free up space without performing suspend and resume, or is there a way to limit the memory usage percentage to 90%?

 

Any insights or suggestions would be greatly appreciated. Thank you in advance for your help.

Answer 1

It is either you increase the DWU as suggested in the error message like you did.

If possible, consider batching the data load into smaller chunks. This reduces the memory required for each load operation and may help avoid exceeding memory limits.

You can use Query Performance Insight in Azure Synapse to identify any specific queries or operations that are consuming excessive resources.

If you cannot fix your issue, try to open a ticket to the Azure Support team : https://azure.microsoft.com/en-us/support/create-ticket/

Answer 2

Hi @EmaLom

Based on the error message you received, it seems that the memory allocated for the current resource class and DWU is not sufficient to complete the operation. The error message suggests rerunning the query at a higher resource class and increasing DWU.  You are looking for a solution that does not involve increasing the processing power.

When you suspend and then resume the Synapse, the memory usage drops from 94% to 5%. This happens because suspending the Synapse deallocates the resources, including memory, that were allocated to the Synapse. When you resume the Synapse, it starts with a fresh set of resources, which results in lower memory usage.

To free up space without performing suspend and resume, you can try optimizing the query to reduce the memory usage. Here are some suggestions:

• One of the best ways is to compress your data into a column store index, which will reduce the amount of memory needed to store the same data.
• You can also optimize your query to use less memory. This might mean reducing the amount of data processed at once or using more efficient data types.
• Another way to improve performance is to allocate more memory to your query using resource classes. These classes assign a fixed amount of memory to users or groups, and you can create a class with more memory for the user running the query.
• You can also use dynamic resource classes that adjust memory allocation based on the current workload. This ensures that your query gets the memory it needs when it needs it. Alternatively, static resource classes provide a consistent amount of memory, which can be useful for predictable workloads.
• Finally, you can use staging tables to reduce the amount of data transferred between the source and the sink. These tables hold intermediate results, which reduces the amount of data that needs to be transferred during the copy activity.

Regarding limiting the memory usage percentage to 90%, there is no direct way to limit the memory usage percentage in Azure Synapse. However, you can try optimizing the query and reducing the memory usage to keep it below 90%.

I hope these suggestions help you resolve the issue. Let me know if you have any further questions.