question

MSProg-4478 avatar image
0 Votes"
MSProg-4478 asked OuryBa-MSFT commented

Resource Governance - Sql Managed Instance - dm_io_virtual_file_stats


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




azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OuryBa-MSFT avatar image
1 Vote"
OuryBa-MSFT answered OuryBa-MSFT commented

Hi @MSProg-4478 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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MSProg-4478 Checking if the above answer was helpful, If yes please mark answer as accepted. Otherwise let us know how we ca better assist here.

Regards,
Oury

0 Votes 0 ·