游標(biāo)腳本性能問(wèn)題詳解之案例實(shí)踐篇
游標(biāo)類型對(duì)性能影響的實(shí)例。下面的兩個(gè)游標(biāo)腳本分別創(chuàng)建并執(zhí)行了dynamic和fast forward only兩種類型的游標(biāo)。
知識(shí)補(bǔ)充：
關(guān)系數(shù)據(jù)庫(kù)中的操作會(huì)對(duì)整個(gè)行集起作用。由 SELECT 語(yǔ)句返回的行集包括滿足該語(yǔ)句的 WHERE 子句中條件的所有行。這種由語(yǔ)句返回的完整行集稱為結(jié)果集。應(yīng)用程序并不總能將整個(gè)結(jié)果集作為一個(gè)單元來(lái)有效地處理。這些應(yīng)用程序需要一種機(jī)制以便每次處理一行或一小部分行。游標(biāo)不僅可提供這種機(jī)制，而且是對(duì)結(jié)果集的一種擴(kuò)展。
游標(biāo)通過(guò)執(zhí)行以下操作來(lái)擴(kuò)展結(jié)果集處理：
- 允許定位在結(jié)果集的特定行。
- 從結(jié)果集的當(dāng)前位置檢索一行或一部分行。
- 支持對(duì)結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改。
- 為由其他用戶對(duì)顯示在結(jié)果集中的數(shù)據(jù)庫(kù)數(shù)據(jù)所做的更改提供不同級(jí)別的可見(jiàn)性支持。
不理想的游標(biāo)類型：(dynamic游標(biāo))
- 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
理想的游標(biāo)類型(fast forward only游標(biāo))
- 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
注：腳本中用到的和游標(biāo)有關(guān)的存儲(chǔ)過(guò)程，請(qǐng)參考：http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec
一、如何解讀游標(biāo)的類型
- 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 轉(zhuǎn)成16進(jìn)制就是1002，對(duì)應(yīng)的游標(biāo)類型為Parameterized query + Dynamic cursor
@p5=4112 轉(zhuǎn)成16進(jìn)制就是1010，對(duì)應(yīng)的游標(biāo)類型為Parameterized query + Fast forward-only cursor
問(wèn)題的現(xiàn)象是，左邊的游標(biāo)類型下，該腳本執(zhí)行時(shí)間遠(yuǎn)大于右邊的游標(biāo)類型。
#p#
二、如何比較兩個(gè)不同執(zhí)行計(jì)劃的優(yōu)劣
在繼續(xù)以下內(nèi)容之前，這里要介紹一些查看和比較語(yǔ)句執(zhí)行計(jì)劃的知識(shí)。通常情況下，我們從management studio中輸出圖形界面的執(zhí)行計(jì)劃進(jìn)行直觀的比較，查看每個(gè)表用的訪問(wèn)方式，使用index還是table scan，使用了哪個(gè)index，表和表之間使用的join 方式有什么不一樣。但是如果是一個(gè)復(fù)雜的語(yǔ)句，在不同的數(shù)據(jù)庫(kù)上使用了不同的執(zhí)行計(jì)劃，對(duì)于同樣表的訪問(wèn)，使用了不同的index，如何比較哪種執(zhí)行計(jì)劃更加優(yōu)化呢?比較整個(gè)語(yǔ)句的執(zhí)行時(shí)間是一種方法，但是這個(gè)比較的結(jié)果并不準(zhǔn)確。語(yǔ)句的執(zhí)行時(shí)間很容易受到其他外在因素的影響：
1. 不同機(jī)器上CPU，memory和disk的性能會(huì)影響執(zhí)行時(shí)間。
2. 測(cè)試的時(shí)候有沒(méi)有其他人在使用同樣的數(shù)據(jù)造成阻塞
3. 其他人堆數(shù)據(jù)庫(kù)的使用占用了系統(tǒng)資源
以上這些原因都有可能影響的語(yǔ)句的執(zhí)行時(shí)間，從而影響到我們對(duì)語(yǔ)句性能結(jié)果的比較。因此我們不能把語(yǔ)句的執(zhí)行時(shí)間作為衡量語(yǔ)句性能的標(biāo)準(zhǔn)。
這里介紹一種比較語(yǔ)句cost的方法。我們對(duì)于語(yǔ)句cost的衡量，主要是通過(guò)比對(duì)語(yǔ)句總的logical reads.
我們可以通過(guò)在management studio里的query window 執(zhí)行”set statistics io on” ，在當(dāng)前窗口中對(duì)所有執(zhí)行的語(yǔ)句輸出信息：
- set statistics io on
- select * from dbo.test_TicketFact
- set statistics io on
執(zhí)行語(yǔ)句兩次，以消除physical reads和read-ahead reads的影響。
輸出的結(jié)果如下：
- (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.
這里打印出來(lái)了語(yǔ)句中訪問(wèn)過(guò)的table的訪問(wèn)次數(shù)，總共的logical reads，physical reads等信息
這里我們需要關(guān)注的是logic reads的值，這個(gè)值實(shí)際上決定了對(duì)于IO和DISK以及內(nèi)存的消耗。當(dāng)語(yǔ)句是第一次執(zhí)行，我們會(huì)看到physical reads的數(shù)字，以,而當(dāng)語(yǔ)句第二次執(zhí)行的時(shí)候，這些數(shù)據(jù)已經(jīng)被讀到memory里面了，因此我們會(huì)看到physical read和read-ahead reads都變?yōu)?，而logical reads的值就變成了語(yǔ)句所有使用的data的量。
為什么logic reads是我們需要關(guān)注的值呢?因?yàn)閘ogic reads決定了語(yǔ)句要訪問(wèn)數(shù)據(jù)的量。如果我們的系統(tǒng)瓶頸在IO上，一旦語(yǔ)句需要訪問(wèn)的數(shù)據(jù)從內(nèi)存里面清除，這個(gè)語(yǔ)句原本所有的logic reads會(huì)全部轉(zhuǎn)為physical reads.因此那些大量使用logic reads就是可能導(dǎo)致大量physical reads的元兇。如果我們的bottleneck是CPU，這些做大量logical reads的語(yǔ)句同樣有可能導(dǎo)致大量的memory 讀，而讀memory是需要消耗CPU資源的。因此，無(wú)論是CPU，memory還是DISK的瓶頸，那些做大量logical reads的語(yǔ)句都非常可能是造成問(wèn)題的原因。
由以上內(nèi)容，我們可以得出結(jié)論，語(yǔ)句的性能好壞，取決與這個(gè)語(yǔ)句做了多少logical reads.因此，如果同樣的語(yǔ)句，使用了不同的執(zhí)行計(jì)劃，那么總的logical reads低的那個(gè)執(zhí)行計(jì)劃就是相對(duì)優(yōu)化的。
#p#
三、分析本案例中兩種游標(biāo)的執(zhí)行計(jì)劃
現(xiàn)在我們回到需要研究的腳本，在這里，語(yǔ)句是一樣的，不同的只是游標(biāo)的類型。不同的執(zhí)行時(shí)間說(shuō)明很可能這個(gè)語(yǔ)句使用了不同的執(zhí)行計(jì)劃?，F(xiàn)在問(wèn)題變成了，同樣語(yǔ)句使用了不同的執(zhí)行計(jì)劃，得到了不同的執(zhí)行時(shí)間。我們首先從”set statistics io on” 的結(jié)果入手：
1.左邊使用dynamic游標(biāo)有大量的邏輯讀，情況如下：
- 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游標(biāo)只有三次邏輯讀，情況為：
- 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.
從這里輸出的結(jié)果的區(qū)別，說(shuō)明了在table S_AUDIT_ITEM上SQL Server使用了不同的訪問(wèn)方式
接下來(lái)我們分析兩個(gè)腳本的執(zhí)行計(jì)劃：
1. dynamic游標(biāo)對(duì)應(yīng)的不理想的執(zhí)行計(jì)劃中，SQL Server選擇了索引掃描(index scan)及索引S_AUDIT_ITEM_M4來(lái)查閱S_AUDIT_ITEM表。因此我們會(huì)在這里看到大量的IO。
這個(gè)索引掃描實(shí)際上訪問(wèn)了整張表的數(shù)據(jù)。
2.而fast forward only游標(biāo)對(duì)應(yīng)的理想的執(zhí)行計(jì)劃中，SQL Server選擇的是索引查找(index seek)及索引S_AUDIT_ITEM_M3來(lái)查閱S_AUDIT_ITEM表。所以我們只看到3個(gè)邏輯讀。索引S_AUDIT_ITEM_M3包含4個(gè)列，第一個(gè)列是RECORD_ID。另外，在語(yǔ)句中，有WHERE條件T1.RECORD_ID=@P2
#p#
四、嘗試解決問(wèn)題
首先我們嘗試更新統(tǒng)計(jì)信息：UPDATE STATISTICS ON S_AUDIT_ITEM WITH FULLSCAN，但是這個(gè)操作在此問(wèn)題案例中沒(méi)有作用。
從以上的分析中，我們已經(jīng)發(fā)現(xiàn)，如果使用index S_AUDIT_ITEM_M3訪問(wèn)S_AUDIT_ITEM表，得到的執(zhí)行計(jì)劃非常好，我們可以直接用index hint來(lái)解決這個(gè)問(wèn)題：
- 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
