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

Published Mar 23 2019 12:43 PM 168 Views
Microsoft
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.

%3CLINGO-SUB%20id%3D%22lingo-sub-384071%22%20slang%3D%22en-US%22%3EThe%20SQL%20Swiss%20Army%20Knife%20%233%20-%20View%20I%2FO%20per%20file%20-%20Updated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384071%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Mar%2008%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%20is%20another%20one%20focusing%20on%20SQL%20scripts%20that%20may%20help%20on%20everyday%20DBA%20tasks%2C%20following%20the%20series%20%22SQL%20Swiss%20Army%20Knife%22.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20script%20will%20return%2C%20very%20promptly%2C%20the%20overall%20I%2FO%20statistics%20for%20all%20databases%20in%20your%20server%2C%20ordered%20by%20stalled%20I%2FO%2C%20and%20is%20based%20on%20the%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fenu%2Flibrary%2Fms190326.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20sys.dm_io_virtual_file_stats%20%3C%2FA%3E%20DMV.%20This%20DMV%20returns%20I%2FO%20statistics%20for%20data%20and%20log%20files%20and%20replaces%20the%20old%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fenu%2Flibrary%2Fms187309.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20fn_virtualfilestats%20%3C%2FA%3E%20function.%20Be%20aware%20that%20this%20requires%20the%20VIEW_SERVER_STATE%20permission.%20There%E2%80%99s%20all%20sort%20of%20uses%20for%20this%20information%2C%20like%20helping%20with%20drafting%20a%20server%20consolidation%20strategy%2C%20or%20pointing%20to%20eventual%20need%20to%20check%20perf%20counters%20if%20I%2FO%20is%20waiting%20more%20than%20expected.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIts%20output%20will%20resemble%20this%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97569iC50DFAA9A406779A%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22scid%3A8eb9d37f-1541-4f29-b6f4-1eea890d4876%3Aea6a2e3c-1fdb-4dcd-bbc9-25685f75dd00%22%3E%3CBR%20%2F%3E%3CDIV%3EDownload%20script%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2FMSDNBlogsFS%2Fprod.evol.blogs.msdn.com%2FCommunityServer.Blogs.Components.WeblogFiles%2F00%2F00%2F01%2F13%2F22%2Fmetablogapi%2F4353.view_IO_Stats_per_file_7E0994B0.sql%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20view_IO_Stats_per_file.sql%3C%2FA%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3EUntil%20next%20time!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3EDisclaimer%3A%20%3C%2FB%3E%20I%20hope%20that%20the%20information%20on%20these%20pages%20is%20valuable%20to%20you.%20Your%20use%20of%20the%20information%20contained%20in%20these%20pages%2C%20however%2C%20is%20at%20your%20sole%20risk.%20All%20information%20on%20these%20pages%20is%20provided%20%22as%20-is%22%2C%20without%20any%20warranty%2C%20whether%20express%20or%20implied%2C%20of%20its%20accuracy%2C%20completeness%2C%20fitness%20for%20a%20particular%20purpose%2C%20title%20or%20non-infringement%2C%20and%20none%20of%20the%20third-party%20products%20or%20information%20mentioned%20in%20the%20work%20are%20authored%2C%20recommended%2C%20supported%20or%20guaranteed%20by%20Ezequiel.%20Further%2C%20Ezequiel%20shall%20not%20be%20liable%20for%20any%20damages%20you%20may%20sustain%20by%20using%20this%20information%2C%20whether%20direct%2C%20indirect%2C%20special%2C%20incidental%20or%20consequential%2C%20even%20if%20it%20has%20been%20advised%20of%20the%20possibility%20of%20such%20damages.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384071%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Mar%2008%2C%202012%20Hello%20all%2CHere%20is%20another%20one%20focusing%20on%20SQL%20scripts%20that%20may%20help%20on%20everyday%20DBA%20tasks%2C%20following%20the%20series%20%22SQL%20Swiss%20Army%20Knife%22.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384071%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerTiger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 12:43 PM
Updated by: