May 7, 2014 at 1:40 pm
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!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply