Dumping IO Stats to powershell, and WTF did he just say.
Posted by John Trumbul on Sunday, October 7, 2012
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.
[code]
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.NumberofIOPendingRows
FROM sys.dm_io_virtual_file_stats(NULL, NULL) tblFileStat
INNER JOIN ( SELECT io_handle , MAX(io_pending) AS io_pending ,
COUNT(*) AS NumberofIOPendingRows FROM sys.dm_io_pending_io_requests
GROUP BY io_handle) tblIOPending
ON tblFileStat.file_handle = tblIOPending.io_handle
INNER JOIN sys.master_files tblMasterFile
ON tblFileStat.database_id = tblMasterFile.database_id
AND tblFileStat.file_id = tblMasterFile.file_id
ORDER BY tblFileStat.io_stall_read_ms DESC
[/code]
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. http://www.brentozar.com/archive/2012/10/learn-speak-dba-slang-terms/
Until Next Time,
JT++
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.
[code]
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.NumberofIOPendingRows
FROM sys.dm_io_virtual_file_stats(NULL, NULL) tblFileStat
INNER JOIN ( SELECT io_handle , MAX(io_pending) AS io_pending ,
COUNT(*) AS NumberofIOPendingRows FROM sys.dm_io_pending_io_requests
GROUP BY io_handle) tblIOPending
ON tblFileStat.file_handle = tblIOPending.io_handle
INNER JOIN sys.master_files tblMasterFile
ON tblFileStat.database_id = tblMasterFile.database_id
AND tblFileStat.file_id = tblMasterFile.file_id
ORDER BY tblFileStat.io_stall_read_ms DESC
[/code]
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. http://www.brentozar.com/archive/2012/10/learn-speak-dba-slang-terms/
Until Next Time,
JT++