Log rate throughput cap on Managed Instance

Azure SQL Database Managed Instance is a fully managed version of SQL Server Database engine hosted in Azure cloud that provides built-in maintenance operations such as automatic backups.

Managed Instance guarantees that the database backups are taken every 5–10 min (log backups) so it must ensure that it can take all changes made in all databases and create the backup of all updates. Managed instances introduces the following constraint for the log rate limits per instance to ensure that it can catch-up all updates:

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

Make sure that you check these values on Managed Instance documentation because they might change in the future.

These limits might slow down some log intensive operations such as:

• Bulk load that loads a huge amount of data in your database
• Index rebuild could recreate indexes with the rate much higher than the instance log limits.

If you believe that your workload is slow, you should check are you hitting these limits.

Identifying log rate limit caps

You can identify log rate limits on Managed instance by looking at Query Store and DMVs; however, it would be easier to take the Query Performance Insight helper library that contains several wrappers around the system views. This is not mandatory, but it would make analysis easier. There is a version build for Managed Instance that you can run on your database.

Once you add the procedures and views from this library, you can start analyzing wait statistics.

First, you need to take a snapshot of wait statistics using the following procedure:

exec qpi.snapshot_wait_stats;

This procedure will clear the dm_os_wait_stats values so you can see the latest wait statistics since the snapshot time.

Then you can use qpi.wait_stats view to get the recent wait statistics ordered by wait_time_s:

Log Rate Governor/INSTANCE_LOG_RATE_GOVERNOR in the top of the wait statistics is the indication that your instance is probably hitting the log rate limits.

You can find more information how to analyze wait statistics on Managed Instance in this story.

Now you can use file statistics to verify that total log rate on all files is close to the instance limit. First you need to take a snapshot of the current file statistics that will be used as a baseline:

EXEC qpi.snapshot_file_stats;

Then you can calculate sum of throughput values for all log files in your Managed Instance:

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

If you are getting the value that is close to the limits described above, then you are hitting the instance log limit cap.

Note that it is not likely that you will always get the value that exactly matches instance log limits. Your workload might have spikes of usages and the query above returns average values in some interval.

Unfortunately, there are no corrective actions that you can do to prevent this issue. Limit on the log rate is the price that must be taken to always ensure that the backups are taken.

If you are migrating your databases from SQL Server to Managed Instance you can repeat this experiment on your SQL Server instance to check what are the instance log limits that you have and could your workload run on the Managed Instance with the same performance. Just make sure that you are using the version built for your version of SQL Server.