遊標指令碼效能問題解決與分析 (4) - Cursor Performance Analysis

第四部分、遊標相關知識講解

（一）Fast Forward Only（快速只進）遊標分析

Microsoft SQL Server 實現了一種稱作fast forward only遊標的效能優化。http://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 )選擇一箇中性值。

（2）       通過呼叫sp_cursorprepare或OPTION(RECOMPILE)避免在不同引數下的計劃重用。

（3）       避免在遊標中使用ORDER BY

（4）       使用相等謂詞及多列索引來支援序列。稍微修改一下上面的例子，如果我們在SUBTOTAL範圍有一個包含值{S,M,L,XL}的計算列SIZE，我們可以查詢WHERE SIZE=’XL’ ORDER BY DATE，並在上使用索引。通常通過增加索引列來包括相等謂詞後的排序列。