Resource Governance - Sql Managed Instance - dm_io_virtual_file_stats

Question

Hi,

good day
we are running Sql on Azure Managed Instance General Purpose.

I am trying to understand the IO stats on a particular DB and so i run the below query:

Select

``[name],
[num_of_reads],
[num_of_writes],
[io_stall_read_ms],
[io_stall_write_ms],
[io_stall_queued_read_ms],
[io_stall_queued_write_ms]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL)

The answer i get is (I have transposed the values below here)

name DbFile1
num_of_reads 11622507
num_of_writes 1570477
io_stall_read_ms 3828739147
io_stall_write_ms 318531144
io_stall_queued_read_ms 3771506603
io_stall_queued_write_ms 313060033

What is io_stall_queued_read_ms mean? the doc say it is introduced by resource governance - I am not very clear on this - please can someone explain.
Does io_stall_read_ms is inclusive of io_stall_queued_read_ms?

we have not enabled any resource governor so not sure how this comes into play.
Please help clarify

Thanks in advance

Answer 1

Hi @MS Prog Thank you for posting your Question on Microsoft Q&A.

There is a correlation between the two: actual_read_time_ms = io_stall_read_ms - io_stall_queued_read_ms

The full explanation could come something like this :

You have 1000 IO that you want to perform in 1 second so you could get 1000 IOPS.

This means that every single IO will be in the queue and the 1000th will have to wait for a whole second; it is queued 999ms.

So, stalled is the time for read + time it was queued. And if you want to get the actual read time, you have to substract the previous two (the first equation).

Hope that helps

Regards,
Oury