How to test Random/sequential ratio?

• i1888

  SSCommitted

  Points: 1970

  More actions

  August 31, 2015 at 2:03 am

  #311261

  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

• arr.nagaraj

  SSCertifiable

  Points: 6528

  More actions

  September 10, 2015 at 3:55 am

  #1825625

  From SQL layer, its hard to track a sequential or random I/O. Refer to the link below for a good explanation.

  http://sqlblog.com/blogs/linchi_shea/archive/2011/04/01/there-are-no-sequential-or-random-disk-i-os.aspx

  Regards,
  Raj

  http://Strictlysql.blogspot.com