游标脚本性能问题详解之案例实践篇(1)

游标类型对性能影响的实例。下面的两个游标脚本分别创建并执行了dynamic和fast forward only两种类型的游标。

知识补充：

关系数据库中的操作会对整个行集起作用。由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一小部分行。游标不仅可提供这种机制，而且是对结果集的一种扩展。

游标通过执行以下操作来扩展结果集处理：

允许定位在结果集的特定行。 从结果集的当前位置检索一行或一部分行。 支持对结果集中当前位置的行进行数据修改。 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

不理想的游标类型：(dynamic游标)

declare @p1 int  set @p1=NULL    declare @p2 int  set @p2=0    declare @p5 int  set @p5=4098  declare @p6 int  set @p6=8193    declare @p7 int  set @p7=0     exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',  N'  SELECT       T1.CONFLICT_ID  FROM         dbo.S_AUDIT_ITEM T1              LEFT OUTER JOIN dbo.S_USER T2   ON T1.USER_ID = T2.PAR_ROW_ID      WHERE  ((T1.BC_BASE_TBL = @P1)    AND  (T1.RECORD_ID = @P2))      ORDER BY  T1.OPERATION_DT DESC    OPTION (FAST 40)  ',  @p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'     print 'fetch' exec sp_cursorfetch @p2,2,4,1     exec sp_cursorclose @p2 

理想的游标类型(fast forward only游标)

declare @p1 int  set @p1=NULL    declare @p2 int  set @p2=0    declare @p5 int  set @p5=4112  declare @p6 int  set @p6=8193    declare @p7 int  set @p7=0     exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',  N'  SELECT       T1.CONFLICT_ID  FROM         dbo.S_AUDIT_ITEM T1              LEFT OUTER JOIN dbo.S_USER T2   ON T1.USER_ID = T2.PAR_ROW_ID      WHERE  ((T1.BC_BASE_TBL = @P1)    AND  (T1.RECORD_ID = @P2))      ORDER BY  T1.OPERATION_DT DESC    OPTION (FAST 40)  ',  @p5 output,@p6 output,@p7 output,'S_SRV_REQ','1-WUQTM6'     select @p1, @p2, @p5, @p6, @p7   print '2' exec sp_cursorfetch @p2,2,1,1    print '3' exec sp_cursorclose @p2 

注：脚本中用到的和游标有关的存储过程，请参考：http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec

一、如何解读游标的类型

sp_cursorprepexec [@handle =] statement_handle OUTPUT,       [@cursor =] cursor_handle OUTPUT,       [@paramdef =] N'parameter_name data_type, [,...n]'        [@stmt =] N'stmt',       [, [@scrollopt =] scroll_options OUTPUT]       [, [@ccopt =] concurrency_options OUTPUT]       [, [@rowcount =] rowcount OUTPUT]   @scrollopt  

 

[@ccopt

 

@p5=4098 转成16进制就是1002，对应的游标类型为Parameterized query + Dynamic cursor

@p5=4112 转成16进制就是1010，对应的游标类型为Parameterized query + Fast forward-only cursor

问题的现象是，左边的游标类型下，该脚本执行时间远大于右边的游标类型。

二、如何比较两个不同执行计划的优劣

在继续以下内容之前，这里要介绍一些查看和比较语句执行计划的知识。通常情况下，我们从management studio中输出图形界面的执行计划进行直观的比较，查看每个表用的访问方式，使用index还是table scan，使用了哪个index，表和表之间使用的join 方式有什么不一样。但是假如是一个复杂的语句，在不同的数据库上使用了不同的执行计划，对于同样表的访问，使用了不同的index，如何比较哪种执行计划更加优化呢 比较整个语句的执行时间是一种方法，但是这个比较的结果并不准确。语句的执行时间很容易受到其他外在因素的影响：

1. 不同机器上CPU，memory和disk的性能会影响执行时间。

2. 测试的时候有没有其他人在使用同样的数据造成阻塞

3. 其他人堆数据库的使用占用了系统资源

以上这些原因都有可能影响的语句的执行时间，从而影响到我们对语句性能结果的比较。因此我们不能把语句的执行时间作为衡量语句性能的标准。

这里介绍一种比较语句cost的方法。我们对于语句cost的衡量，主要是通过比对语句总的logical reads.

我们可以通过在management studio里的query window 执行”set statistics io on” ，在当前窗口中对所有执行的语句输出信息：

set statistics io on select * from dbo.test_TicketFact  set statistics io on 

执行语句两次，以消除physical reads和read-ahead reads的影响。

输出的结果如下：

(320 row(s) affected)  Table 'test_TicketFact'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

这里打印出来了语句中访问过的table的访问次数，总共的logical reads，physical reads等信息

这里我们需要关注的是logic reads的值，这个值实际上决定了对于IO和DISK以及内存的消耗。当语句是第一次执行，我们会看到physical reads的数字，以,而当语句第二次执行的时候，这些数据已经被读到memory里面了，因此我们会看到physical read和read-ahead reads都变为0，而logical reads的值就变成了语句所有使用的data的量。

为什么logic reads是我们需要关注的值呢 因为logic reads决定了语句要访问数据的量。假如我们的系统瓶颈在IO上，一旦语句需要访问的数据从内存里面清除，这个语句原本所有的logic reads会全部转为physical reads.因此那些大量使用logic reads就是可能导致大量physical reads的元凶。假如我们的bottleneck是CPU，这些做大量logical reads的语句同样有可能导致大量的memory 读，而读memory是需要消耗CPU资源的。因此，无论是CPU，memory还是DISK的瓶颈，那些做大量logical reads的语句都非常可能是造成问题的原因。

由以上内容，我们可以得出结论，语句的性能好坏，取决与这个语句做了多少logical reads.因此，假如同样的语句，使用了不同的执行计划，那么总的logical reads低的那个执行计划就是相对优化的。

三、分析本案例中两种游标的执行计划

现在我们回到需要研究的脚本，在这里，语句是一样的，不同的只是游标的类型。不同的执行时间说明很可能这个语句使用了不同的执行计划。现在问题变成了，同样语句使用了不同的执行计划，得到了不同的执行时间。我们首先从”set statistics io on” 的结果入手：

1.左边使用dynamic游标有大量的逻辑读，情况如下：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 9770695, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

2．而右边使用fast forward only游标只有三次逻辑读，情况为：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

从这里输出的结果的区别，说明了在table S_AUDIT_ITEM上SQL Server使用了不同的访问方式

接下来我们分析两个脚本的执行计划：

1. dynamic游标对应的不理想的执行计划中，SQL Server选择了索引扫描(index scan)及索引S_AUDIT_ITEM_M4来查阅S_AUDIT_ITEM表。因此我们会在这里看到大量的IO。

 

这个索引扫描实际上访问了整张表的数据。

2.而fast forward only游标对应的理想的执行计划中，SQL Server选择的是索引查找(index seek)及索引S_AUDIT_ITEM_M3来查阅S_AUDIT_ITEM表。所以我们只看到3个逻辑读。索引S_AUDIT_ITEM_M3包含4个列，第一个列是RECORD_ID。另外，在语句中，有WHERE条件T1.RECORD_ID=@P2

 

四、尝试解决问题

首先我们尝试更新统计信息：UPDATE STATISTICS ON S_AUDIT_ITEM WITH FULLSCAN，但是这个操作在此问题案例中没有作用。

从以上的分析中，我们已经发现，假如使用index S_AUDIT_ITEM_M3访问S_AUDIT_ITEM表，得到的执行计划非常好，我们可以直接用index hint来解决这个问题：

declare @p1 int set @p1=NULL  declare @p2 int set @p2=0   declare @p5 int set @p5=4098   declare @p6 int set @p6=8193   declare @p7 int set @p7=0   exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',   N'   SELECT T1.CONFLICT_ID   FROM dbo.S_AUDIT_ITEM T1 with (INDEX=S_AUDIT_ITEM_M3) /* 解决方案2 */   LEFT OUTER JOIN dbo.S_USER T2   ON T1.USER_ID = T2.PAR_ROW_ID   WHERE ((T1.BC_BASE_TBL = @P1)   AND (T1.RECORD_ID = @P2))   ORDER BY T1.OPERATION_DT DESC  OPTION (FAST 40)   ',   @p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'  print 'fetch'  exec sp_cursorfetch @p2,2,4,1   exec sp_cursorclose @p2   

游标类型对性能影响的实例。下面的两个游标脚本分别创建并执行了dynamic和fast forward only两种类型的游标。

知识补充：

关系数据库中的操作会对整个行集起作用。由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一小部分行。游标不仅可提供这种机制，而且是对结果集的一种扩展。

游标通过执行以下操作来扩展结果集处理：

允许定位在结果集的特定行。 从结果集的当前位置检索一行或一部分行。 支持对结果集中当前位置的行进行数据修改。 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

不理想的游标类型：(dynamic游标)

declare @p1 int  set @p1=NULL    declare @p2 int  set @p2=0    declare @p5 int  set @p5=4098  declare @p6 int  set @p6=8193    declare @p7 int  set @p7=0     exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',  N'  SELECT       T1.CONFLICT_ID  FROM         dbo.S_AUDIT_ITEM T1              LEFT OUTER JOIN dbo.S_USER T2   ON T1.USER_ID = T2.PAR_ROW_ID      WHERE  ((T1.BC_BASE_TBL = @P1)    AND  (T1.RECORD_ID = @P2))      ORDER BY  T1.OPERATION_DT DESC    OPTION (FAST 40)  ',  @p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'     print 'fetch' exec sp_cursorfetch @p2,2,4,1     exec sp_cursorclose @p2 

理想的游标类型(fast forward only游标)

declare @p1 int  set @p1=NULL    declare @p2 int  set @p2=0    declare @p5 int  set @p5=4112  declare @p6 int  set @p6=8193    declare @p7 int  set @p7=0     exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',  N'  SELECT       T1.CONFLICT_ID  FROM         dbo.S_AUDIT_ITEM T1              LEFT OUTER JOIN dbo.S_USER T2   ON T1.USER_ID = T2.PAR_ROW_ID      WHERE  ((T1.BC_BASE_TBL = @P1)    AND  (T1.RECORD_ID = @P2))      ORDER BY  T1.OPERATION_DT DESC    OPTION (FAST 40)  ',  @p5 output,@p6 output,@p7 output,'S_SRV_REQ','1-WUQTM6'     select @p1, @p2, @p5, @p6, @p7   print '2' exec sp_cursorfetch @p2,2,1,1    print '3' exec sp_cursorclose @p2 

注：脚本中用到的和游标有关的存储过程，请参考：http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec

一、如何解读游标的类型

sp_cursorprepexec [@handle =] statement_handle OUTPUT,       [@cursor =] cursor_handle OUTPUT,       [@paramdef =] N'parameter_name data_type, [,...n]'        [@stmt =] N'stmt',       [, [@scrollopt =] scroll_options OUTPUT]       [, [@ccopt =] concurrency_options OUTPUT]       [, [@rowcount =] rowcount OUTPUT]   @scrollopt  

 

[@ccopt

 

@p5=4098 转成16进制就是1002，对应的游标类型为Parameterized query + Dynamic cursor

@p5=4112 转成16进制就是1010，对应的游标类型为Parameterized query + Fast forward-only cursor

问题的现象是，左边的游标类型下，该脚本执行时间远大于右边的游标类型。

二、如何比较两个不同执行计划的优劣

在继续以下内容之前，这里要介绍一些查看和比较语句执行计划的知识。通常情况下，我们从management studio中输出图形界面的执行计划进行直观的比较，查看每个表用的访问方式，使用index还是table scan，使用了哪个index，表和表之间使用的join 方式有什么不一样。但是假如是一个复杂的语句，在不同的数据库上使用了不同的执行计划，对于同样表的访问，使用了不同的index，如何比较哪种执行计划更加优化呢 比较整个语句的执行时间是一种方法，但是这个比较的结果并不准确。语句的执行时间很容易受到其他外在因素的影响：

1. 不同机器上CPU，memory和disk的性能会影响执行时间。

2. 测试的时候有没有其他人在使用同样的数据造成阻塞

3. 其他人堆数据库的使用占用了系统资源

以上这些原因都有可能影响的语句的执行时间，从而影响到我们对语句性能结果的比较。因此我们不能把语句的执行时间作为衡量语句性能的标准。

这里介绍一种比较语句cost的方法。我们对于语句cost的衡量，主要是通过比对语句总的logical reads.

我们可以通过在management studio里的query window 执行”set statistics io on” ，在当前窗口中对所有执行的语句输出信息：

set statistics io on select * from dbo.test_TicketFact  set statistics io on 

执行语句两次，以消除physical reads和read-ahead reads的影响。

输出的结果如下：

(320 row(s) affected)  Table 'test_TicketFact'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

这里打印出来了语句中访问过的table的访问次数，总共的logical reads，physical reads等信息

这里我们需要关注的是logic reads的值，这个值实际上决定了对于IO和DISK以及内存的消耗。当语句是第一次执行，我们会看到physical reads的数字，以,而当语句第二次执行的时候，这些数据已经被读到memory里面了，因此我们会看到physical read和read-ahead reads都变为0，而logical reads的值就变成了语句所有使用的data的量。

为什么logic reads是我们需要关注的值呢 因为logic reads决定了语句要访问数据的量。假如我们的系统瓶颈在IO上，一旦语句需要访问的数据从内存里面清除，这个语句原本所有的logic reads会全部转为physical reads.因此那些大量使用logic reads就是可能导致大量physical reads的元凶。假如我们的bottleneck是CPU，这些做大量logical reads的语句同样有可能导致大量的memory 读，而读memory是需要消耗CPU资源的。因此，无论是CPU，memory还是DISK的瓶颈，那些做大量logical reads的语句都非常可能是造成问题的原因。

由以上内容，我们可以得出结论，语句的性能好坏，取决与这个语句做了多少logical reads.因此，假如同样的语句，使用了不同的执行计划，那么总的logical reads低的那个执行计划就是相对优化的。

三、分析本案例中两种游标的执行计划

现在我们回到需要研究的脚本，在这里，语句是一样的，不同的只是游标的类型。不同的执行时间说明很可能这个语句使用了不同的执行计划。现在问题变成了，同样语句使用了不同的执行计划，得到了不同的执行时间。我们首先从”set statistics io on” 的结果入手：

1.左边使用dynamic游标有大量的逻辑读，情况如下：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 9770695, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

2．而右边使用fast forward only游标只有三次逻辑读，情况为：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

从这里输出的结果的区别，说明了在table S_AUDIT_ITEM上SQL Server使用了不同的访问方式

接下来我们分析两个脚本的执行计划：

1. dynamic游标对应的不理想的执行计划中，SQL Server选择了索引扫描(index scan)及索引S_AUDIT_ITEM_M4来查阅S_AUDIT_ITEM表。因此我们会在这里看到大量的IO。

 

这个索引扫描实际上访问了整张表的数据。

2.而fast forward only游标对应的理想的执行计划中，SQL Server选择的是索引查找(index seek)及索引S_AUDIT_ITEM_M3来查阅S_AUDIT_ITEM表。所以我们只看到3个逻辑读。索引S_AUDIT_ITEM_M3包含4个列，第一个列是RECORD_ID。另外，在语句中，有WHERE条件T1.RECORD_ID=@P2

 

四、尝试解决问题

首先我们尝试更新统计信息：UPDATE STATISTICS ON S_AUDIT_ITEM WITH FULLSCAN，但是这个操作在此问题案例中没有作用。

从以上的分析中，我们已经发现，假如使用index S_AUDIT_ITEM_M3访问S_AUDIT_ITEM表，得到的执行计划非常好，我们可以直接用index hint来解决这个问题：

declare @p1 int set @p1=NULL  declare @p2 int set @p2=0   declare @p5 int set @p5=4098   declare @p6 int set @p6=8193   declare @p7 int set @p7=0   exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',   N'   SELECT T1.CONFLICT_ID   FROM dbo.S_AUDIT_ITEM T1 with (INDEX=S_AUDIT_ITEM_M3) /* 解决方案2 */   LEFT OUTER JOIN dbo.S_USER T2   ON T1.USER_ID = T2.PAR_ROW_ID   WHERE ((T1.BC_BASE_TBL = @P1)   AND (T1.RECORD_ID = @P2))   ORDER BY T1.OPERATION_DT DESC  OPTION (FAST 40)   ',   @p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'  print 'fetch'  exec sp_cursorfetch @p2,2,4,1   exec sp_cursorclose @p2