--Pending I/O requests by file
--Ensure the hard limit of outstanding write I/Os for each database’s transaction log is not being reached
SELECT
DB_NAME(mf.database_id) AS [Database], mf.Physical_Name as [File Physical],
r.IO_Pending, r.IO_Pending_ms_ticks, UPPER(r.IO_Type) as [IO Type], Type_Desc, fs.Num_Of_Reads, fs.Num_Of_Writes
FROM sys.dm_io_pending_io_requests AS r
INNER JOIN sys.dm_io_virtual_file_stats(null,null) AS fs ON r.io_handle = fs.file_handle
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
ORDER BY r.io_pending, r.io_pending_ms_ticks DESC;
--Aggregate Pending I/O's
SELECT
COUNT (*) AS [Pending IOs],
DB_NAME ([vfs].[database_id]) AS [Database],
[mf].[name] AS [File Logical],
[mf].[type_desc] AS [File Type],
SUM ([iop].[io_pending_ms_ticks]) AS [TotalStall]
FROM sys.dm_io_pending_io_requests AS [iop]
INNER JOIN sys.dm_io_virtual_file_stats (NULL, NULL) AS [vfs] ON [vfs].[file_handle] = [iop].[io_handle]
INNER JOIN sys.master_files AS [mf] ON [mf].[database_id] = [vfs].[database_id] AND [mf].[file_id] = [vfs].[file_id]
WHERE
[iop].[io_pending] = 1
GROUP BY [vfs].[database_id], [mf].[name], [mf].[type_desc]
ORDER BY [vfs].[database_id], [mf].[name];
--Pending IO per Active Request
SELECT
req.Session_ID, req.Command, req.Status, Wait_Type, Last_Wait_type, Wait_Resource, Blocking_Session_ID, Wait_time,
ST.Text, iop.io_type, iop.io_pending, iop.scheduler_address, iop.io_handle, oss.scheduler_id, oss.cpu_id,
oss.pending_disk_io_count, req.cpu_time, io_offset, sql_handle, query_hash, query_plan_hash
FROM sys.dm_io_pending_io_requests iop
INNER JOIN sys.dm_os_schedulers oss ON iop.scheduler_address = oss.scheduler_address
INNER JOIN sys.dm_exec_requests AS req ON oss.scheduler_id = req.Scheduler_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
|
|
|
|
|
|