Log Rate mesument is required prior to Migrate the sql server Database azure MI instance

Azure SQL Database Managed Instance stands as a fully managed iteration of the SQL Server Database engine, hosted within the Azure cloud, offering inherent maintenance operations such as automated backups.

Ensuring comprehensive coverage of changes across all databases, Managed Instance commits to taking database backups every 5–10 minutes, specifically log backups. To accomplish this, it imposes log rate limits per instance, tailored to catch up with updates efficiently:

• General Purpose instance: Up to 22 MB/s
• Business Critical instance: Up to 48 MB/s

It is advisable to verify these values in the Managed Instance documentation as they may undergo changes over time.

These limits may impact the performance of certain log-intensive operations, including:

1. Bulk loads that introduce substantial data into the database.
2. Index rebuilds that recreate indexes at a rate exceeding the instance’s log limits.

If you suspect performance issues, it is crucial to assess whether these limits are being reached.

To identify log rate limits on Managed Instance, examination of Query Store and DMVs is one approach. Alternatively, using the Query Performance Insight helper library, specifically designed for Managed Instance, simplifies the analysis. Though not mandatory, adding the library’s procedures and views to your database facilitates the process.

To initiate analysis, follow these steps:

1. Execute the qpi.snapshot_wait_stats procedure to capture wait statistics, subsequently viewable through qpi.wait_stats.
2. Identify the presence of “Log Rate Governor/INSTANCE_LOG_RATE_GOVERNOR” at the top of wait statistics, indicating potential log rate limit issues.

Additionally, file statistics help verify that the total log rate across all files aligns with the instance limit. Establish a baseline snapshot with EXEC qpi.snapshot_file_stats and then calculate the sum of throughput values for all log files:

SELECT instance_log_throughput_MBps = SUM(write_mbps)
FROM qpi.file_stats
WHERE type = 'LOG';

If the obtained value approximates the specified limits, it suggests hitting the instance log limit cap. Note that variations may occur due to workload spikes, as the query returns average values within a given interval.

Unfortunately, there are no direct corrective actions to avert this issue, as the log rate limit serves as a necessary trade-off to consistently ensure timely backups. For those migrating from SQL Server to Managed Instance, repeating this assessment on the SQL Server instance can gauge compatibility and performance under similar circumstances, using the appropriate version of the Query Performance Insight library.