Share via

Sending resource alerts on Managed Instance using db_mail

  • Article

One of the biggest issue that you might experience in Managed Instance is reaching storage limit or finding out that you don't have enough CPU. In this case you would need to get the bigger instance; however, this is not instant operation. In this post, you will see how you can monitor resource usage and send email alerts if there is a risk that you might reach the limits.

Azure SQL Managed Instance enables you to define how much resources you want to provision in terms of max storage and max cores that can be used. If you reach the storage limit there are several issues that will happen:

  • Your databases are in "read-only" state because nothing can be written in log/data files.
  • Some of the read queries cannot run because they might require tempdb to grow to store some temporary objects.
  • CHECKPOINT cannot flush data from memory into mdf files.

Generally, this is the case that you want to avoid. Although you can always upgrade the service tier and get more storage or CPU, this is not an instant operation in Managed Instance because new a host VM must be provisioned and injected in your network.

Therefore, it is important that you constantly monitor your instance and find if you are reaching the storage limits.

Managed Instance provides a system view in master database called sys.server_resource_stats that contains information about the CPU usage, reserved and used storage that enables you to monitor usage in the real-time:

 select top 1 avg_cpu_percent,
storage_space_used_mb / reserved_storage_mb
from master.sys.server_resource_stats
order by start_time desc

This view contains snapshots of 5-min usage on the Managed Instance in last two weeks, so you just take the most recent row and see CPU/storage usage. You can periodically run the query on Manage Instance and take some action if the percentages get high.

Sending alerts using db_mail

As an alternative, you can enable db_mail feature and send the email alerts directly from Managed Instance once you are close to resource limits. If you haven't configured emails on Managed Instance take a look at this post.

The following script will send you an email if CPU usage is above 95% and storage usage is above 90%:

 declare @cpu_perc float, @storage_perc float;
declare @instance nvarchar(200) = @@SERVERNAME;

select top 1 @cpu_perc = avg_cpu_percent,
@storage_perc = storage_space_used_mb / reserved_storage_mb
from master.sys.server_resource_stats order by start_time desc

if(@cpu_perc > .95 or @storage_perc > .9)
begin

declare @msg nvarchar(max) = CONCAT('You are reaching the compute/storage limits of your instance ', @@SERVERNAME, ':
Storage ', @storage_perc, '%
CPU usage:', @cpu_perc, '%
Consider upgrading the instance.');
exec msdb.dbo.sp_notify_operator 
@profile_name = N'AzureManagedInstance_dbmail_profile', 
@name = N'DevOps team', 
@subject = N'Azure SQL Instance - Storage limit alert', 
@body = @msg;
end

CPU limit might not be critical and you might find the average CPU usage in last few hours. However, if you are reaching the storage limit you should take some actions faster and add more storage or free-up some space on your instance.

You can easily automate this query and copy/paste it into SQL Agent job that will run every 15 minutes and send the email if you are reaching the limit.