Interpreting the results of sys.dm_io_virtual_file_stats

• david.gugg

  SSCertifiable

  Points: 6030

  More actions

  May 7, 2014 at 1:40 pm

  #307146

  Hi

  I am using the following query to collect latency statistics:

  SELECT

  [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,

  [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,

  [Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,

  [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,

  [AvgBPerWrite] = CASE WHEN [io_stall_write_ms] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,

  [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,

  LEFT ([mf].[physical_name], 2) AS [Drive],

  DB_NAME ([vfs].[database_id]) AS [DB],

  SUBSTRING(mf.physical_name, len(mf.physical_name)-CHARINDEX('\',REVERSE(mf.physical_name))+2, 100),

  CASE vfs.file_id WHEN 2 THEN 1 ELSE 0 END as islog

  FROMsys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]

  INNER JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id]

  I am seeing some high results for tempdb, but the latency is showing for each file:

  ReadLatencyWriteLatencyTotalLatencyDBPhysicalName

  2 551 448 tempdbtempdb16.ndf

  2 551 447 tempdbtempdb15.ndf

  3 554 450 tempdbtempdb14.ndf

  2 551 448 tempdbtempdb13.ndf

  3 554 450 tempdbtempdb12.ndf

  2 553 449 tempdbtempdb11.ndf

  2 551 448 tempdbtempdb10.ndf

  3 549 446 tempdbtempdb9.ndf

  3 548 445 tempdbtempdb8.ndf

  2 550 447 tempdbtempdb7.ndf

  3 548 445 tempdbtempdb6.ndf

  3 550 447 tempdbtempdb5.ndf

  2 551 447 tempdbtempdb4.ndf

  3 551 447 tempdbtempdb3.ndf

  3 551 448 tempdbtempdb2.ndf

  2 551 445 tempdbtempdb.mdf

  My question is this: Is the latency for tempdb the sum of the latency for each file, or the average of all tempdb files? So 10 seconds or .5 seconds? I gotta think it's the average, but I just wanted to be sure.

  Thank you!

  
  [font="Tahoma"]Personal blog relating fishing to database administration:[/font]
  [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]