用好三张 DMV， 检测 80% 的性能故障

很多同学遇到 SQL 性能问题，要么毫无头绪干着急，要么手忙脚乱瞎忙活。

可谁不是从小工到大师呢，关键是要找到那条道。

如果你也在寻找这条道，不妨一起来看看这里。

用好三张 DMV， 检测 80% 的性能故障。

-sys.dm_os_wait_stats
-sys.dm_io_virtual_file_stats(NULL,NULL)
-sys.dm_os_performance_counters

前面有两篇文章已经对 sys.dm_os_wait_stats 做了比较详细的描述与扩展，贴在这里，有兴趣再回顾回顾！

揭秘数据库性能杀手 - 等待
揭秘数据库性能杀手 - 等待（赋脚本）

通过上面的两篇文章，大家可以穷尽自己的想象力，多做一些主题分析，相信能掌握不少有趣有用的诊断技术。

接下来我们来看另外两张 DMV，其中一张是个表级函数 sys.dm_io_virtual_file_stats

通过 sys.dm_io_virtual_file_stats可以看到数据库文件级别的 IO 情况：

-某一段时间内的写和读的量，大小变化，异常峰值；
-磁盘的写和读延迟；读写延迟严重的，就需要配合performance counter的趋势线，判断读写延迟是否是因为整个操作系统的作业量过大造成的

*延迟有个标准可以参考：
小于 10ms, 非常好；
在 10 - 20 ms 之间，正常；
在 20 - 50 ms 之间，异常，需要注意
大于 50 ms, 严重的 I/O 瓶颈
（来源于 MSDN）*

大家看到这里一定会问这么个问题，如果系统有严重的延迟，我该怎么办呢？

-可以将服务器上多余的其他进程移走；
-将严重延迟的数据文件，迁移到更快速度的 SSD 上
-精简索引和调优查询计划。精简索引是为了更新快，调优则是为了查询更快。少则快的原则在OLTP 上通用。

以下是创建性能数据采集表的脚本，依然是 5 分钟一次的采集：

CREATE TABLE T_GLOBAL_VIRTUAL_FILE_HIST(
    ID BIGINT IDENTITY(1,1)
,   AUDIT_DATETIME DATETIME
,   DATABASE_NAME VARCHAR(200)
,   DATABASE_FILE_NAME VARCHAR(200)
,   DATABASE_FILE_ID INT
,   DATABASE_FILE_DESC VARCHAR(15)
,   DATABASE_FILE_PHYSICAL_NAME VARCHAR(500)
,   NUM_OF_READS BIGINT
,   NUM_OF_BYTES_READ BIGINT
,   NUM_OF_WRITES BIGINT
,   NUM_OF_BYTES_WRITTEN BIGINT 
,   IO_STALL_READ_MS BIGINT
,   IO_STALL_WRITE_MS BIGINT
,   IO_STALL BIGINT
) 

DECLARE @INC_TIME_PERIOD INT = 5 ;
DECLARE @WAIT_DELAY VARCHAR(20) = '' ;
SET @WAIT_DELAY = LEFT( '00:' + RIGHT('00'+CONVERT(VARCHAR,@INC_TIME_PERIOD),2),5 ) 

WHILE(EXISTS(SELECT * FROM T_GLOBAL_WAIT_COLLECT_PROGRESS_CONTROL WHERE ENABLED_FLAG=1))
BEGIN 
    INSERT INTO T_GLOBAL_VIRTUAL_FILE_HIST (  
        AUDIT_DATETIME  
    ,   DATABASE_NAME 
    ,   DATABASE_FILE_NAME
    ,   DATABASE_FILE_ID  
    ,   DATABASE_FILE_DESC  
    ,   DATABASE_FILE_PHYSICAL_NAME  
    ,   NUM_OF_READS  
    ,   NUM_OF_BYTES_READ  
    ,   NUM_OF_WRITES  
    ,   NUM_OF_BYTES_WRITTEN   
    ,   IO_STALL_READ_MS
    ,   IO_STALL_WRITE_MS
    ,   IO_STALL
        )
    SELECT GETUTCDATE() AS AUDIT_DATETIME
        ,   DB_NAME(dbf.database_id) AS database_name
        ,   dbf.name as database_file_name
        ,   dbf.file_id as database_file_id 
        ,   dbf.type_desc as database_file_desc
        ,   dbf.physical_name as database_file_physical_name
        ,   vfs.num_of_reads
        ,   vfs.num_of_bytes_read
        ,   vfs.num_of_writes
        ,   vfs.num_of_bytes_written
        ,   vfs.io_stall_read_ms
        ,   vfs.io_stall_write_ms
        ,   vfs.io_stall 
    FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs 
        INNER JOIN sys.master_files dbf ON vfs.database_id = dbf.database_id
                                        and vfs.FILE_ID = dbf.file_id
    ORDER BY database_name,database_file_desc   ,database_file_id

        WAITFOR DELAY @WAIT_DELAY  ;
END 

上面两图基于下面的 SQL 完成。
这里有个 IO Stall 的概念，就是等待 IO 完成的一个等待状态。

; WITH BASE_QUERY  AS (
        SELECT AUDIT_DATETIME, ROW_NUMBER() OVER ( ORDER BY AUDIT_DATETIME) AS RNK 
        FROM (SELECT  DISTINCT AUDIT_DATETIME FROM T_GLOBAL_VIRTUAL_FILE_HIST WITH(NOLOCK)   ) TMP 
    ) 

    , CAL_QUERY_CURRENT AS ( SELECT HIS.AUDIT_DATETIME
            ,   HIS.DATABASE_NAME
            ,   HIS.DATABASE_FILE_ID
            ,   HIS.DATABASE_FILE_DESC
            ,   HIS.DATABASE_FILE_PHYSICAL_NAME 
            ,   HIS.IO_STALL_READ_MS
            ,   HIS.IO_STALL_READ_MS / NULLIF(HIS.NUM_OF_READS,0) AS AVG_STALL_RAED_MS
            ,   HIS.DATABASE_FILE_NAME
            ,   BQ.RNK 
    FROM T_GLOBAL_VIRTUAL_FILE_HIST HIS 
        INNER JOIN BASE_QUERY BQ ON HIS.AUDIT_DATETIME = BQ.AUDIT_DATETIME
              WHERE HIS.IO_STALL_READ_MS IS NOT NULL
        ) 

SELECT * FROM CAL_QUERY_CURRENT

上面两图基于下面的 SQL 完成。
这里可以看到日志是写比较频繁的一类文件，所以存放他们的物理磁盘一定要够快。
我们这里显示的是开发库，4G 左右的数据用了 1 小时沾满。

; WITH BASE_QUERY  AS (
        SELECT AUDIT_DATETIME, ROW_NUMBER() OVER ( ORDER BY AUDIT_DATETIME) AS RNK 
        FROM (SELECT  DISTINCT AUDIT_DATETIME FROM T_GLOBAL_VIRTUAL_FILE_HIST WITH(NOLOCK)   ) TMP 
    ) 

    , CAL_QUERY_CURRENT AS ( SELECT HIS.AUDIT_DATETIME
            ,   HIS.DATABASE_NAME
            ,   HIS.DATABASE_FILE_ID
            ,   HIS.DATABASE_FILE_DESC
            ,   HIS.DATABASE_FILE_PHYSICAL_NAME 
            ,   HIS.NUM_OF_READS
            ,   HIS.NUM_OF_BYTES_READ / 1024  /1024 /1024 AS NUM_OF_BYTES_READ
            ,   HIS.NUM_OF_BYTES_READ / 1024   / NULLIF(NUM_OF_READS,0) AS  AVG_OF_BYTES_READ
            ,   HIS.NUM_OF_WRITES
            ,   HIS.NUM_OF_BYTES_WRITTEN /1024 /1024 /1024 AS NUM_OF_BYTES_WRITTEN
            ,   HIS.NUM_OF_BYTES_WRITTEN /1024   / NULLIF(HIS.NUM_OF_WRITES,0) AS AVG_OF_BYTEST_WRITTEN
            ,   HIS.DATABASE_FILE_NAME
            ,   BQ.RNK 
    FROM T_GLOBAL_VIRTUAL_FILE_HIST HIS 
        INNER JOIN BASE_QUERY BQ ON HIS.AUDIT_DATETIME = BQ.AUDIT_DATETIME
        ) 

SELECT * FROM CAL_QUERY_CURRENT

通过 sys.dm_os_performance_counter 能看到 windows performance monitor 能看到的有关SQL Server 进程使用资源的信息。关于他的应用，下回分解！