I received following question:
“How do we know how many pending IO requests are there for database files (.mdf, .ldf) individually?”
Very interesting question and indeed answer is very interesting as well.
Here is the quick script which I use to find the same. It has to be run in the context of the database for which you want to know pending IO statistics.
USE DATABASE
GO
SELECT vfs.database_id, df.name, df.physical_name
,vfs.FILE_ID, ior.io_pending
FROM sys.dm_io_pending_io_requests ior
INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs
ON (vfs.file_handle = ior.io_handle)
INNER JOIN sys.database_files df ON (df.FILE_ID = vfs.FILE_ID)
I keep this script handy as it works like magic every time. If you use any other script please post here and I will post it with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
To see the slowest data file, I join the sys.dm_io_virtual_file_stats tabular function with the sys.master_files system view and calculate the average wait time.
SELECT smf.name AS InternalName
, smf.physical_name AS PhysicalFile
, smf.type_desc AS FileType
, CONVERT(NUMERIC(15,0), ((io_stall_read_ms / num_of_reads)
+ (io_stall_write_ms / num_of_writes)) / 2) AS AverageWait_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) svs
INNER JOIN sys.master_files smf ON svs.database_id = smf.database_id
AND svs.file_id = smf.file_id
ORDER BY AverageWait_ms DESC
Pinal, I’m using a different query that does outer joins on dm_io_pending_io_requests and dm_io_virtual_file_stats, and I’m finding slowdowns in unknown files – the io_handle doesn’t match up. Any ideas?
select
db_name(vfs.database_id) as database_name,
mf.name as logical_file_name,
pr.io_type,
io_handle,
sum(pr.io_pending_ms_ticks) as sum_io_pending_ms_ticks,
count(*) as [count]
from
sys.dm_io_pending_io_requests pr
left join sys.dm_io_virtual_file_stats(null, null) vfs on vfs.file_handle = pr.io_handle
left join sys.master_files mf on mf.database_id = vfs.database_id
and mf.file_id = vfs.file_id
where
pr.io_pending = 1
group by
db_name(vfs.database_id),
mf.name,
pr.io_type, io_handle
i like to know , how to find total time taken by io in sql server.i need to find this by query.then what is the difference between elaspsed time and io time.