On Prem vs PaaS Performance aspect

Nandan Hegde 29,886 Reputation points MVP
2022-05-18T07:36:49.1+00:00

Hello All,

Wanted some understanding w.r.t performance aspects in our migration initiative:

Current architecture:

On Prem database server ( seattle) >>> PowerBI gateway (on VM) {Seattle} >> PowerBI dataset/Dataflows ( US )

New Architecture :
We migrated the onprem database to Azure SQL database

Azure DB (North Europe) >> PowerBI dataset/Dataflows ( US )

Since the Azure DB is in cloud, there is no need of any gateway.

Based on my understanding the 2nd flow refresh should be much quicker but currently we are seeing that the refresh time taken in the 2nd flow is much more than the initial flow.

Any reason why ?
Shouldnt cloud to cloud communication be much more faster than one via gateway and hosted on server.

Note: The on prem is getting refreshed within 2 mins and cloud flow is taking ~30 mins

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2022-05-18T10:44:23.837+00:00

    Good morning Nandan,

    On Azure SQL database all your queries and processes should be well optimzed. Examine the query plans.

    Some service tiers limit the max degree of parallelism that queries can use , in some workload scenarios like this one that can benefit performance but in others that can hinder performance.

    On Azure SQL the benefits of maintaining indexes and statistics is crucial for performance. Use Ola Hallengren scripts to defrag and update statistics and then test performance again.

    In addition, premium tiers use local attached SSD which faster is. Examine what kind of waits you are seeing when those processes execute. If you see IO waits, then Premium tiers should be considered if indexes and statistics are in good shape.

    In terms of CPU resources premium tiers do not mean more advantage, for example a P1 tier has a max degree of parallelism of 1 core. While a S12 tier has a limit of 22 cores.

    Depending of how the workload is intensive on IO or CPU you need to take that in consideration when hardware sizing your project.
    RAM memory is another important factor, for example a P1 is limited to 5 GB of RAM (5610496) despite been a Premium tier.

    Finally, make sure you have no important missing indexes if you are using Azure SQL, or you may have to scale up resources to get the performance you need.

    We are here to help you. You can contact me personally if that speed up things for you.