Finding the Root cause for IO request count on SQL managed instance

Dayalan T 0 Reputation points
2023-09-13T07:52:16.4133333+00:00

We've set an alert, as the IO request count breaches 750 would alarm us on the metrics, I've been assigned to find the Root cause for IO request count on SQL managed instance. It would be really helpful if someone could help on sharing some techniques on finding the bottleneck that causing the problem

Azure SQL Database
Azure Managed Applications
Azure Managed Applications
An Azure service that enables managed service providers, independent software vendors, and enterprise IT teams to deliver turnkey solutions through the Azure Marketplace or service catalog.
92 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 9,391 Reputation points Microsoft Employee
    2023-09-13T08:46:02.9333333+00:00

    Hi
    Dayalan T     •,

    Welcome to Microsoft Q&A forum

    As I understand, you want to know the root cause of the IO request increase in Azure SQL Managed Instance.

    Please let us know if your ask is different.

    In the General Purpose service tier, every database file gets dedicated IOPS and throughput that depend on the file size. Larger files get more IOPS and throughput. IO characteristics of database files are shown in the following table:
    IOPS per file 500 2300 5000 7500 7500 7500
    Throughput per file 100 MiB/s 150 MiB/s 200 MiB/s 250 MiB/s 250 MiB/s 250 MiB/s

    If you notice high IO latency on some database file or you see that IOPS/throughput is reaching the limit, you might improve performance by increasing the file size.

    There is also an instance-level limit on the max log write throughput (see the previous table for values, for example 22 MiB/s), so you may not be able to reach the max file throughout on the log file because you are hitting the instance throughput limit.

    Hope this helps.

    If not please let us know more details about the question.

    Thanks