遊標腳本性能問題詳解之案例實踐篇

    【IT168 專稿】遊標類型對性能影響的實例。下面的兩個遊標腳本分別創建並執行了dynamic和fast forward only兩種類型的遊標。

　　不理想的遊標類型：(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