SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 dm_io_virtual_file_stats
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/02/2013 :  03:09:43  Show Profile  Reply with Quote
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

USA
36631 Posts

Posted - 02/03/2013 :  14:01:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
36631 Posts

Posted - 02/03/2013 :  14:02:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 02/03/2013 14:02:39
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/04/2013 :  02:04:20  Show Profile  Reply with Quote
Thanks tkizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000