Sizing Azure Sql Managed Instance

[RonFusionHSLLC]

We have sort of solved our issues with Replicate performance, still have a ways to go.  We decided to move on to Compose DW/DM builds.

Our current Compose server seems to be sized adequately, as the cpu/memory don't show any pressure during the loads.

What we are seeing is huge numbers in INSTANCE_LOG_RATE_GOVERNOR waits.  50% of any insert operations turns out to hit the instance_log_rate_governor wait, indicating that the Managed Instance cant backup the logs as fast as Compose can deliver data to insert.

We are complete rookies in Azure Sql Managed Instance, and are wondering how do we decide how to improve performance.  Opting for $13k++/month instances is not an option for us.

Our source is Azure Sql Managed Instance, around 180million rows over 288 tables amounting to a mere 100Gig of data.

Has anyone out there had to decide between Azure Sql Managed Instance and Dedicated Server+SQLServer install in Azure?

What were the trade offs (other than oS/db maintenance)?

ron

[Nanda_Ravindra · Accepted solution]

@RonFusionHSLLC Compose doesn't need a 'full recovery mode' to run the process. So, changing it to 'simple recovery mode' might be an option here to get around the INSTANCE_LOG_RATE_GOVERNOR wait time you are seeing.

I would suggest you check with your DBA's or with the MS team to see if they can enlight on the trade-off between on-prem vs Azure.

Hope this helps!

Thanks,

Nanda

[RonFusionHSLLC]

Yep, thought of that.  Azure Sql Managed Instance do not give you that option.  Since they are managed by Microsoft, they dictate many settings