Blog Post

SQL Server Blog
1 MIN READ

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

Pedro Lopes's avatar
Pedro Lopes
Icon for Microsoft rankMicrosoft
Mar 23, 2019
First published on MSDN on Mar 08, 2012

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.

Updated Mar 23, 2019
Version 2.0
No CommentsBe the first to comment