June 19, 2009 at 1:50 pm
I have developed some functionality that polls sys.dm_io_virtual_file_stats at regular intervals for all database files in an instance, calculates differentials from one polled snapshot to the next and determines, among other things, the I/O Stalls (ms) per I/O operation (read/write).
I'm getting values of "I/O Stalls (ms) per I/O operation" frequently over 20, reaching up to 140 for one of my database files.
Based on link http://www.novicksoftware.com/Articles/sql-server-io-statistics-page-3.htm, anything over 20 is cause for concern about the disk subsystem.
Is this a good guideline to go by in terms of interpreting these results?
Any other hints on how I should interpret these results?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 19, 2009 at 10:18 pm
You have an indication that at some points in time, your disk sub-system is unable to keep up with I/O requests from SQL Server. Whether this is a severe problem or not depends on how long it lasts, how frequently it occurs, and whether it is having any measurable impact on query performance.
If the problem is serious, I would look into ways of improving the capacity of the I/O system, either by upgrading hardware, or distributing the load across more physical disks for example. If the waits are transient and associated with something like checkpoint activity, and if the performance of the system as a whole is fine, I would probably not worry too much about it - but bear it in mind for the future.
You could also look at the sys.dm_io_pending_io_requests view to gather more information about the problem.
Paul
June 20, 2009 at 6:52 pm
Paul White (6/19/2009)
You have an indication that at some points in time, your disk sub-system is unable to keep up with I/O requests from SQL Server. Whether this is a severe problem or not depends on how long it lasts, how frequently it occurs, and whether it is having any measurable impact on query performance.If the problem is serious, I would look into ways of improving the capacity of the I/O system, either by upgrading hardware, or distributing the load across more physical disks for example. If the waits are transient and associated with something like checkpoint activity, and if the performance of the system as a whole is fine, I would probably not worry too much about it - but bear it in mind for the future.
You could also look at the sys.dm_io_pending_io_requests view to gather more information about the problem.
Paul
Thank you, I will look at sys.dm_io_pending_io_requests.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply