SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interpreting the results of sys.dm_io_virtual_file_stats


Interpreting the results of sys.dm_io_virtual_file_stats

Author
Message
david.gugg
david.gugg
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2446 Visits: 1084
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!


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search