locked
Read,write latency differences between datafiles. RRS feed

  • Question

  • Hello Friends,

    I was looking read,write latencies of datafile by using sys.dm_io_virtual_file_stats inline function. I realized that,i have big differences between datafiles in terms of latencies even they are same directory. Why is the reason of that ? Since they are in same directory,i approximatly expected to see same number.

    Thanks,Arif

    Friday, December 11, 2015 4:21 PM

Answers

  • That is the point that make me confused.The all data files are same storage with same file groups.Why did have different latencies in terms of databases.

    The data access patterns may be different for different database files.  For example, the average transfer size for OLTP database reads may be about 8K and have a lower latency compared to a reporting workload that may have a much higher average transfer size and higher latency due to read-aheads.  Also consider that file IO might not be even over time for each database.  The metrics should be comparable for files in the same database, though.

    Try the tweaked version of Ahsan's query below.

    SELECT
    	 DB_NAME(io.database_id) AS DatabaseName
    	,mf.physical_name
    	,CASE WHEN io.num_of_reads > 0 THEN io.num_of_bytes_read/io.num_of_reads ELSE 0 END AS AvgReadTransfer
    	,CASE WHEN io.num_of_writes > 0 THEN io.num_of_bytes_written/io.num_of_writes ELSE 0 END AS AvgWriteTransfer
    	,CASE WHEN io.num_of_reads > 0 THEN io.io_stall_read_ms/io.num_of_reads ELSE 0 END AS AvgReadStall
    	,CASE WHEN io.num_of_writes > 0 THEN io.io_stall_write_ms/io.num_of_writes ELSE 0 END AS AvgWriteStall
    	,*
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) io
    JOIN sys.master_files mf ON mf.database_id = io.database_id
    	AND mf.file_id = io.file_id;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Eric__Zhang Thursday, December 24, 2015 8:43 AM
    Tuesday, December 15, 2015 3:38 AM

All replies

  • You can see the below :

    How to Test Your Storage with CrystalDiskMark


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sunday, December 13, 2015 5:58 PM

  • SQL Server needs to read and write to the disk. All writes (inserts, updates, deletes) must be written to disk. Queries always return data from the in-memory cache (the buffer pool) but the cache may not contain the desired data and has to be read from disk. Understanding if the IO is a bottleneck for your performance is a necessary step in any performance investigation. SQL Server collects, aggregates and exposes information about every data and log IO request. First thing I like to look at is sys.dm_io_virtual_file_stats. This DMV exposes the number of writes and reads on each file for every database in the system, along with the aggregated read and write IO ‘stall’ times. Stall times are the total time tasks had to block waiting for transfer of data to and from disk.

    select db_name(io.database_id) as database_name,
    	mf.physical_name as file_name,
    	io.*
    from sys.dm_io_virtual_file_stats(NULL, NULL) io
    join sys.master_files mf on mf.database_id = io.database_id
    	and mf.file_id = io.file_id
    order by (io.num_of_bytes_read + io.num_of_bytes_written) desc;


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Sunday, December 13, 2015 6:01 PM
  • I was looking read,write latencies of datafile by using sys.dm_io_virtual_file_stats inline function. I realized that,i have big differences between datafiles in terms of latencies even they are same directory. Why is the reason of that ? Since they are in same directory,i approximatly expected to see same number.

    Are the data files part of the same database and filegroup?  If not, the differences in latencies may be due to different access patterns.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, December 13, 2015 9:57 PM
  • Hello Dan,

    That is the point that make me confused.The all data files are same storage with same file groups.Why did have different latencies in terms of databases.

    Monday, December 14, 2015 8:59 AM
  • That is the point that make me confused.The all data files are same storage with same file groups.Why did have different latencies in terms of databases.

    The data access patterns may be different for different database files.  For example, the average transfer size for OLTP database reads may be about 8K and have a lower latency compared to a reporting workload that may have a much higher average transfer size and higher latency due to read-aheads.  Also consider that file IO might not be even over time for each database.  The metrics should be comparable for files in the same database, though.

    Try the tweaked version of Ahsan's query below.

    SELECT
    	 DB_NAME(io.database_id) AS DatabaseName
    	,mf.physical_name
    	,CASE WHEN io.num_of_reads > 0 THEN io.num_of_bytes_read/io.num_of_reads ELSE 0 END AS AvgReadTransfer
    	,CASE WHEN io.num_of_writes > 0 THEN io.num_of_bytes_written/io.num_of_writes ELSE 0 END AS AvgWriteTransfer
    	,CASE WHEN io.num_of_reads > 0 THEN io.io_stall_read_ms/io.num_of_reads ELSE 0 END AS AvgReadStall
    	,CASE WHEN io.num_of_writes > 0 THEN io.io_stall_write_ms/io.num_of_writes ELSE 0 END AS AvgWriteStall
    	,*
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) io
    JOIN sys.master_files mf ON mf.database_id = io.database_id
    	AND mf.file_id = io.file_id;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Eric__Zhang Thursday, December 24, 2015 8:43 AM
    Tuesday, December 15, 2015 3:38 AM
  • Hi Arif

    It depends upon what your underlying storage is. The overhead to do a write could be much more than simply "move the disk head to the correct block and write a page of data". 

    I'd also suggest looking at your PerfMon counters and checking what is the Avg Bytes per read and write and compare that to the Avg Seconds per read and write.


    Martin Cairney SQL Server MVP


    Tuesday, December 15, 2015 4:21 AM