Data loading into sql managed instance table taking time

Dixon Rodrigues 0

Hi All,

we have couple of data loads in which the distribution is taking lot of time. Our source queries are extracting results in and around 15 minutes but as per sys.dm_pdw_sql_requests column operation_type=OnOperation and column distribution_type=Alldistribution data loading into a either Round Robin or Hash(Column) taking more than 45 minutes. Ofcourse we have crores of records to be loaded into the table.

There is no lack of any TempDB space observed. We have DW500C managed instance.

Any suggestion, insights that why the system is taking so much time while loading the data to a table.

Erland Sommarskog 116K Reputation points MVP

2025-01-02T22:19:34.2366667+00:00

Since you mention sys.dm_pdw_sql_requests and DW500, I don't think you are using Azure SQL Managed Instance, but rather Azure SQL Synapse Analytics, so I changed the tag. You may also want to change the title.
Sai Raghunadh M 1,915 Reputation points Microsoft Vendor

2025-01-03T09:25:01.94+00:00

Hi @Erland Sommarskog

Thanks for sharing your insights.

@ dixon Rodrigues

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.

1 answer

Sai Raghunadh M 1,915 Reputation points Microsoft Vendor

2025-01-02T12:00:50.9533333+00:00

Hi @dixon Rodrigues

Thanks for the question and using MS Q&A platform

It appears that you are encountering a performance issue with query distribution in your SQL Managed Instance.

Since the operation_type is set to OnOperation and the distribution_type to AllDistribution, it appears the query is being distributed across all distributions. This can be resource-intensive and time-consuming, particularly with a large volume of data.

If possible, break down the data load into smaller batches. This can help reduce the load on each distribution and improve overall performance.

If your source data is remote, network latency can be a factor. Ensure the data transfer speed is optimal.

Check that no resource limits are imposed by the Resource Governor that might slow down your load process.

Please refer to these Documentations for Additional Information:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-service-capacity-limits#metadata

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-sql-requests-transact-sql?view=aps-pdw-2016-au7

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

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.
Please sign in to rate this answer.
Sai Raghunadh M 1,915 Reputation points Microsoft Vendor

2025-01-06T05:51:12.7766667+00:00

Hi @dixon Rodrigues

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.

Rodrigues Dixon 0 Reputation points

2025-01-23T11:45:59.0233333+00:00

Hello Sai Raghunandan, Sorry for late reply.

Thanks for your response and the knowledge link that you shared.

I would like to understand bit deeper in terms of steps that are shown at sys.dm_pdw_sql_requests.

Problem statement is, if i run the select query that loads the table, it completes in 15 minutes whereas if i submit the same select query to a table as an INSERT statement, takes more than an hour.

Run time is same for both Round Robin and HASH columns distribution.

when i look at steps showed at sys.dm_pdw_sql_requests, as soon as i submit the query, I see the plan immediatly jumps to step_index 27 with Operation_type=OnOperation, Distribution_type=AllDistributions, Location_type=Compute. and the system keep on running in the same step for more than an hour.

When i track the previous steps Row_count column, all values are showed as -1.

whats happening behind the screen? If the execution step immediately jumps to 27, does it mean all previous steps are really executed? For all these previous steps, the status column says COMPLETE.

Something weird to me.

Could this scenario be explained for me to for to understand query execution from the backend persepective?

Thanks

Dixon

Rodrigues Dixon 0 Reputation points

2025-01-23T12:01:30.6233333+00:00

Hello Sai Raghunandan

Thanks for your response and sorry for late reply.

I still have some doubt in terms of back end execution that showed at sys.dm_pdw_request_steps.

If i execute my select query of the insert statement, it completes within 15 minutes but if i run along with INSERT statement, the query runs for more than an Hour.

While this insert query is running and if i parallely verify the sys.dm_pdw_request_steps. I see the execution step jumps directly to step_index 27 as soon as i submit the query.

while it was in step_index 27 and long running there, i went to previous step_index and started checking the row_count column, all steps have rowcount as -1.

This makes me weird in two ways, 1st thing is, execution step directly jumped to 27th step and if i track previous steps rowcount, all shows -1

Could you please help me to understand how to read the output of sys.dm_pdw_request_steps? how the query works at the backend when i submit the INSERT statement.

That would give me some idea about execution plan.

Run time for the insert query is same for ROUND ROBIN and also over individual HASH column and a set of HASH Column distribution.

Rodrigues Dixon 0 Reputation points

2025-01-23T12:04:51.3133333+00:00

Also execution time is not improving with XLARGERC workload change. The run time is same as STATICRC40. Ofcourse nothing was running in parallel during this testing.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Data loading into sql managed instance table taking time

1 answer

Your answer