SQL Server磁盘I/O性能分析

 

SQL Server中的I/O操作类型：

1.对于内存中没有缓存的数据，第一次访问时需要将数据从所在的页面从数据文件中读取到内存中

2.在任何Insert/Update/Delete提交前，SQL Server需要保证日志记录能够写入到日志文件中

3.当SQL Server做Checkpoint时，需要将内存缓冲区中已经发生修改的数据页面同步到硬盘的数据文件中，一般一分钟一次Checkpoint。如果修改较多，频率高一些，写的数量 和上次checkpoint依赖发生的数据修改量有直接关系

4.当SQL Server Buffer Pool发生压力时，会触发Lazy Writer，主动将内存里的一些很久没有使用过的数据页面和执行计划清空。如果这些页面上发生的修改还未由checkpoint写回硬盘，Lazy Writer会将其写回

5.一些特殊操作，例如DBCC Checkdb、Reindex、Updata Statistics、Backup等，会带来较大的硬盘读写。这些操作应该给都发生在一些固定的时间段

 

 

数据库级别的I/O影响：
1.Recovery Interval（sp_configure)控制着SQL Server多久进行一次checkpoint

   checkpoint pages/sec（Buffer Manager）呈周期性上升

   MSSQL：SQL Statistics-Batch Requests/sec：每秒钟完成的批处理（batch）数目

   MSSQL：Databases-Active Transactions：SQL Server里打开的，还没有提交的事物数目

2.数据/日志文件的自动增长和收缩

3.数据文件里面碎片程度：页面碎片越多，SQL Server就需要读取和写入更多的页面，增加硬盘读写量

4.表格上的索引结构

5.数据文件和日志文件分开放在不同的磁盘上。如果可能的话，日志文件要放在写入速度较快的磁盘上

6.一个数据文件组是否有多个文件，并且放在不同的磁盘上。但是对于日志文件，在一个时间点，SQL Server只会写一个日志文件，所以在不同磁盘上创建多个日志文件对性能没有任何提高

 

系统级别的I/O影响：
%Disk Time：只观察其曲线趋势，值本身没有参考价值

%idle Time：磁盘处于空闲状态百分比。当磁盘处于空闲状态时，值为100%。当磁盘满负荷操作时，值为0，所以可以根据该值反推%Disk Time

Disk Bytes/sec：每秒钟磁盘读和写总量（磁盘的吞吐量）。需要先确认该磁盘的最大读写速度才有参考价值，可以看出是否已经达到了该磁盘的读写上限

Avg.disk sec/transfer：磁盘每一次读或写的动作所花的平均时间

Avg.Disk Queue Length：发出的磁盘操作正在等待被磁盘处理的请求数目。理论上讲，这个值不应该长时间大于2

Current Disk Queue Length：当前正在等待被磁盘处理的请求数目

当Avg.Disk Queue Length高时，需要分别观察Disk Bytes/sec和Avg.disk sec/transfer哪个计数器已经达到了其最大值

 

SQL Server中的性能计数器

Buffer Manager：

Page Reads/sec和Page Writes/sec：每秒钟读写了多少页面。了解到由于Buffer Pool的行为带来了多少磁盘读写

Lazy Writes/sec：Lazy Writer为了清空Buffer Pool每秒钟做了多少页面写入操作

Checkpoint Writes/sec：每秒钟从Buffer Pool里写入到磁盘上的Dirty Page数目

Freespace Scans/sec：在堆（heap）结构里找能够使用的空间。对于没有聚集索引的表格，SQL Server会以堆的形式存储。如果该值很高，应该多建立一些聚集索引

Full Scans/sec：每秒钟SQL Server做全表扫描数目，该值越小越好

Databases(Log Activity)：

Log Flush Wait Time：写入日志的动作曾经因为磁盘来不及响应而遇到的等待时间，会导致前端的事务不能提交，会严重影响SQL Server性能。该值应该在绝大多数时间都为0

Log Flush Waits/sec：在每秒提交的事务里，有多少个事务曾经等待过日志写入完成。理想情况下，日志写入应该立刻完成，不需要等待。