The SQL Swiss Army Knife #3 – View I/O per file – Updated

Hello all,

Here is another one focusing on SQL scripts that may help on everyday DBA tasks, following the series "SQL Swiss Army Knife".

This script will return, very promptly, the overall I/O statistics for all databases in your server, ordered by stalled I/O, and is based on the sys.dm_io_virtual_file_stats DMV. This DMV returns I/O statistics for data and log files and replaces the old fn_virtualfilestats function. Be aware that this requires the VIEW_SERVER_STATE permission. There’s all sort of uses for this information, like helping with drafting a server consolidation strategy, or pointing to eventual need to check perf counters if I/O is waiting more than expected.

Its output will resemble this:


Download script here:view_IO_Stats_per_file.sql

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

Comments (1)

  1. Andrew Craven says:

    Using this script, it is possible to see IO Stall Percentages higher than 100%

    In one actual run I received values for io_stall_pct_of_overall_sample:
    182, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, etc …
    Presumably due to Parallelization. This does weaken the assumption regarding sample_ms but does not detract from the usefulness of the script.

Skip to main content