question

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 asked OuryBa-MSFT commented

On Prem vs PaaS Performance aspect

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered OuryBa-MSFT commented

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.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@NandanHegde-7720 Please do not forget to mark as accept answer if the reply from @AlbertoMorillo helped.

Regards,
Oury

0 Votes 0 ·