第22/24周 等待和I/O延迟统计

大家好，欢迎回到性能调优培训的第22周。上周我谈了SQL Server里的基线，今天我们继续，谈下SQL Server里的等待和I/O延迟统计。当我进行SQL服务器健康检查时，我总会使用这2个维度全局掌握下SQL Server的健康状况。

等待统计（Wait Statistics）

在SQL Server里每次你执行一个查询，查询会等待。初次看这个看起来很惨淡，但其实有一个非常好的原因，在SQL Server里总会等待。每次一个查询等待，SQL Server通过所谓的等待统计（Wait Statistics）来跟踪这些等待。在我们讨论等待统计本身前。我想介绍下为什么在执行期间，查询总会等待。等待的概念主要基于2个原则：

• 非同步资源等待（Asynchronous Resource Waiting）
• 协同调度（Cooperative Scheduling）

我们来详细看下这2个。每次查询等待一个当前不可用的资源——例如在缓存池理还没缓存的页，或者因为另一个不兼容的锁而不能获得的锁——查询会进入SQL Server里所谓的挂起（Suspended）状态。查询在挂起状态一直等待直到资源变成可用。

当资源变成可用时，查询进入所谓的可执行（Runnable）状态，再次等待，知道CPU变成可用。当CPU是可用时，查询最后进入运行（Running）状态，执行到资源再次变成不可用。当这个发生时，查询再次进入挂起（Suspended）状态。下图显示了这个查询生命周期。

 

另外查询也会由于在SQLOS（SQL Server操作系统）里SQL Server实现的协同调度（Cooperative Scheduling）而等待。SQL Server通过使用特定的WIN32 API功能调度它的线程。协同调度意味着当一个查询本身超过近4ms的额（quantum ）时，它从CPU上撤离。因为这个实现方式，在SQL Server里查询总会等待：一旦一个资源尚不可用，或者查询已超过了它的额——查询就会进入挂起（Suspended）状态并等待。

每次当一个等待情况发生时，等待时间被SQL Server通过等待统计（Wait Statistics）自动跟踪。SQL Server通过DMV sys.dm_os_wait_stats 报告这些信息。通过这个DMV返回的每一行都代表SQL Server里的一个特定等待——所谓的等待类型（Wait Type）。通过评估等待统计，SQL Server告诉你什么是最突出的等待类型。然后你可以聚焦这个等待类型并找出内部问题根源，还有对于这个等待类型为什么等待时间如此高。

I/O延迟统计（I/O Latency Statistics）

除了等待统计外另一个非常重要的是SQL Server也会报告的I/O延迟统计（I/O Latency Statistics）。有了这些延迟时间很容易找出你的SQL Server实例哪个文件有延迟时间。SQL Server通过DMF sys.dm_io_virtual_file_stats来报告这些信息。你可以传入database_id和file_id。如果你对这2个值都提供NULL值的话，你会得到SQL Server实例（数据和日志）所有查询相关文件的延迟统计。

对于这个DMF最重要的是io_stall_read_ms和io_stall_write_ms列。自上次SQL Server重启后，对你的存储进行读写操作所发生的累积延迟时间。如果你把这2个值除以num_of_read和num_of_writes列，你就得到从SQL Server角度来说，对于磁盘读写的平均延迟时间。这对于你的存储子系统的故障排除非常方便。

如果这个DMF报告非常高的延迟时间，你不应该简单的跑到存储供应商那里并买更快的存储。第一步你总要想下为什么你有这么高的延迟时间。当我在不同的系统上使用这个DMF时，TempDb总会报告很高的延时。但这也不意味着你要把TempDb移到更快的存储，例如SSD硬盘。第一步总要思考下，对于你特定的数据库“为什么”你有这么高的延迟时间。如果是TempDb的话你可以尝试最小化TempDb的使用——例如应用合理的索引策略来摆脱执行计划里的排序和哈希运算符，这2个运算符会蔓延到TempDb。

等待统计和I/O延迟统计直报告你症状，你的任务是找出性能问题的内在根源，分析它，最后解决它。

小结

在今天的性能调优培训里我们详细讨论了SQL Server里的等待统计和I/O延迟统计。对于性能监控和故障排除来说，这2个DMVs/DMFs非常重要，因为你从中可以找出SQL Server当前在哪些领域有性能问题。下周我们会详细谈下TempDB，我把它叫做SQL Server的公共厕所。请继续关注！

围观PPT:

1123_22_等待统计.rar