SQL SERVER 查詢效能最佳化——分析事務與鎖（五）

      SQL SERVER 查詢效能最佳化——分析事務與鎖（一）

      SQL SERVER 查詢效能最佳化——分析事務與鎖（二）

      SQL SERVER 查詢效能最佳化——分析事務與鎖（三）

 

上接SQL SERVER 查詢效能最佳化——分析事務與鎖（四）

 

（四）未檢測到的分布式死結

        某應用程式持有資料庫資源，開啟事務之後又與使用者互動，而在與使用者的互動過程中出現了錯誤，導致資料庫資源遲遲不能釋放。SQL SERVER 2005/2008 動態管理檢視sys.dm_exec_requests提供相關資訊，該SESSION_ID的status欄位值為“sleeping”，wait_type為“NULL”值。如果是SQL 2005則可以通過Microsoft SQL Server Management Studio管理工具中的“活動監視器--》進程資訊”視圖，該進程的“開啟事務欄位”顯示非“0”值。如。

 

 

      在SQL 2005(2008)中執行代碼，即SQL SERVER 查詢效能最佳化——分析事務與鎖（二）中的“例一”，也就是下面的代碼，得到如。

select spid 進程,STATUS 狀態, 登入帳號=SUBSTRING(SUSER_SNAME(sid),1,30),使用者機器名稱=SUBSTRING(hostname,1,12),是否被鎖住=convert(char(3),blocked),資料庫名稱=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待類型,last_batch 最後批處理時間,open_tran 未提交事務的數量from master.sys.sysprocesses--列出鎖住別人（在別的進程中blocked欄位中出現的值）但自己未被鎖住(blocked=0)Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

 

 

 

      由於應用程式持有事務，而且應用程式出錯之後，沒對事務的相應處理，也沒有需要等待的資源，但持有事務，與前一種（三）情況類似，但通過SQL PROFILER工具進行跟蹤，卻無法發現任何錯誤事件。

建議解決方式

     應用程式所造成的分布式死結，很難加以跟蹤分析，需要程式開發人員自行記錄該應用程式的行為，比較多使用者情況下，在進行哪些工作之後，系統就遲滯無法正常執行下去。這需要程式開發人員保持良好的開發習慣：事務越晚開啟越好，使用資源越少越好，一旦開啟了事務遲早關閉，事務執行過程中不要與使用者有任何互動，要輸入的參數或內容應該在開啟事務之前就應該輸入完畢，對相關資料的各種校正也要在開啟事務之前進行校正，事務應該只是在往資料庫中插入更新資料時開啟，插入更新完畢之後，就立即關閉。

 

 

（五）鎖定資料粒度太低或太高

        使用者佈建不當的鎖定粒度時，如果設定事務一律使用Row lock或table lock均可能產生問題，或是當系統資源使用過度，也很容易產生被鎖定的情形。

建議解決方式

       可以通過SQL PROFILER 觀察“TextData”欄位所呈現的SQL語句，觀察該應用程式是否設定了鎖定提示，若想要暫時停止鎖定提示造成的影響，可以通過以下語句

       Dbcc traceon(8755)

或以SQL SERVER 啟用參數-T 8755 來停止鎖定提示功能，若有改善，可以重新考慮從應用程式從新移除鎖定提示的可能性。

 

（六）Compile Blocking

         此現象是由於編譯預存程序導致被鎖定，在master.sys.sysprocesses視圖中或sp_lock預存程序中觀察到的等待資源欄位中的內容是“COMPILE”，或者使用SQL PROFILER 錄製過程中出現大量的“SP:REComplie”事件。由於重新編譯需要耗費CPU資源，所以，此種鎖定是在一長串的被鎖定串連中，單一鎖定者鎖定時間不長，但整個連結各點都有一點耗時，所以在連結尾端的被鎖定者需要等待較長時間。同時會出現CPU的使用率比較高。

        當預存程序中使用了快取資料表，而該快取資料表還需要設定結構，如需要要設定主鍵或者利用快取資料表開開啟遊標，則每次調用該預存程序進，都會要求重新編譯。或這個預存程序是當應用程式執行時，常常會被調用的熱門預存程序，就會出現Compile Blocking的狀況出現。

       但預存程序第一次使用時，也會需要編譯，所以不要一看到是在等待編譯，就識以為是COMPILE Blocking現象。

建議解決方式

      使用sp_executesql執行語句，即使用sp_executesql執行SQL語句，SQL語句不會編譯為預存程序執行計畫的一部分，因此在執行該類語句時，SQL SERVER 會自由的使用快取中的現有語句計劃，或者在執行階段建立新的執行計畫，不管任何一種情況，調用預存程序的計劃都不會受影響，也不必進行重新編譯。

      EXECUTE語句也有相同的效果，但不建議你使用。因為使用EXECUTE沒有使用SP_EXECUTESQL語句的效率高，因為前者不允許查詢參數化。

 

三、基本原則：

     1. 事務不可以跨批處理，語句越短越好，事務期間不要與使用者進行互動

     2. 小心處理逾時放棄，或者執行錯誤等情況。

    3. 正確建立索引。可以參考本人前面的相關文章。

    （如SQL Server 查詢效能最佳化——建立索引原則（一）

      SQL Server 查詢效能最佳化——覆蓋索引（一）等系列文章）

    4. 資料表最好有叢集索引，而且叢集索引的索引值不要太大，因為所有的非叢集索引儲存的都是叢集索引的索引值。不要使用經常需要進行更新的欄位做為叢集索引的索引值，因為叢集索引一旦進行了變更，則所有的非叢集索引也要跟著進行變更，導致大量的鎖定。索引建少了，影響查詢效率，建多了，浪費維護的資源與降低新增、修改、刪除的效率，所以建好索引之後，要小心觀察SQL SERVER 使用索引的情況，將多餘的索引刪除，對於資料密度大，或者查詢條件鑒別率太低的欄位不要建立索引。

     5. 盡量不要啟用Implicit Transaction，以免它長時間的持有事務。

     6. 盡量降低交易隔離等級

     7. 進行壓力測試以瞭解當大使用者量時，互動將造成何種程度的鎖定問題。

 

  四、 防止與處理死結

        1.盡量避免或儘快處理鎖定，當鎖定與被鎖定過多時，就可能造成死結

        2.訪問資源的順序要相同。例如串連A先訪問資源1，然後訪問資源2，而串連B的訪問順序與之相反，則可能發生死結。不要在開啟事務的情況下，調用外部程式，容易造成分布式死結。

        3.讓不同的串連使用相同的鎖定。或兩條串連因為修改相同的資源而互相鎖定，如果你的系統對於更新資料的正確性不做強制性要求，可以考慮使用sp_getbindtoken和sp_bindsession兩個系統預存程序，讓串連共用鎖定定，則兩條串連同時更新資料，也就可能造成資料更新遺失。

例：

use Testgocreate proc sp_upd_OPINION@OPINIONID varchar(20),@bindToken varchar(255) outputasexec sp_getbindtoken @bindToken outputupdate WBK_OPINION set OPINION_VALUE='true'where OPINION_ID=@OPINIONIDgocreate proc sp_upd_OPINION2@OPINIONID varchar(20),@bindSession varchar(255) outputasexec sp_bindsession @bindSession update WBK_OPINION set OPINION_VALUE='False'where OPINION_ID=@OPINIONID go----在第一個串連中執行declare @bindToken varchar(255)begin tranexec sp_upd_opinion 'PreEntryIDUse',@bindToken outputselect * from WBK_OPINIONselect @@trancount  --事務數量為select @bindToken

 

 

 

----在第二個串連中執行---其中@binToken是由第一個串連執行完畢之後，而擷取的begin tranexec sp_upd_opinion2 'PreEntryIDUse',@bindToken select * from WBK_OPINIONselect @@trancount  --事務數量為 

 

 

 

---在第三個串連中執行以下語句，由於不在同一個事務之內，所以會被鎖定update WBK_OPINION set OPINION_VALUE='true'where OPINION_ID='PreEntryIDUse'   rollback tran  ---復原

 

 

         1. 你可以根據以上代碼，自行編碼相應的測試樣本，通過Management studio分別使用三條串連來執行更新範例程式碼，你會發現享有相同TOKEN的兩條串連會一同更新，而其擷取的@@TRANCOUNT系統變數也是一樣的。而不在同一事務中的其他串連則會被鎖住。@@TRANCOUNT也與前述的事務無關。

        2.提交不同的資料訪問路徑。如果兩條不同串連的SQL語句，因為搶相同索引而導致死結，可以考慮為不同的訪問語句建立不同的索引，通過索引提示強制讓兩條串連訪問各自的索引。或者是兩條不同的串連訪問相同的資料表，如果引用不同的索引，但各自的訪問順序正彼此交錯，形成死結，則可強制兩條串連使用相同的索引，以維護訪問先後秩序。

      不管如何，採用此類解決方式時，都要考慮額外的效能損耗，因為你通過索引提示強制了索引訪問，讓查詢最佳化程式不能憑藉資料的特性使用最佳的索引。

 

五、發生死結後的處理

       通過設定SET DEADLOCK_PRIORITY LOW，讓不重要的事務自動放棄，並在這些串連執行的商務邏輯中，加上針對死結的錯誤處理。

      事實上，在非常複雜的高並發量的系統中，要完全預防死結，或者要知道什麼樣的使用者在特殊的訪問次序中會發生死結，是非常困難的。所以應用程式應該對死結錯誤“1205”要有相應的處理，以完成原有的商務邏輯的處理或是善後清除處理。