Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 dm_io_virtual_file_stats

Author  Topic 

shaggy
Posting Yak Master

248 Posts

Posted - 2013-02-02 : 03:09:43
select * from dm_io_virtual_file_stats(dbid(),null)
avg_io_stall_ms for some of my DB files by average it is 400 ms

is it possible that there is either a disk bottleneck or that high reads and writes are occurring on that drive

All DB files are in separate drives.

Is it like (Average Disk sec/read or write )> 20 ms is a IO botleneck.

Pls can anyone explain in this whether am in a right track in identifying problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-02-03 : 14:01:23
Over 12ms is a bottleneck for average disk sec/read or write. Validate that the DMV is showing an I/O bottleneck by checking the PerfMon counters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-02-03 : 14:02:23
Spikes are okay, especially during a checkpoint. It's the sustained average over 12ms that would be worrisome. And really writes should be even lower for a threshold.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

shaggy
Posting Yak Master

248 Posts

Posted - 2013-02-04 : 02:04:20
Thanks tkizer
Go to Top of Page
   

- Advertisement -