IO Usage measures the IO utilization of your database for its currently configured capacity. The amount of total IO available depends on your service tier and the configured amount of vCore of DTU. Blue Matador automatically monitors Data and Log IO utilization via the physical_data_read_percent and log_write_percent metrics for both SQL Databases and Elastic Pools to detect anomalies and detect when IO utilization is near 100%.
Effects
Anomalous IO usage can be an indicator that access patterns have changed in your application, more queries require disk access, or that the database is near its capacity limits. Running at consistently high IO, especially near 100% usage, will be detrimental to the performance of your database and should be addressed ASAP. The most common impact of high IO utilization is dramatically increased query times.
Fixes
Diagnosing and fixing database performance is a complicated and involved process at best. Microsoft provides a very comprehensive troubleshooting guide for SQL Databases here, but we will also go over some basic techniques below to get you started.
First, determine if there is an upstream service that is causing increased load on your database. In an emergency it may be quicker and easier to alleviate load on the database from upstream sources than to do database maintenance on-the-spot.
IO Usage is split into two types: Data IO and Log IO. Blue Matador monitors usage individually but a combination of the two can also cause issues with IO performance. To get current IO usage, run the following query on your database:
SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;
Your options when dealing with high IO usage are to either troubleshoot the queries causing IO issues, or to upgrade your database.
Azure SQL provides Dynamic Management Views on your databases that allow you to quickly identify problematic queries. Follow this documentation to look at the currently running queries and then determine a fix for those queries. Another feature that is especially helpful for debugging IO issues is Query Store. Query Store retains information about query execution plan changes and can help you pinpoint which queries are causing IO issues.
In the case that your database has slowly increased its usage, the correct solution may be to upgrade your database. You can either increase its DTUs, add vCore capacity, or upgrade to a higher service tier altogether.
