Problem Summary:
One of the biggest challenges in creating an Azure Managed Instance is determining the resource limits.
In this case study, we use the Microsoft guidelines and the SQL Server Distributed Replay feature to assess a SQL Server migration and choose the right service tier.
The Distributed Replay Test
Current Production (Source Server):
- 48 CPU cores;
- 200GB RAM Memory;
- SQL Server 2008 R2 (SP1) – Enterprise Edition
Azure Managed Instance (Target Server)
- Gen 5, 4 vCores;
- 20,4GB RAM Memory;
- A SQL Managed Instance always runs the latest stable SQL engine version, equal to or higher than the latest available RTM version of SQL Server.
Workload Replay
- Collector: Profiler
- Duration: 8 hours
- Collection date: 02/07/2020
- #of commands: 1,981
- #of executions: 10,741,98
We executed a sampled workload. The criteria used were replaying the workload that happened every 30 minutes for 10 to 15minutes.
Wait observed during Replay (Target)
As we can see in the table above, 67% of waits faced by SQL Server are related to lack of Memory to accommodate the cache needed. Considering that the current prod has 200GB of Memory and the Azure Managed instance only has 20GB, this explains the bad performance result below for CPU and duration.
The Azure managed instance needs more Memory and CPU to present the safe performance visible in the current prod.
Replay Results (Target)
Before we draw any conclusion, it is essential to keep in mind that we are comparing a 48 core – 200GB RAM Server with an Azure managed instance that under the hood has only 4 cores and 20GB.
As per the table above, the new server presented a worse CPU and duration even though it had much better reads. The better reads are justified by the query engine with a decade of improvements compared to the current prod version.
The poor CPU and duration are expected because the number of CPU cores is much lower in the Azure managed instance.
Conclusion
The replay test was essential to figure out the main bottlenecks. In short, after some tests, we precisely recommended upgrading the Azure managed instance server (MI) tier, increasing the number of vCores to 16 and the Memory to 82GB.
Finally the migrations went very well! Without problems. And most importantly, without nasty surprises.
See more SQL Server Performance Optimization examples here.
We’d love to talk to you about any questions you may have about your SQL Upgrade or Migration project specifics.
