SQL Server 速度之谜

等待时间分析可以通过控制应用程序对询问的响应时间来提高SQL的性能。你是否曾因为SQL服务器减慢了应用程序的运行速度而自己却为不知该如何解决这一问题感到苦恼呢？

　　【美国1105集团供IT专家网专稿】SQL服务器的性能管理通常是反应式的，它侧重的是服务器的运行状态。数据库管理员会对问题作出响应而不是将避免问题的产生摆在首位。可视性也很大程度上仅限于观测数据库服务器而不是了解SQL服务器怎样直接对程序用户的产生影响。

　　等待时间分析是对SQL服务器数据库性能的一种改进方法，它不再只是监测系统的运行状况而已，等待时间分析侧重于应用程序响应询问所需的时间。其结果是一种能够迅速回答关键问题的分析技巧，如为什么数据库要让用户进行等待，我们又可以为此做些什么呢?

　　由于轻量级监测技术和无代理架构的发展，等待时间分析现在已经可以应用与实际操作了。它利用了SQL服务器中新设备来暴露等待类型，这些步骤在SQL服务器处理询问时积累延时。

　　事半功倍

　　对于IT机构来说，使用等待时间分析的结果可以减少数据库操作的成本并改进IT服务。数据库管理员可以用更少的服务器完成更多的工作。从SQL Server 2000到2005再到2008，开发周期缩短了。对于那些要用更少的资源提供更优质服务的IT公司来说，等待时间分析是实现成本效益的答案。

　　数据库管理员通常位于两难的境地。对于响应程序用户的数据库来说，他们的数量是有限的，但是他们却不能看到数据库减缓的原因。通常这样的事情根本不是发生在其数据库中，但是却源于应用程序代码，网络或者系统架构。为了获取改变的应用程序代码，数据管理器必须为程序员提供证据，与此同时，程序员会感到十分疑惑因为他们并不了解数据库。这些问题是典型的依赖于旧式服务器状态监测技术的表现。

　　等待时间分析

　　有效的等待时间分析不仅仅是截取等待类型的数据。为了能有效地在模糊数据点中生成有用信息，它必须利用商业智能情境中验证过的技术。其中关键的概念包括：

　　l 测量时间，不要考虑操作 对于应用程序用户，I/O操作或逻辑读取的数量已经没有什么意义。重要的是应用程序响应所需的时间。从这一角度来实现优化要侧重于数据库所需的时间。等待类所做的就是这一操作。

　　l 注意查询 问题的关键是SQL查询级别的测量和单独对话。测量通过实体或数据库的等待而不会减缓其速度的工具不会给出有价值的信息。

　　l 持续捕获 整个过程中要时刻保持监视。通过观测所有的对话，数据库管理员可以发现任何问题。当用户因为程序速度减慢而寻求帮助的时候，数据肯定已经准备好了。依赖于间歇性追踪的系统将错过可能发生的错误。

　　l 历史地观测 要明白需要修复什么，数据库管理员应该研究数据库的趋势和变化，而不只是看到即时结果。有效的等待时间分析用历史的眼光来比较当前等待类的统计数据和以前的统计以便发现其中的不同之处，这有助于解决潜在的新问题。

　　SQL Server等待类

　　意识到SQL 服务器等待类是了解该方法的第一步。任何有悖于SQL服务器运行的语句都将面对等待，因为SQL服务器会为要完成的命令按照顺序访问资源。请求会等待要检索的，要写入磁盘或是要写入SQL服务器日志的数据。当等待变成一个长期过程或超量时，就会出现性能问题了。

　　普通等待类

　　SQL服务器会记录类的信息以及等待进程的持续时间。虽然在SQL服务器中有100多种类，但是其中可能只有少数会出现问题。任何等待类都以LCK_开始，这意味着该任务在等待获得一个锁定。例如，一个LCK_M_IX的等待类意味着等待获取Intent Exclusive锁定。20多种等待类是锁定等待，这些等待之所以恰当是因为SQL服务器中大多数执行的任务要求某种锁定。下一个最普遍的锁定种类是ASYNC_IO_COMPLETION和ASYNC_NETWORK_IO。前一个是等待I/O操作完成的等待，后一个是等待I/O在网络上完成的等待。最后，留意一下CXPACKET等待状态。当某一进程试图使查询处理器交换迭代同步时会出现这一等待。这说明了服务器并行设置问题。花时间弄清楚怎样的潜在等待状态是耗时的。平均来说，大约20种潜在等待状态表现了Server 7.080%的问题。在完成等待时间分析之后，你就习惯某些等待类了。

捕获等待类数据

　　SQL服务器已经提供了等待类的观点，但是遗憾的是，这些观点大多数情况下有些模糊因此难以发挥作用。从SQL 和2000开始，数据库管理员可以使用企业管理器来查看等待类。问题是所有企业管理器提供的都是等待类的名称和进程等待的时长。当SQL Server Management Studio被引入SQL Server 2005的时间，主动查询和对话的观点都得到了保存。此外，数据库管理员会获得一个等待类和等待时间。

　　目前，观察SQL服务器中统计的最佳方法是使用动态管理查看(DMVs)。如果你仍在运行SQL Server 2000或更旧的版本，那就无法使用这一功能。与查看统计关系最密切的动态管理查看是sys.dm_exec_requests，sys.dm_exec_query_stats和sys.dm_os_wait_stats。

　　l sys.dm_exec_requests： 该动态管理查看提供了与 给定SQL服务器上执行的每个请求相关的信息。当看到等待状态的时候，你所关心的只是其中的某些部分：尤其是sql_handle, wait_type, wait_time, last_wait_type 和 wait_resource。

　　l sys.dm_exec_query_stats： 该查看返回了用于缓冲查询的性能统计信息。通过使用sys.dm_exec_requests中的sql_handle细则来加入查看，就可以获取等待发生频率的图片。记住，该查看不会给出详细信息。

　　l sys.dm_os_wait_stats： 该查看提供所有SQL服务器上等待状态的图片。它提供所有不同等待状态的列表和状态任务的详细信息，包括状态中等待任务的数量和所有等待时间以及平均等待时间。

　　情境一：识别问题查询

　　数据库管理员遭遇的最恼人的问题是"问题查询"(图一)。通常这样的查询是程序员定义的运行缓慢的查询。数据库管理员通常会听说这类查询"在开发的时候能正常运行"或"已经正常运行一段时间"。有时，反复的性能投诉会使数据库管理员寻找这一问题产生的原因以提高其性能。

　　

　　图一

　　在这两种情况中，传统的研究方法涉及到开放若干工具，如SQL Server Profile和Windows Performance Monitor，再尝试捕获实时问题。具体来说，大多数数据库管理员都会看到具有较高等待时长，大量读取/写入的查询以及那些被频繁重复运行的查询。

　　不过在这两种情况中，基本的数据可能会有误导作用。例如，频繁重复运行但是运行得很快的查询一般不会形成阻碍。如果基本查询能高效快速地运行，一般应该不存在什么问题。但是如果查询的等待类持续总是相同，如ASYNC_IO_COMPLETION，那就可能是有问题了。

　　情境二：解决锁定问题

　　SQL服务器锁定通常是一个令人费解的问题。然而，使用等待类分析，要找到所需的锁定以及锁定的方式就容易得多。

　　大多数SQL服务器都会经历瞬间锁定和阻塞状况。只有当这些锁定导致长时间阻塞时才出现问题。列出锁定种类的等待类，如LCK_M_SCH_M，能准确识别进程等待的东西(图二)。图二示例中，一个等待锁定的进程需要修改表或视图的框架，因此该进程需要等待前面的插入，更新或删除数据的进程才能完成。

　　另一个潜在的问题是单独阻塞进程的自然扩展：阻塞链。一旦一个进程处于等待资源并阻断另一个进程的状态，另一个进程就很有可能会结束等待。解决的方案是找到阻塞链的源头。一旦等待类的源头找到并解决，其他进程就会不再受影响。

　　

　　图二

　　情境三：找到硬件瓶颈

　　识别硬件资源的瓶颈可能是最复杂的情况。虽然会有一些指示瓶颈的症状，但是却似乎没有等待类以外的其他方法可用来确定硬件问题。

　　在本例中，关键是要找到与磁盘子系统(PAGELATCHIO_*)，CPU(CXPACKET)或通用存储系统(RESOURCE_*)相关的等待类。当等待超过几秒后，这些等待类就会指示硬件故障。

　　例如，我们假设由一个经常运行20分钟左右的查询，且该查询使用三个表格来确定第四个表格的更新。程序员已经提供反馈显示该查询已经随机启动超过四小时;查询运行的快慢速度之间没有可辨认模式。一个数据库管理员能够确定这样的查询一般会产生怎样的等待类，以及每个等待类的持续时间。如果等待类与硬件瓶颈相关，就该看看系统中那些在类似等待类中等待时长超出预期的其他查询。