SQL Server 2005性能排错(13)

I/O 瓶颈

SQL Server性能非常依赖于I/O子系统。除非你的数据库适合物理内存，SQL Server经常地会有数据库页面进出缓存池。这样就发生了实质的I/O流量。同样，在事务被明确的提交前，日志记录需要写入磁盘。SQL Server为各种目的可以使用tempdb，例如存储中间结果，排序，保持行的版本或其他。所以好的I/O子系统对于SQL Server性能非常重要。

除了当事务需要回滚时，访问日志文件是非常频繁的，而包括访问数据文件，包括tempdb，是随机访问的。所以作为一个通常的规则，为获取更好的性能，你最好将日志文分布不到不同的物理磁盘。本文重点于不是在如何配置你的I/O设备，而是描述识别你的I/O瓶颈的方法。一旦识别了I/O瓶颈，你也许需要重新配置你的I/O子系统。

如果你有一个慢速的I/O子系统，你的用户会遇到性能问题，例如很长的响应时间，任务由于超时而中断。

你可以使用下列性能参数来识别I/O瓶颈。注意，如果你的收集间隔过长，这些平均值可能会倾向于过小。例如，很难通过60秒一次的快照获取正确的I/O值。所以，你不能依赖于一个计数器来确定瓶颈；要考虑多个技术起来反复校验。

◆PhysicalDisk Object: Avg. Disk Queue Length表现在采样周期中所选择的物理磁盘队列中的物理读和写平均请求数量。如果你的I/O系统过载，更多的读/写操作将被等待。如果在很少使用SQL Server时，你的磁盘队列长度经常超过2，这样你可能遇到了I/O瓶颈
◆Avg. Disk Sec/Read 是平均每次从磁盘读取数据的时间

小于10 ms – 很好
在 10 - 20 ms 之间- 正常
在20 - 50 ms 之间- 缓慢，需要注意
大于 50 ms – 严重的I/O 瓶颈

◆Avg. Disk Sec/Write 是平均每次从磁盘读取数据的时间。请引用之前的指导数据。
◆Physical Disk: %Disk Time 是所选磁盘驱动器用于服务于读或写请求的总共时间的百分比。一般推荐是如果该值大于50%，则表现为I/O瓶颈。
◆Avg. Disk Reads/Sec 表现磁盘上读操作的速度。你需要确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。
◆Avg. Disk Writes/Sec表现在磁盘上写操作的速度。确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。

当使用以上计数器，你在使用RAID配置时，你需要使用下列公式调整结果值。

Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks

例如，你有带有2个物理磁盘的RAID-1系统和下列计数器值。

Disk Reads/sec            80
Disk Writes/sec           70
Avg. Disk Queue Length    5

这种情况下，你遇到了(80 + (2 * 70))/2 = 110 I/Os per disk，你的磁盘队列长度为5/2=2.5，表明系统接近于I/O瓶颈。

你也可以通过考察锁等待来识别I/O瓶颈。当数据页通过读或写访问并且在缓存池中页不可用时，这些锁等待占用了大量的物理I/O等待。当页面没有在缓存池中找到时，一个异步I/O请求被发出，I/O的状态是被选中的。如果I/O已经完成，工作进程处理正常。否则，依赖于请求的类型，它会等待PAGEIOLATCH_EX 或 PAGEIOLATCH_SH。从下列的DMV查询可以用于找到I/O锁等待的统计。

Select  wait_type, 
waiting_tasks_count, 
wait_time_ms
from sys.dm_os_wait_stats  
where wait_type like 'PAGEIOLATCH%'  
order by wait_type
wait_type       waiting_tasks_count  wait_time_ms   signal_wait_time_ms
-----------------------------------------------------------------------
PAGEIOLATCH_DT  0                    0                    0
PAGEIOLATCH_EX  1230                 791                  11
PAGEIOLATCH_KP  0                    0                    0
PAGEIOLATCH_NL  0                    0                    0
PAGEIOLATCH_SH  13756                7241                 180
PAGEIOLATCH_UP  80                   66                   0

下划线的是有兴趣的锁等待。当I/O完成时，工作者被放置到可用队列中。从I/O完成到工作者被实际的调度的时间在signal_wait_time_ms列说明。如果你得waiting_task_counts and wait_time_ms偏离正常值太多，你可以识别为I/O问题。为了这样，通过使用性能计数器和关键DMV建立正常时运行时的性能基线就十分重要。wait_types可以识别是否你的I/O子系统处在瓶颈状态，但是他们不能提供任何关于物理磁盘遇到问题的信息。

你可以使用下列DMV查询找到当前等待的I/O请求。你可以周期性的执行这些请求检查I/O子系统的健康情况，并找到涉及I/O瓶颈的物理磁盘。

select 
database_id, 
file_id, 
io_stall,
io_pending_ms_ticks,
scheduler_address 
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

下面是示例的输出。它展示当时在给定的数据库上有3个未决定的I/O 。你可以使用database_id 和 file_id 来找到映射的物理磁盘文件。

io_pending_ms_ticks 表现了所有等待在未决定队列中的个别的I/O。

Database_id File_Id io_stall io_pending_ms_ticks scheduler_address
----------------------------------------------------------------------
6  1  10804  78   0x0227A040
6  1  10804  78   0x0227A040
6  2  101451 31   0x02720040