MSSQL 读写占比分析方法分享


为了精准分析当前系统的读写占比,我们可以通过以下sql语句,获取指定数据库的DB文件的读写情况,
通过这些情况,我们可以为系统提速做下一步的操作,
例:
如果读多于写,那么需要做读写分离
如果写非常多,那么需要对业务场景的后台进行重新部署,分库分表,等提速操作,
以下脚本将展示,
监控mssql 文件的读写情况(读写比例 IO的情况分析)


 
----指定数据库的IO信息统计
SELECT  DB_NAME(DB_ID()) AS [数据库名称] ,
        [file_id] as [文件编号] ,
        num_of_reads  as [读页数],
        num_of_writes  as [写页数],
        num_of_bytes_read  as [读字节数],
        num_of_bytes_written  as [写字节数],
        CAST(100.00*num_of_reads/(num_of_reads+num_of_writes) 
        AS DECIMAL(10,1)) AS [读次数占比] ,
        CAST(100.00*num_of_writes/(num_of_reads+num_of_writes)
         AS DECIMAL(10,1)) AS [写次数占比] ,
        CAST(100.00*num_of_bytes_read/(num_of_bytes_read+num_of_bytes_written)
         AS DECIMAL(10,1)) AS [读字节占比] ,
        CAST(100.00*num_of_bytes_written/(num_of_bytes_read+num_of_bytes_written)
         AS DECIMAL(10,1)) AS [写字节占比]
FROM    sys.dm_io_virtual_file_stats(DB_ID(), NULL)