Failed copy command operation for a memory usage limit issue

Question

Failed copy command operation for a memory usage limit issue

pmscorca 1,052

Hi, I'm working with a DW100c Synapse SQL pool. In particular, by Data Factory I'm implementing a pipeline with a copy activity having an Oracle on-premises as a source and the SQL pool as a sink. Running the pipeline I've obtained this error: "Copy Command operation failed with error 'Could not get the memory grant of 243160 KB because it exceeds the maximum configuration limit in workload group 'SloDWGroupSmall' (2000001783) and resource pool 'SloDWPool' (2000000006). Contact the server administrator to increase the memory usage limit." I've solved by scaling the SQL Pool from DW100c to DW200c, but is it the better solution, considering the costs? Thanks

Harishga 6,005 Reputation points Microsoft External Staff

2024-02-14T04:46:19.24+00:00

Hi @pmscorca
Following up to see if the below answer was helpful. 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.

2 answers

Your answer

Harishga 6,005 Reputation points Microsoft External Staff

2024-02-14T04:46:19.24+00:00

Hi @pmscorca
Following up to see if the below answer was helpful. 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

pmscorca 1,052

Hi @Harishga as as adding comment, I say that in Synapse as an ADF user I'm using the ADF system managed identity.

Before the pipeline to copy from the Oracle source table to the Synapse sink table could I add this workload classifier with no impacts for the system managed identity database user?

CREATE WORKLOAD CLASSIFIER [wgc_ADFmanagedidentity]
WITH (WORKLOAD_GROUP = 'xlargerc'
      ,MEMBERNAME = 'ADFmanagedidentity'
	  );

As a last activity of the pipeline, could I drop the workload classifier with any impacts for the system managed identity database user?

It isn't possible to try the create and drop workload classifier by a transaction.

I think that for my requirements I need to have a workload classifier with only a major resources, so to restore the SQL pool to DW100c.

Thanks

Harishga 6,005 Reputation points Microsoft External Staff

2024-03-06T08:23:40.89+00:00

Hi @pmscorca
To answer your first question, changing the workload settings is not a permanent action, and you can change them before the ADF pipeline run and then restore the previous workload setting. You can create a separate workload group for the ADF pipeline and assign the necessary resources to it. After the pipeline run, you can remove the workload group or adjust its settings as needed.

Regarding your second question, you can use the Execute SQL activity in ADF to create and drop the workload classifier. You can add the CREATE WORKLOAD CLASSIFIER statement as a SQL script in the Execute SQL activity and run it before the copy activity. Similarly, you can add the DROP WORKLOAD CLASSIFIER statement as a SQL script in the Execute SQL activity and run it after the copy activity.

Regarding your last question, if you need to have a workload classifier with only a major resource, you can create a separate workload group with the necessary resources and assign the ADF pipeline to it. After the pipeline run, you can remove the workload group or adjust its settings as needed.

I hope this information helps you. Let me know if you have any further questions or concerns.

Answer 2

Harishga 6,005 Microsoft External Staff

Hi @pmscorca

Welcome to Microsoft Q&A platform and thanks for posting your question here.

Based on the error message you provided, it appears that you encountered a memory usage limit issue while running a pipeline that involved copying data from an Oracle on-premises source to a Synapse SQL pool sink. The error message indicates that the memory grant required by the query exceeded the maximum configuration limit in the 'SloDWGroupSmall' workload group and 'SloDWPool' resource pool.

You mentioned that you solved the issue by scaling up the SQL pool from DW100c to DW200c. While scaling up can be a quick solution, it may not always be the most cost-effective option. One alternative solution is to modify the memory grant configuration for the workload group and resource pool. You can increase the memory usage limit by contacting the server administrator or modifying the configuration settings yourself.

Another solution is to increase the memory usage limit for the workload group and resource pool. You can contact the server administrator to increase the memory usage limit. However, this may not always be possible or cost-effective, especially if you need to increase the limit frequently.

I recommended that scaling up the SQL pool may not always be the best solution. You can try to optimize the query or increase the memory usage limit for the workload group and resource pool. If you need to scale up frequently, you may want to consider a long-term solution that is more cost-effective.

Reference
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/resource-classes-for-workload-management

Hope this helps. Do let us know if you any further queries.

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.

pmscorca 1,052 Reputation points

2024-02-11T18:12:40.2933333+00:00

Hi, I'd like to continue to use the DW100c SQL pool and find an alternative solution. I'm trying to explain better the scenario. I need to get data from an Oracle source table having about 2 millions of rows for 270 columns in order to retrieve historical data. This load is to execute to once time. The Synapse sink table is an hash distributed table, having a nonclustered primary key. To perform a such load, I've implemented an ADF pipeline by a copy activity with a copy command and an enabled staging. The user of the Data Factory has the dbowner role inside the SQL pool. Now for a such scenario, in order to manage the workload should I change the resource class or acting at workload classification, importance and isolation level? F.e. thinking to change the resouce class I could assign the largerc or xlargerc database role to the ADF user first to run the copy activity and then dropping the assigned database role. Any suggests to me, please?
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-12T07:06:31.8833333+00:00
Hi pmscorca

To manage the workload, you can consider adjusting the resource class or workload classification, importance, and isolation level. Here are some suggestions:

To improve the performance of the copy activity and prevent memory grant issues, you can assign a larger resource class to the ADF user running the activity. This will increase the memory and CPU resources available to the user. Assign the larger resource class to the user before starting the copy activity and then remove it after the operation is complete by dropping the assigned database role.

Prioritize the copy activity as a high-priority workload by creating a workload group and setting the maximum memory and CPU usage for the group. Then, assign the copy activity to the workload group and set its importance level to high.

Set the importance level of the copy activity to high to ensure that it gets the necessary resources to complete the operation and is prioritized over other activities in the SQL pool.

Isolate the copy activity from other activities in the SQL pool by creating a separate workload group for the copy activity and setting the maximum memory and CPU usage for the group.

Instead of assigning a larger or xlargerc database role to the ADF user, assign a resource class or workload classification to the ADF pipeline to ensure that the user only gets the necessary resources to complete the task and avoid security risks. Assigning a resource class or workload classification to the ADF pipeline allows you to scale the workload more efficiently by allocating more resources to manage the workload.

Regarding to the suggestion of assigning a larger or xlargerc database role to the ADF user, this may work, Instead, you can consider assigning a resource class or workload classification to the ADF pipeline to manage the workload more efficiently.

I hope this information helps you. Let me know if you have any further questions or concerns.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-13T03:29:47.3133333+00:00

Hi pmscorca
Following up to see if the above answer was helpful. 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.
pmscorca 1,052 Reputation points

2024-03-05T14:19:35.7866667+00:00

Hi, thanks for your reply.

I say again that I need to run an ADF pipeline to copy data from an Oracle source table on-premise to a corresponding Synapse synk table; so there isn't any particular query. The Oracle source table has 260 columns and 2 millions of data rows!

It is interesting to act at workload level and I could figure that follow this way is cost free, but I've some questions.

Is chaging the workload settings a permanent action or is it possible to change them before the ADF pipeline run and then restore the previous workload setting? Moreover, where changing the workload settings, is there a particular ADF activity to do it?

The pipeline has to run by a scheduled trigger.
Thanks

Share via

Failed copy command operation for a memory usage limit issue

2 answers

Your answer