Azure Datawarehouse Performance Tuning

Bharathiraja 1 Reputation point
2020-10-13T06:52:25.62+00:00

Hi..Am currently working on Azure Sql DB to Azure Datawarehouse migration.. After migration when I tired to run fact load sp am facing severe performance issue.. That sp which has while loop in it and it has to iterate for 7000+ plus times. For each and every iteration it is taking 1 minute to complete. I have tried the following steps to resolve the performance issue but still issue exists, a. Created indexes matching with existing system b. Scaled up DWU from 100C to 1000C c. Updated Statistics on relevant attributes Also to highlight here, it is not individual query taking time inside that sp.. Each and every query taking considerable amount of time. Note: But the same SP is running within 4 to 5 secs in Azure Sql DB. Please hep here. Thanks In advance

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,916 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike Ubezzi 2,776 Reputation points
    2020-10-14T06:31:47.537+00:00

    Hi, @Bharathiraja - Thank you for bringing this issue to our attention as we can assist you. First, Azure SQL DB and Azure Synapse Analytics (SQL Pools and SQL on-demand) should be considered separate systems when applying performance and tuning logic, and there are quite a few docs to outline and explain the options.

    Synapse Best Practices Documentation:

    • Performance tuning with materialized views (link)
    • Best practices for Synapse SQL pool in Azure Synapse Analytics (formerly SQL DW) (link)
    • Performance tuning with ordered clustered columnstore index (link)

    Can you detail a bit about the size of your deployment and the transaction/data volume mix? Can you scale up your instance to complete the activity? You can monitor your workload via the portal which is the easiest means to start investigating, identifying, and taking actionable steps to optimize your solution.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.