为了精准分析当前系统的读写占比,我们可以通过以下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)