查找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

 

如图：