Hello everyone,
I am checking some ratio numbers for our system engineers, those are
Read/write ratio?
Random/sequential ratio?
Read/write block size?
For Read/write ratio, I am using below query,
SELECT
m.type_desc
, CEILING(sum(num_of_bytes_read*1.0) / (sum(num_of_bytes_read*1.0) + sum(num_of_bytes_written*1.0)) * 100) AS 'Read %'
, CAST((sum(v.size_on_disk_bytes) / 1024.0 / 1024 / 1024) AS MONEY) AS 'FileSizeGB'
, CAST((sum(v.num_of_bytes_read) / 1024.0 / 1024 / 1024) AS MONEY) AS 'ReadGB'
, CAST((sum(v.num_of_bytes_written) / 1024.0 / 1024 / 1024) AS MONEY) AS 'WrittenGB'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) v
INNER JOIN sys.master_files m on m.database_id = v.database_id AND v.file_id = m.file_id
where DB_NAME(m.database_id) like 'xxx'
and m.type_desc = 'ROWS'
--and m.type_desc = 'LOG'
group by m.type_desc
Random/sequential ratio, I googled but cannot find a similar query to get the result, does anyone know the answer?
Thanks very much!
Albert