查找SQL SERVER 2005 IO过高的数据库

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

 

如图：