各个数据库的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

 

转载于:https://www.cnblogs.com/qanholas/archive/2012/01/05/2313058.html