查询数据库IO相关脚本

IO请求的等待和挂起，数据库引擎记录对数据文件和日志文件的IO操作，缓存到函数：sys.dm_io_virtual_file_stats，对于数据文件，数据的物理读操作更为重要；对于日志文件，数据的读写操作都重要：

• io_stall_read_ms：等待读操作的时间
• io_stall_write_ms：等待写操作的时间

如果硬盘繁忙，数据库引擎发送的IO请求，可能会被IO子系统挂起（pending），数据库引擎把pending的IO请求缓存到视图：sys.dm_io_pending_io_requests，

• io_pending：指定是否有IO请求挂起或完成

1，查看数据库文件的IO和等待IO完成的时间

select db_name(vfs.database_id) as db_name,
    --vfs.file_id,
    mf.name as file_name,
    mf.type_desc as file_type,
    vfs.sample_ms/1000/60/60 as sample_h,
    vfs.io_stall_read_ms/vfs.num_of_reads as avg_stall_read_ms,
    vfs.io_stall_write_ms/vfs.num_of_writes as avg_stall_write_ms,

    vfs.num_of_reads as physical_reads,
    vfs.num_of_bytes_read/vfs.num_of_reads/1024 as avg_read_kb,
    vfs.num_of_writes as physical_writes,
    vfs.num_of_bytes_written/vfs.num_of_writes/1024 as avg_written_kb,
    cast(vfs.size_on_disk_bytes/1024/1024/1024.0 as decimal(10,2)) as disk_size_gb,
    --cast(mf.size/1024*8/1024.0 as decimal(10,2)) as file_size_gb,
    vfs.file_handle
from sys.master_files mf 
cross apply sys.dm_io_virtual_file_stats(mf.database_id,mf.file_id) as vfs
where mf.database_id=db_id()  --current db
order by avg_stall_read_ms desc ,avg_stall_write_ms desc

2，查看pending（挂起）的IO请求

select db_name(vfs.database_id) as db_name,
    --vfs.file_id,
    mf.name as file_name,
    pr.io_type,
    sum(pr.io_pending_ms_ticks) as io_pending_ms,
    pr.io_pending
from sys.dm_io_virtual_file_stats(null,null) vfs
inner join sys.dm_io_pending_io_requests as pr
    on vfs.file_handle=pr.io_handle
inner join sys.master_files mf
    on vfs.database_id=mf.database_id
        and vfs.file_id=mf.file_id
group by vfs.database_id,
    mf.file_id,
    mf.name,
    pr.io_type,
    pr.io_pending
order by vfs.database_id,
    mf.name

3，计划缓存中的逻辑写排名

select 
    p.name as sp_name
    ,s.total_logical_reads
    ,s.total_logical_writes
    ,s.total_physical_reads
    ,s.total_elapsed_time
    ,s.total_worker_time
    ,s.cached_time
    ,s.execution_count
    ,s.type
    ,s.type_desc
from sys.procedures p
inner join sys.dm_exec_procedure_stats s
    on p.object_id=s.object_id
where s.database_id=DB_ID()
    and s.total_logical_writes>0
order by s.total_logical_writes

参考文档：性能调优3：硬盘IO性能