查询sql server数据库IO

cheng029

with DB_IO as (
select
    db_name(a.database_id) as [dbname,
    case when b.type_desc='LOG' then 'LogFile' else 'DataFile' end as FileType,
    num_of_reads,
    num_of_writes,
    sum(a.io_stall) as io_stall,
    sum(num_of_bytes_read+num_of_bytes_written) as TotalIOBytes
from sys.dm_io_virtual_file_stats(null,null) a join sys.master_files b
on a.database_id=b.database_id and a.file_id=b.file_id
group by db_name(a.database_id),b.type_desc,num_of_reads,num_of_writes
)
select
    dbname,
    FileType,
    num_of_reads,
    num_of_writes,
    cast(1. * TotalIOBytes/(1024*1024) as decimal(12,2)) as IO_MB,
    cast(1. * io_stall/1000 as decimal(12,2)) as IO_stall_s,
    cast(100. * io_stall/sum(io_stall) over() as decimal(10,2)) as [IO_stall_pct%,--占总IO的百分比
    row_number() over(order by io_stall desc) as rn
from DB_IO

鄂破机看

我本非随便的人，但如果你想随便，那我就随你的便好啦！

sxz6024

结果的话，看不懂

smith88

.其实我是一个天才，可惜天妒英才！

沈阳格力专卖店

路过，学习下

zhltom

路过，学习下

吉佳

如果有一双眼睛陪我一同哭泣，就值得我为生命受苦。