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

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
13/01/2014 - Latest update on the AdaptiveIndexDefrag procedure v1.5.7. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

18/02/2014 - Updates to Maintenance plan deployment scripts available HERE

16/04/2014 - The SQL Swiss Army Knife Series Index

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

  • Comments 0

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:

image

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.

Social Media Sharing
|
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post