各个数据库的io比例


SELECT  CASE df.database_id
          WHEN DB_ID('distribution') THEN 'distDB'
          WHEN DB_ID('tempdb') THEN 'tempdb'

            --add other needed database
          ELSE 'otherDBs'
        END AS dbName ,
        CASE df.type
          WHEN 0 THEN 'datafile'
          WHEN 1 THEN 'logfile'
          ELSE 'otherfile'
        END AS [type] ,
        SUM(num_of_reads) AS reads ,
        SUM(num_of_writes) AS writes
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) fs
        INNER JOIN sys.master_files df ON fs.database_id = df.database_id
                                          AND fs.file_id = df.file_id
GROUP BY CASE df.database_id
           WHEN DB_ID('distribution') THEN 'distDB'
           WHEN DB_ID('tempdb') THEN 'tempdb'

            --add other needed database
           ELSE 'otherDBs'
         END ,
        df.[type]
ORDER BY reads DESC

 

posted @ 2012-01-05 14:06  qanholas  阅读(338)  评论(0编辑  收藏  举报