How to test Random/sequential ratio?

  • i1888

    SSCommitted

    Points: 1806

    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: 6518

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply