Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Interpreting the results of sys.dm_io_virtual_file_stats Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 1:40 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:10 AM
Points: 421, Visits: 442
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
FROM sys.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:
ReadLatency	WriteLatency	TotalLatency	DB	PhysicalName
2 551 448 tempdb tempdb16.ndf
2 551 447 tempdb tempdb15.ndf
3 554 450 tempdb tempdb14.ndf
2 551 448 tempdb tempdb13.ndf
3 554 450 tempdb tempdb12.ndf
2 553 449 tempdb tempdb11.ndf
2 551 448 tempdb tempdb10.ndf
3 549 446 tempdb tempdb9.ndf
3 548 445 tempdb tempdb8.ndf
2 550 447 tempdb tempdb7.ndf
3 548 445 tempdb tempdb6.ndf
3 550 447 tempdb tempdb5.ndf
2 551 447 tempdb tempdb4.ndf
3 551 447 tempdb tempdb3.ndf
3 551 448 tempdb tempdb2.ndf
2 551 445 tempdb tempdb.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!
Post #1568677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse