Insert operation at Azure SQL Synapse Analytics is running longer

Question

Hi,

We have DW500C system and observing strange behavior, which i would like to understand bit better.

we have a Fact load Insert statement that runs for more than an Hour and its direct execution of SELECT statement is completing within 15 minutes.

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

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.

While it was in step_index=27, the Operation_type=OnOperation, Distribution_type=AllDistributions, Location_Type=Compute. and it remains in this step till the insert query completes.

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 the output of sys.dm_pdw_request_steps? how the query works at the back end 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 with individual hash column and a set of HASH Columns distribution.

while testing this long running INSERT statement, nothing was running in parallel.

Performance is the same even if run the query under XLARGERC or STATICRC40.

Answer 1

The observed behavior with the long running INSERT operation on sql synapse analytics can be analyzed by understanding the execution process at the backend and the role of the dmv sys.dm_pdw_request_steps. Here's an explanation...

Jumping Directly to Step Index 27:

• When you submit the INSERT statement, the query execution starts, and sys.dm_pdw_request_steps logs the steps involved in processing the query.
  • Jumping directly to step index 27 may indicate that the query optimizer is skipping intermediate steps to focus on the primary data movement or processing required for the INSERT operation. This could happen if the system recognizes these steps as irrelevant to the current query execution.
  Row Count as -1 in Previous Steps:

  - A row count of `-1` typically means that the steps were not executed or are placeholders in the query execution plan. It could also imply no data movement or processing occurred in those steps. This behavior is not unusual if the actual execution bypassed earlier steps due to query optimization.
  
  Step Index 27 Details:
  
     - `Operation_type=OnOperation`, `Distribution_type=AllDistributions`, and `Location_Type=Compute` suggest that the operation involves data movement or processing across all distributions in the compute nodes.
  
        - This is likely where data shuffling occurs, which is resource-intensive and time-consuming, especially if the data is large or not optimized for distribution.
  
        Hash vs. Round-Robin Distribution:
  
           - The performance being similar with both ROUND ROBIN and hash-distributed tables indicates that the bottleneck is not in the distribution choice but possibly in other factors such as:
  
                 - Data movement (reshuffling of data for distribution compatibility).
  
                       - Suboptimal statistics or indexing.
  
                             - Resource contention or query design inefficiencies.
  
                             Impact of Resource Class:
  
                                - The runtime being the same under different resource classes like XLARGERC or STATICRC40 implies the bottleneck is not due to insufficient resources. It could be related to query design, table design, or system-level operations.
  

Recommendations:

• Analyze Distribution Compatibility:
  • Ensure that the source and target tables have compatible distributions to minimize data movement. If the target table is hash distributed, use a matching hash column in the SELECT query.
  Query Execution Plan:
  • Use EXPLAIN to examine the execution plan and identify potential inefficiencies or costly operations.
• Statistics and Indexing:
  • Check if statistics are up to date for the target table. Outdated statistics can cause inefficient execution plans.
  • Use clustered columnstore indexes to optimize bulk inserts for large fact tables.
  Data Skew:
  • Investigate for data skew in the target table, which can lead to uneven distribution and long-running queries. Use sys.dm_pdw_nodes_db_partition_stats to check for skewness.
• Resource Usage:
  • Monitor system resources using query activity and DMVs to ensure no hidden resource contention.

Understanding the output of sys.dm_pdw_request_steps provides insights into the query execution flow. Specifically:

• The steps represent the internal operations carried out during query execution.
• Long runtimes in specific steps (like 27 in your case) suggest intensive operations such as data movement or processing on the compute nodes.

Fine tuning the query, distribution, and indexing should improve performance.