游标脚本性能问题解决与分析 (4) - Cursor Performance Analysis

第四部分、游标相关知识讲解

（一） Fast
Forward Only （快速只进）游标分析

Microsoft SQL
Server 实现了一种称作fast forward only游标的性能优化。https://msdn.microsoft.com/zh-cn/library/ms187502.aspx

当遇到下面情形时，Fast Forward-only游标会隐式转换为其他类型

1.
当SELECT语句连接包含trigger table（INSERTED/DELETED）的一个或多个表，游标被转换成static类型。

2.
当SELECT语句查阅text、ntext或image列，如果SQL Server OLE DB访问接口或使用了SQL Server ODBC驱动器，游标被转换成dynamic类型。

3.
当Fast Forward-only游标不是只读的，它会被转换成dynamic游标。动态游标是可以用来update当前滚动到的数据行并将更新回写到table中的。

4.
当SELECT语句是一个查阅了链接服务器中一个或多个远程表进行分部查询，游标被转换成keyset-driven类型。

5.
如果SELECT语句查阅text、ntext或image列以及TOP语句，游标被转换成keyset-driven类型。

（二）几种游标的使用规则：

另外，我们这里列出了几条游标使用规则可供参考：

1.
如果应用程序中，只能使用服务器端游标(所有在SQL
Server上定义的游标都是服务器端游标，如果应用程序在客户端使用游标，在SQL
Server端是不会看到有游标打开的)，尽量选择使用FORWARD-ONLY、FAST-FORWARD、READ-ONLY游标。当处理单向只读数据时，使用FAST_FORWARD选项而不是FORWARD_ONLY，因为它可以提供一些内部的性能优化。这种类型的所游标产生的SQL
Server整体开销是最少的。如果您无法使用FAST_FORWARD游标，可以按序尝试使用下面的游标，找到适合您需求的游标。按照他们的性能特征列出，从最快到最慢为：dynamic，static，keyset。

2.
除非没有其他选择，避免使用static/insensitive和keyset游标，这是因为他们会在TEMPDB中创建临时表，这会增加整体开销导致内存抢夺问题。

3.
使用游标会减少并行而导致不必要的锁和阻塞的发生。要避免这种情况，可以适当地使用READ_ONLY游标选项；或者在你需要进行更新时，使用OPTIMISTIC游标选项来减少锁的产生。避免使用SCROLL_LOCKS游标选项，因为它会减少并行。

4.
从应用程序的角度尽快的加载并且滚动游标到最后一行。这会释放在创建游标时随之创建的共享锁，从而释放SQL Server资源。

5.
如果你的应用程序需要手动滚动记录并进行更新而必须使用游标，请避免使用客户端游标，除非返回的行数很少或数据是静态的。如果行数很大，或者数据不是静态的，可以考虑使用服务器端的keyset游标。由于客户端和服务器端网络拥挤的减少，性能问题很可能会出现。为了优化性能，可能需要在实际情况下对两种游标类型都进行尝试，决定哪种更适合需要应用的系统。

6.
如果游标需要执行JOIN操作， keyset和static游标通常比dynamic游标快。

（三）深入了解Fast_forward游标

1.
既然已经有了read_only forward_only游标，fast_forward游标岂不是是多余的？为什么还需要它们？

fast_forward的确是”多余”的。read_only
forward_only游标确实适用于很多应用，但是在有些应用的查询计划中却不理想。Read_only
forward_only游标是动态游标，动态游标一般使用的是动态计划。问题在于，有些情况下即使最好的动态计划也远不如静态计划。因此我们引入了Fast_forward游标来使用了一种更平衡的方式，当静态计划更适合时它会选择静态计划。

2.
什么时候用fast_forward，什么时候用read_only
forward_only？

综合来说，fast_forward游标更好一点。但是，在做最后决定之前应该先对您的应用进行性能测试。这是因为，使用动态或者静态计划的决定方式是完全不同的（看下面的解释）。不论使用哪种计划方式或哪种游标模式，索引调优（index
tuning）或计划提示（plan
hint）都会是游标优化的一个重要部分。

3.
什么是动态计划？

动态计划可以增量地进行，在SQL Server中，我们通过将查询执行状态序列化到maker中来实现。然后，我们可以构建一个新的查询计划树，使用刚刚的maker来复位每个操作。另外，动态计划可以根据当前位置前后移动。dynamic和一些fast_forward游标都会使用动态计划。

动态计划只包括动态运算符（支持maker及前后的移动），这很像流操作符中进行查询的notion（stop-and-go）。但并不是每个流操作符都是动态的。

在SQL Server中，动态意味着：

（1）
操作符可以使用maker复位到它当前的位置，或者到当前位置的相关位置（下一个或前一个）

（2）
操作符状态一定要小，从而使maker比较小。操作符中不能存储行数据，尤其是sort
table、hash
table或者work
table，甚至一行都不可以，因为即使单行也可能很大。

没有动态计划，游标可能需要临时存储媒介来保存查询结果集（或它的keyset）。然而，某些操作符对动态执行计划来说是不合适的，比如hash
join、hash
agg、compute
sequence和sort。这会导致一个次优的计划。

4.
什么时候动态计划会不如静态计划？

在某些查询中，比如使用row_number的，动态计划是不可用的。但是当动态静态都可用时就有问题出现了：在某个操作符（比如join）既有动态（nested
loops）也有非动态（hash）实现时可能发生；当有些索引支持排序而有些不支持时，也可能会发生问题。

下面是一个例子，表ORDERS有一个DATE上的索引，以及一个SUBTOTAL上的索引（在这个例子中，clustered或者non-clustered没关系）。该查询想要查询订单信息：

SELECT DATE,
SUBTOTAL,
ORDERID,
CUSTOMERID

FROM ORDERS where SUBTOTAL > 10000000

ORDER BY DATE

表中有一亿条数据，查询结果包含100条。动态游标不能排序，所以它必须使用DATE索引，查看每条数据，填到SUBTOTAL中。而静态游标可以查询大于10000范围的SUBTOTAL索引，排序并把它们存储到游标（temp table）中。

5.
fast_forward游标如何解决该问题？

在特定条件下，fast_forward游标从最佳静态计划和最佳动态计划中选择开销最小的一个。在上面所提到的极端例子中，它就会使用静态计划。

6.
应用开发者可以做什么？

（1） 为OPTION(FAST <N>)选择一个中性值。

（2） 通过调用sp_cursorprepare或OPTION(RECOMPILE)避免在不同参数下的计划重用。

（3）
避免在游标中使用ORDER BY

（4） 使用相等谓词及多列索引来支持序列。稍微修改一下上面的例子，如果我们在SUBTOTAL范围有一个包含值{S,M,L,XL}的计算列SIZE，我们可以查询WHERE SIZE=’XL’ ORDER BY DATE，并在<SIZE,DATE>上使用索引。通常通过增加索引列来包括相等谓词后的排序列。