question

Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 asked AmeliaGu-msft answered

IO reads in MB

This screenshot has been sent by one of my senior DBA to me but he never send me scripts . Did anyone know how does this calculated IO reads in MB for database . As I know IO is in miliseconds but this one is in MB . Anyone can help me to understand this better 88286-image.png


sql-server-general
image.png (186.0 KiB)
· 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 @Mario2286-5314,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered Mario2286-5314 commented

Hi @Mario2286-5314,

I think the query gets information from the sys.dm_io_virtual_file_stats.
The IO_Reads_In_MB = CAST(SUM(num_of_bytes_read)/(1024*1024) AS DECIMAL(12, 2))
The IO_Writes_In_MB= CAST(SUM(num_of_bytes_written)/1048576 AS DECIMAL(12, 2))
The IO_In_MB= CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2))
Please try:

 SELECT DB_NAME(database_id) AS DBName,
 CAST(SUM(num_of_bytes_read)/1048576 AS DECIMAL(12, 2)) As IO_Reads_In_MB,
 CAST(SUM(num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS IO_Writes_In_MB,
 CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) 
 GROUP BY database_id

Hope it helps.
Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 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.

Number of bytes read means pages read by SQL server multiply with 8kb isit?

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Mario2286-5314 commented

Maybe this is the total number of bytes that were read, written, and the sum, in megabytes, during some period of time. Look large.

· 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.

Do you mean it might be pages read from disk, then multiply with 8kb

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @Mario2286-5314,

Number of bytes read means pages read by SQL server multiply with 8kb is it?

The num_of_bytes_read means total number of bytes read on the data and log files. Please refer to sys.dm_io_virtual_file_stats.

Best Regards,
Amelia


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.