Dedicated SQL Pool is taking a long time scale up and down

Question

Dedicated SQL pool is taking 20 - 30 minutes to scale up and down. Average time prior to Jan 16 was 10 minutes or less. This is impacting production performance. As a side note, we had this issue last year and it was resolved. What was fixed seems to have crept back in.

Answer 1

Scaling operations in a dedicated SQL pool can take several minutes to complete, and the time it takes can be influenced by various factors, including the state of in-flight transactions. When you initiate a scale operation, the system first cancels all incoming queries and rolls back any ongoing transactions to ensure a consistent state. This rollback can significantly impact the time it takes to complete the scale operation, especially if there are long-running transactions that need to be undone.

If your dedicated SQL pool is now taking 20-30 minutes to scale compared to an average of 10 minutes or less previously, it may be worth investigating the following:

1. In-flight Transactions: Ensure that there are no long-running transactions when you initiate the scale operation. Allowing existing transactions to finish before scaling can help reduce the time taken.
2. Query Performance: If scaling out does not improve performance, consider checking for issues such as data skew or excessive data movement, which can affect performance during scaling operations.
3. Resource Allocation: Review the current resource allocation and consider whether the current Data Warehouse Units (DWUs) are sufficient for your workload demands.

If this issue has recurred, it might be beneficial to look into any changes in workload or configuration that could have contributed to the longer scaling times.

---

References:

• Manage compute resources for dedicated SQL pool
• Optimizing transactions in dedicated SQL pool in Azure Synapse Analytics

Answer 2

No workload configurations have changed and the environment is optimally tuned for the best performance. The issue that we encountered previously was a Microsoft issue that needs to be looked into today.