游标脚本性能问题解决与分析

转自

http://blogs.msdn.com/b/apgcdsd/archive/2011/03/30/1-cursor-performance-analysis.aspx

http://blogs.msdn.com/b/apgcdsd/archive/2011/04/01/2-cursor-performance-analysis.aspx

http://blogs.msdn.com/b/apgcdsd/archive/2011/04/06/3-cursor-performance-analysis.aspx

http://blogs.msdn.com/b/apgcdsd/archive/2011/04/11/4-cursor-performance-analysis.aspx

第一部分：游标类型对性能影响的实例引出

下面的两个游标脚本分别创建并执行了dynamic和fast forward only两种类型的游标：

不理想的游标类型：（dynamic游标）

理想的游标类型（fast forward only游标）

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)',

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

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)',

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

Value	Description
0x0001	Keyset-driven cursor.
0x0002	Dynamic cursor.
0x0004	Forward-only cursor.
0x0008	Static cursor.
0x0010	Fast forward-only cursor.
0x1000	Parameterized query.
0x2000	Auto fetch.
0x4000	Auto close.
0x8000	Check acceptable types.
0x10000	Keyset-driven acceptable.
0x20000	Dynamic acceptable.
0x40000	Forward-only acceptable.
0x80000	Static acceptable.
0x100000	Fast forward-only acceptable.

@ccopt

Value	Description
0x0001	Read-only.
0x0002	Scroll locks.
0x0004	Optimistic. Checks timestamps and, when not available, values.
0x0008	Optimistic. Checks values (non-text, non-image).
0x2000	Open on any SQL.
0x4000	Update keyset in place.
0x10000	Read-only acceptable.
0x20000	Locks acceptable.
0x40000	Optimistic acceptable.

@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，如何比较哪种执行计划更加优化呢？比较整个语句的执行时间是一种方法，但是这个比较的结果并不准确。语句的执行时间很容易受到其他外在因素的影响：

不同机器上CPU，memory和disk的性能会影响执行时间。
测试的时候有没有其他人在使用同样的数据造成阻塞
其他人堆数据库的使用占用了系统资源

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

这里介绍一种比较语句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 '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 '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)',

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

第二部分：游标的分类及特点

从上面两个脚本执行情况的对比中可以看出，游标的选择对语句执行的性能具有一定的影响。

在SQL Server联机丛书上列出了不止十种游标类型，但是所有游标都可以被划到两大类别：

通过从首次得到结果的临时拷贝映像静态进行
每次fetch都通过动态进行且真正查阅表

STATIC、KEYSET、READ_ONLY和FAST_FORWARD属于第一大类，FORWARD_ONLY、DYNAMIC和OPTIMISTIC属于第二大类。

下面我们来进行一定的比较分析，并学习如何使用各种游标。在进行这部分之前，我们要引入另一个set statistics的方法： set statistics profile on

这个option会帮助我们打印出文本格式的执行计划和每一布的执行统计信息。这个部分的执行语句执行计划都是通过这个option打印的。

首先，我们把游标脚本中的SQL抽取出来直接运行而不使用游标：

SELECT T1.* 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 = 'S_PARTY' AND T1.RECORD_ID = '1-10350J'

ORDER BY T1.OPERATION_DT DESC

执行情况如下：逻辑读15次，使用的是索引查找（index seek）

Table 'S_USER'. Scan count 1, logical reads 260, 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 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

执行计划为：

Rows Executes StmtText

-------------------- -------------------- ---------------------------------------------------------------------------------------------------------

4 1 SELECT T1.* 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 = 'S_PARTY' AND T1.RECORD_ID = '1-10350J' ORDER BY T1.OPERATION_DT DESC 1 1 0 NULL NULL

4 1 |--Sort(ORDER BY:([T1].[OPERATION_DT] DESC))

4 1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_

4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [T1].[ROW_ID]) OPTIMIZED)

4 1 | |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]), SEEK

4 4 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [

66908 4 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))

2. 下面通过T-SQL语句打开一个游标。注意，这里创建的游标为dynamic类型，因为新声明的游标默认类型为dynamic。。本文开头使用的存储过程是调用API游标的写法，这里是用T-SQL语句打开游标，两种写法使用的游标类型和执行的语句是完全一样的。

declare @CONFLICT_ID int

declare curTest cursor

FOR

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 = 'S_PARTY' AND T1.RECORD_ID ='1-10350J'

ORDER BY T1.OPERATION_DT

OPEN curTest

FETCH NEXT FROM curTest

INTO @CONFLICT_ID

CLOSE curTest

deallocate curTest

执行情况为：逻辑读明显增多，使用索引扫描（index scan）

Table 'Worktable'. Scan count 0, 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_USER'. Scan count 1, logical reads 64, 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 3026834, physical reads 1292, read-ahead reads 5574, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

执行计划如下：

Rows Executes StmtText

-------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------

1 1 FETCH NEXT FROM curTest

INTO @CONFLICT_ID

1 1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as

1 1 |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))

1 1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as

1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))

1007751 1 | |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS

1 1007751 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS

16401 1 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))

接下来，我们使用其他类型的游标进行测试，从它们的测试结果会发现：

当使用STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型的游标，可以得到理想的执行计划（索引S_AUDIT_ITEM_M3上使用索引查找）。

但是，如果使用其他第二类游标类型，得到的执行计划就不甚理想了（索引S_AUDIT_ITEM_M4上使用索引扫描）。

从上面的测试，我们知道STATIC、KEYSET、READ_ONLY及FAST_FORWARD游标可以带给我们同样的理想结果。那么，这些游标有什么共同点？

我们可以分析一下两大游标类型执行计划的不同：

STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型游标的执行计划：

Executes StmtText

-------------------- --------------------------------------------------------------------------------------------------------------------

1 OPEN curTest

1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[CONFLICT_ID] as

1 |--Sequence Project(DEFINE:([Expr1008]=i4_row_number))

1 |--Segment

1 |--Sort(ORDER BY:([T1].[OPERATION_DT] ASC))

1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[U

1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]) OPTIMIZED)

1 | |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]),

4 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1]

4 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))

Executes StmtText StmtId NodeId

-------------------- ----------------------------------------------------------------------------------------------- ----------- --------

1 FETCH NEXT FROM curTest INTO @CONFLICT_ID 2 1

1 |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD) 2 2

dynamic类型游标的执行计划

Executes StmtText

---------------------------------------------------------------------------------------------------------------------------------------

1 FETCH NEXT FROM curTest

1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as [T1].[ROW_ID]

1 |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))

1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_ID]=[testcurso

1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))

1 | |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS [T1]), ORDERED BACKWARD)

1007751 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [T1]), SEEK:([T

1 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))

比较一下两个执行计划的FETCH NEXT部分（SQL Server在游标打开阶段不会读取表）：在第一个执行计划中，FETCH是直接从临时对象CWT中得到行，然后从CWT.ROWID中找到相应范围。而在第二个计划中，FETCH是动态的而且是真正对表进行了读取，从表中取得数据。

第三部分、几种解决方法殊途同归

根据我们更多的分析和测试，以下几种方法都可以解决这个性能问题

使用top 10
使用with (INDEX=S_AUDIT_ITEM_M3)
除去ORDER BY
添加索引 ANZ_Custom_Audit_item_01

具体实现为：

declare @CONFLICT_ID int

declare curTest cursor

Dynamic

TYPE_WARNING

FOR

SELECT --top 10 /* 解决方案1 */

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 = 'S_PARTY' AND T1.RECORD_ID ='1-10350J'

ORDER BY T1.OPERATION_DT /* 解决方案3 - Fast query when removed */

OPEN curTest

FETCH NEXT FROM curTest

INTO @CONFLICT_ID

CLOSE curTest

deallocate curTest

/* 解决方案4 */

CREATE NONCLUSTERED INDEX [ANZ_Custom_Audit_item_01] ON [dbo].[S_AUDIT_ITEM]

(

[RECORD_ID] ASC,

[BC_BASE_TBL] ASC,

[OPERATION_DT] DESC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

对于上述四种解决方案：

1．解决方案1和2可以由同一原因说明：

我们在定于游标的时候添加TYPE_WARNING来深入研究这个问题。添加TYPE_WARNING后再次执行语句，出现警告信息：The created cursor is not of the requested type（创建的游标不是所需的类型）。这个信息说明，语句实际执行的时候，游标类型发生了变化，不再是我们定义的动态游标了。为了跟踪游标类型的转换，我们打开Profiler Trace并把所有游标对象下面的事件都添加上，再次执行语句，看到profiler trace里面抓取了一个CursorImplictConversion。

这里的CursorImplictConversion事件类如下表说明：

BinaryData

image

Resulting cursor type. Values are:

1 = Keyset

2 = Dynamic

4 = Forward only

8 = Static

16 = Fast forward

也就是说，我们使用方法1和2，实际上等同于我们使用了静态游标。在这个部分之前的测试中，我们已经知道了静态游标可以得到好的执行计划。在语句执行的过程中，游标隐式地转换为了static类型的。

2.解决方案3和4也可以用同一个原因来说明。

解决方案3去掉了ORDER BY语句，从使用索引扫描（index scan）变成了索引查找（index seek），不用转换游标类型就解决了该问题。

对于解决方案4，新建的索引是一个cover index，

所以如果我们创建下面的索引，一样可以解决这个性能问题。

CREATE NONCLUSTERED INDEX [ANZ_Custom_Audit_item_01] ON [dbo].[S_AUDIT_ITEM]

(

[RECORD_ID] ASC,

[BC_BASE_TBL] ASC,

[OPERATION_DT] DESC

)

在分析3和4之前，我们检查一下index 的定义：

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M3] ON [dbo].[S_AUDIT_ITEM]

(

[RECORD_ID] ASC,

[BUSCOMP_NAME] DESC,

[OPERATION_DT] ASC

)

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M4] ON [dbo].[S_AUDIT_ITEM]

(

[OPERATION_DT] DESC

)

为什么在动态游标的情况下，SQL Server选择这个不好的索引呢？在语句中有这个一个排序的子句：ORDER BY T1.OPERATION_DT desc

如果我们使用动态游标的时候，又同时指定了order by的顺序，那么这个动态游标一定要保证其滚动的顺序和位置。而动态游标又是动态从表上获取数据，因此动态游标+order by必须要在一个包含了order by的column和同样的排序顺序的index上滚动，这就是为什么SQL Server 坚持在动态游标下使用index S_AUDIT_ITEM_M4，即使SQL Server知道使用index S_AUDIT_ITEM_M4会导致不好的性能。

所以如果我们移掉order by子句，SQL Server就不再坚持使用index S_AUDIT_ITEM_M4。或者我们创建新的cover index，这个index包含了语句所使用的所有column，并且包含了order by 的列OPERATION_DT ，因此这个语句可以快速的缩小数据筛选范围，并且提供动态游标需要滚动的列排序。

一个有趣的测试

最后，让我们做一个有趣的测试：

仍旧使用上面的游标脚本，移除索引ANZ_Custom_Audit_item_01和索引S_AUDIT_ITEM_M4，会发生什么现象？这时语句中还是包含了order by，但是表上没有任何index提供了order by的column所需要的顺序。

从profiler trace里面我们发现，游标类型进行了转变，语句使用索引S_AUDIT_ITEM_M3去访问table S_AUDIT_ITEM：创建的游标类型不是所需类型。动态游标变成了静态游标。

下表对SQL Server游标类型隐式转换进行了解释：

Step	Conversion triggered by	Forward-only	Keyset-driven	Dynamic	Go to step
1	查询中FROM从句没有查阅任何表	变Static	变Static	变Static	完成
2	查询包括：集合了GROUP BY UNION DISTINCT的选择列表	变Static	变Static.	变Static	完成
3	查询产生了一个内部工作表，比如，ORDER BY的列没有被索引覆盖到	变 keyset.		变keyset.	至5
4	查询在链接服务器（linked server）中查阅远程表	变 keyset.		变keyset.	至5
5	查询查阅了至少一个没有唯一索引的表，仅适用于T-SQL游标。		变 static.		完成

从这个表格中的内容来看，只有Forward_only, Keyset_driven 和Dynamic游标会发生类型转换。

根据表格内容，回过来分析我们上面的脚本，它发生变化的步骤为：步骤3+步骤5，动态游标由于条件3，转成了keyset游标，然后又由于条件5，变成了静态游标。

到处，我们在这个问题中提供了4种解决问题的方法，并逐一分析了每种方法能够解决问题的原因。这个性能问题其实可以总结为两句话：

尽量使用Fast Forward only游标。
如果必须使用dynamic游标，而定义游标的语句又有order by，保证table上有一个具有可以用来order by的index，并且这个index也包含了其他有效的可以最大缩小数据反问的where 条件中所使用的column。

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

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

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

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

当SELECT语句连接包含trigger table（INSERTED/DELETED）的一个或多个表，游标被转换成static类型。
当SELECT语句查阅text、ntext或image列，如果SQL Server OLE DB访问接口或使用了SQL Server ODBC驱动器，游标被转换成dynamic类型。
当Fast Forward-only游标不是只读的，它会被转换成dynamic游标。动态游标是可以用来update当前滚动到的数据行并将更新回写到table中的。
当SELECT语句是一个查阅了链接服务器中一个或多个远程表进行分部查询，游标被转换成keyset-driven类型。
如果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。

除非没有其他选择，避免使用static/insensitive和keyset游标，这是因为他们会在TEMPDB中创建临时表，这会增加整体开销导致内存抢夺问题。
使用游标会减少并行而导致不必要的锁和阻塞的发生。要避免这种情况，可以适当地使用READ_ONLY游标选项；或者在你需要进行更新时，使用OPTIMISTIC游标选项来减少锁的产生。避免使用SCROLL_LOCKS游标选项，因为它会减少并行。
从应用程序的角度尽快的加载并且滚动游标到最后一行。这会释放在创建游标时随之创建的共享锁，从而释放SQL Server资源。
如果你的应用程序需要手动滚动记录并进行更新而必须使用游标，请避免使用客户端游标，除非返回的行数很少或数据是静态的。如果行数很大，或者数据不是静态的，可以考虑使用服务器端的keyset游标。由于客户端和服务器端网络拥挤的减少，性能问题很可能会出现。为了优化性能，可能需要在实际情况下对两种游标类型都进行尝试，决定哪种更适合需要应用的系统。
6. 如果游标需要执行JOIN操作，keyset和static游标通常比dynamic游标快。

（三）深入了解Fast_forward游标

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

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

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

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

什么是动态计划？

动态计划可以增量地进行，在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。这会导致一个次优的计划。

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

在某些查询中，比如使用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）中。

fast_forward游标如何解决该问题？

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

应用开发者可以做什么？

（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>上使用索引。通常通过增加索引列来包括相等谓词后的排序列。

posted @ 2014-06-09 00:40 princessd8251 阅读(912) 评论(0) 收藏举报

刷新页面返回顶部