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

最新推荐文章于 2020-06-15 12:19:58 发布

diniushu8825

最新推荐文章于 2020-06-15 12:19:58 发布

阅读量165

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/luckjun/archive/2011/10/10/2205210.html

版权

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
--ORDER BY [IO_stall_pct%] desc

转载于:https://www.cnblogs.com/luckjun/archive/2011/10/10/2205210.html