Thursday, May 9, 2013

Dumping IO Stats to Powershell... Get em Girl!

Hello All,

A 2 part post this month, i guess i finally removed head from arse and got around to posting updates. First a super useful query for IO stats of log and data files. Please note these are at file level. This may exist already and im sure there are better ways to do this, but since i have been going through a ton of logs and data files at work recently, i figured it was worth a post.


SELECT DB_NAME(tblFileStat.database_id) AS databaseName ,tblFileStat.file_id ,tblMasterFile.physical_name ,tblIOPending.io_pending ,tblFileStat.io_stall_read_ms ,tblFileStat.io_stall_write_ms ,tblFileStat.file_handle ,tblIOPending.NumberofIOPendingRowsFROM sys.dm_io_virtual_file_stats(NULL, NULL) tblFileStatINNER JOIN ( SELECT io_handle , MAX(io_pending) AS io_pending ,COUNT(*) AS NumberofIOPendingRows FROM sys.dm_io_pending_io_requests GROUP BY io_handle) tblIOPendingON tblFileStat.file_handle = tblIOPending.io_handleINNER JOIN sys.master_files tblMasterFileON tblFileStat.database_id = tblMasterFile.database_idAND tblFileStat.file_id = tblMasterFile.file_idORDER BY tblFileStat.io_stall_read_ms DESC


Second for a little bit of comic relief a list of DBA jargon i am entitling WTF did he just say. Note I have never in 15 years heard any of these phrases used but I still think they are pretty funny.

No comments:

Post a Comment