查询SQL启动以来数据库的IO读写情况 [数据库 SQLServer]

脚本:

SELECT  db.name AS 数据库,

        f.fileid AS 文件Id,
        f.filename AS 文件路径,
        i.num_of_reads AS 读取次数,
        i.num_of_bytes_read 读取总字节数,
        i.io_stall_read_ms '等待读取时间(毫秒)',
        i.num_of_writes AS 写入次数,
        i.num_of_bytes_written AS 写入总字节数,
        i.io_stall_write_ms AS '等待写入时间(毫秒)',
        i.io_stall AS 等待IO完成总时间,
        i.size_on_disk_bytes 磁盘占用字节数
FROM    sys.databases db
        INNER JOIN sys.sysaltfiles f ON db.database_id = f.dbid

        INNER JOIN sys.dm_io_virtual_file_stats(NULL,NULL) i ON i.database_id = f.dbid AND i.file_id = f.fileid 

结果:


查看当前处于挂起状态的IO请求:

SELECT  t1.database_id,t1.file_id,t1.io_stall,t2.io_pending_ms_ticks,t2.scheduler_address
FROM    sys.dm_io_virtual_file_stats(NULL,NULL) t1,
            sys.dm_io_pending_io_requests t2
WHERE   t1.file_handle = t2.io_handle;