Performance Monitor4：监控SQL Server的IO性能

g87616758

　　SQL Server的IO性能受到物理Disk的IO延迟和SQL Server内部执行的IO操作的影响。在监控Disk性能时，最主要的度量值（metric）是IO延迟，IO延迟是指从Application创建IO请求，到Disk完成IO请求的时间延迟。如果物理Disk不能及时完成IO请求，跟不上请求负载的速度，那么SQL Server就容易出现性能问题。SQL Server内部在执行一些特定的操作时，会和Disk做读写交互，这也会影响物理硬盘响应SQL Server的IO请求的性能，使查询进程处于PageIOLatch或WriteLog等待。
　　一，在系统级别监控物理Disk的IO性能
　　1，监控物理Disk的IO延迟
　　在Windows级别上对Physical Disk的IO延迟进行分析，主要依赖于Performance Monitor的计数器，衡量物理Disk的IO延迟的计数器主要有三个：

• Avg. Disk sec/Transfer：Disk每一次读写操作所用的平均时间
  
• Avg. Disk sec/Read：Disk每一次读操作所用的平均时间
  
• Avg. Disk sec/Write：Disk每一次写操作所用的平均时间
  

　　avg.Disk sec/(Transfer,Read,Write)，能够很好的反映Disk的IO速度，推荐的衡量Disk的IO速度的基线（baseline）:

• 很快：<10ms
  
• 一般：10-20ms
  
• 有点慢：20-50ms
  
• 非常慢：>50ms
  

　　2，分析Data Collector收集的计数器数值
　　下图是产品环境中一台Server的计数器数值图表，将IO延迟的度量值按比例放大1000倍，这样图表显示的单位就是ms。

• %Idle Time：在60%左右浮动，说明Disk不是很忙碌
  
• Avg.Disk sec/Write：大多数情况下都是10ms以下，偶尔波动，说明Disk的写延迟比较低
  
• Avg.Disk sec/Read：读延迟大多数情况下都是在40ms以上，鲜有低于40ms，偶尔达到峰值，说明Disk的读延迟非常高
  
• Avg.Disk sec/Transfer：读写延迟的均值在30ms左右，时有波动，在%Idle Time曲线不波动时，Disk的读写延迟也有波动，说明Disk的读写延迟不稳定
  

　　初步判断，Disk的读写延迟非常高，Disk的IO性能较差，IO速度慢

　　3，监控物理Disk的IO次数
　　根据Disk的IO次数来界定Disk性能，没有统一的阈值，一般通过监控计数值来获取一个趋势，设置一个基线，如果在Disk比较忙碌时，遇到异常的谷值，那么就需要查看是否出现参数嗅探问题和Disk IO密集的查询，异常的谷值一般是由查询语句请求的数据量太多造成的，需要对查询语句进行性能调优。
　　系统级经常用到的Disk性能计数器是PhysicalDisk计数器：

• Avg. Disk Queue Length ：提供Disk阻塞程度的主要度量值，表示在 sample interval期间，Disk等待处理的IO请求队列的平均长度，即等待被Disk处理的IO请求的数量
  
• % Idle Time：Disk的空闲程度，可以反推出Disk的忙碌程度
  
• Disk (Reads/Writes/Transfers)/sec：每秒Disk执行读写操作数量
  

　　队列长度波动很大，在%Idle Time 升高时，IO数量降低，没有发现明显的异常谷值。

　　4，监控物理Disk读写的数据量
　　这几个计数值，对监控物理Disk的读写性能，意义不大，仅仅作为参考。

• Avg.Disk Bytes/(Read,Write,Transfer)表示：在物理Disk执行读写操作时，物理Disk从Disk读取到内存的字节数量，从内存写入到Disk的字节数量，以及两者的总字节数量
  
• Disk Bytes/sec：在物理Disk执行读写操作时，数据从Disk传输到内存，或从内存写入到Disk的字节速度，好的Disk，其值在20-40MB之间，一般Disk，其值在20MB以下
  

　　二，SQL Server内部操作对Disk IO性能的影响
　　SQL Server能够缓存从Disk加载的数据页，正常情况下，大部分操作不需要任何物理读操作，不需要Disk的物理IO参与就能完成，但是，有一些操作，必须和物理Disk进行IO操作，才能完成。SQL Server和物理Disk进行IO交互的操作：

• 对于内存中没有缓存的数据，第一次访问时，需要将数据从数据文件读取到内存中，SQL Server访问的任何数据必须缓存到内存中，如果不在内存中，SQL Server发送读请求，将数据页从物理Disk读取到内存中，这个过程叫做物理读；如果数据存在于内存中，SQL Server直接访问，这个过程叫做逻辑读。
  
• 在任何修改操作提交之前，预写事务日志记录到日志文件，在CheckPoint和LazyWriter运行时，数据被写入数据文件。
  
• 执行CheckPoint时，将缓存中的脏页写入数据文件，脏页是指载入内存之后被修改过的数据页，内存中的数据和数据文件中的数据不一致，由CheckPoint触发的物理写操作和内压没有关系，和用户修改的数据量有关，用于控制还原的时间间隔。
  
• 当Buffer Pool空间不足，Free Buffer List减少到临界值时，LazyWriter进程主动将内存中的一些很久没有访问过数据页面和执行计划清空，如果数据页面是脏页，那么将其写入到数据文件，LazyWriter和内存压力有关，由于内存可用的Free Buffer不足导致LazyWriter进程执行清理操作。
  
• IO密集型操作，比如检查数据库的一致性（DBCC CheckDB），重建索引，更新统计信息，数据库备份等，会带来大量的Disk IO操作
  

　　SQL Server只会读取数据文件，只要数据缓存在内存中，理想情况下，SQL Server不会执行任何物理读操作，也不需要从物理Disk加载数据到内存，SQL Server执行读取操作性能和内存的缓存能力有直接关系，也和用户读取的数据量有关。
　　SQL Server的写操作分为写数据文件和写日志文件。写入日志文件的数据量，完全由数据修改量决定，和内存压力没有关系；写入数据文件的数量，主要和修改量有关。LazyWriter和内存压力有关系，一旦内存有压力，LazyWriter自动启动，负责清理最久未被访问的缓存，释放内存，增加可用的Free buffer数量。
　　因此，SQL Server请求的物理Disk的读操作数量和内存有直接关系，内存越充足，缓存的数据量越多，物理Disk的读操作的数量就会越少，逻辑读的数量不会减少；SQL Server请求的物理Disk的写操作数量和用户执行的数据修改量有直接关系，和内存是否存在压力关系很微小。在执行物理disk的读写请求时，SQL Server的查询进程产生PageIOLatch等待，表示进程正在执行物理读操作，该等待可以从DMV：sys.dm_exec_requests 查看到：





select  r.session_id,
r.blocking_session_id as blocking,
r.wait_type as Current_Wait_Type,
r.wait_resource,
r.last_wait_type,
r.wait_time,
r.status,
r.command,
r.cpu_time,r.reads,r.writes,r.logical_reads,
r.total_elapsed_time,
r.start_time,
db_name(r.database_id) as database_name,
SUBSTRING(  st.text,
r.statement_start_offset/2+1,
(    CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text))
ELSE (r.statement_end_offset - r.statement_start_offset)/2
END
)
) as IndividualQueryStatement
from sys.dm_exec_requests r
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where (r.wait_type<>'MISCELLANEOUS' or r.wait_type is null)
and r.session_id>50
and r.session_id<>@@spid
View Code

　　PageIOLatch 等待：表示进程正在从物理Disk加载数据到内存，即进程在进行物理读操作，从Reads字段能够看到物理读的数量
　　WriteLog 等待：表示事务正在修改数据，SQL Server将预先将事务日志记录写入到事务日志文件
　　参考文档：
Memory - Lazy Writer and Checkpoint
SQL Server disk performance metrics – Part 1 – the most important disk performance metrics
Measuring Disk Latency with Windows Performance Monitor (Perfmon)