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

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

 

二、死結的原因及相關處理

       死結的原因很多，尤其是前端應用程式沒有合理的使用事務，或者對錯誤處理不當而導致事務長期持有而沒有關閉。接下來講講最常見的幾種死結情況，並提供可能的解決方案。

1.費時的查詢事務

2.不正確的事務或事務隔離等級設定

3.事務未正確處理

4.未檢測到的分布式死結

5.鎖定資料粒度太高或太低

6.Compile Blocking

 

  （一）費時的查詢事務

       當查詢或事務所花的時間較長時，可由SQL SERVER 2005/2008 動態管理檢視sys.dm_exec_requests提供相關資訊（也可觀察sysprocesses系統檢視表），如status欄位為“running”，wait_type為非“NULL”值。“running”代表該進程依然在執行，而wait_type則表示該進程是否在等待資源。如。

 

       這也可以從SQL SERVER 2005的Microsoft SQL Server Management Studio管理工具中的活動監視器--》進程資訊--》查看相關資訊。如，所圈出的相關欄位可以觀察進程之間的相關資訊。

 

 

 

      如所示，進程“55”被“54”鎖住，也可以從“等待類型”列中看出相關資訊。

     通過SQL PROFILER工具中觀察“T SQL”事件下的“SQL StmtCompleted/SQL BatchComplete”，或是“預存程序”事件類別目錄下的“SP StmtCompleted/SP BatchComplete/RPC Complete ”等事件，可觀察SQL語句執行情況，並通過TextData(呈現T-SQL語句執行內容)及Duration(語句執行所需的時間)欄位判斷哪一句語句是否執行時間過長而導致鎖定行為。如1、圖2。

 

圖1

      1中圈出來的地方，沒有結束時間，所以此SQL語句還在繼續等待。

 

圖2

     2中圈出來的地方，雖然有執行結束時間，但是duration的執行時間過長，也就是說此SQL語句等待了這麼長的時間，等待其他事務釋放資源。

 

     如果查詢語句使用大量系統資源導致查詢耗時過長，可能伴隨的現象有：處理器，硬碟I/O，記憶體等的使用率很高。SQL PROFILER工具中的“錯誤和警告”事件類別目錄中的Missing Column Statistics，產生過多“預存程序”事件類型的SP:Recompile事件也值得注意，前者表示無法產生有效執行計畫，後者表示預存程序的編寫方式，無法提供快取執行計畫。“錯誤和警告”事件類別目錄中的Hash warning和Sort warning則反映可能沒有好的索引可供使用。

 

建議解決方案

      如果事務執行時間過長，一直鎖住資源不放可能導致其他想要執行的事務被鎖。例如：設定交易隔離等級為“可重複讀取”，當查詢語句（SELECT)執行時間過長時，則更新語句（UPDATE）則無法對資料進行更新，最終導致系統癱瘓。出現此類情況，可以試著使用以下方式進行解決

1.新增或設定適當的索引以增加查詢速度

2.更新統計資料以避免執行計畫使用舊的統計資訊

3.重新設計資料表、預存程序等對象

4.檢查是否過度使用觸發器和遊標。

如果無法通過以上方式提高 工作效率，則可能要考慮修改系統的工作流程

1. 分割工作，不要同時執行所有的需求

2. 切割工作時間，將工作排至系統不繁忙的時段執行

3. 切割工作屬性，將工作交給另一個資料庫去執行，把查詢與更新分成兩個資料庫來執行。

 

 

（二）不正確的事務或交易隔離等級設定

      當死結是由於不正確的事務或交易隔離等級設定所導致時，SQL SERVER 2005/2008動態管理檢視sys.dm_exec_requests會提供相關資訊，該SESSION_ID的status欄位值為“running”，wait_type非“NULL”值，通過sys.dm_exec_session動態視圖的transaction_isolation_level欄位可以看出進程所設定的交易隔離等級。且從Microsoft SQL Server Management Studio管理工具中的“活動監視器--》進程資訊”視圖，該進程的“開啟的事務”欄位顯示為非“0”值，表示為該進程仍握有事務資源。

       通過SQL PROFILER工具尋找“TextData”，觀察前端傳遞命令中是否含有不當的事務設定，例如，設定隱含式事務（SET IMPLICIT_TRANSACTION ON）、事務隔離等級或是設定鎖定提示等。

 

建議解決方式：

       事務設定大多與實際商務邏輯有關，不容易界定是否有必要，如果你通過追蹤檔案找到不正確的事務或事略等級隔離設定時，也需要與開發人員討論設定的必要性。尤其是當事務中包含大量資料的運算的情況，可能需要研究如何切割成較小的事務，但仍需要符合原來的資料完整性和商務邏輯要求。

 

 

（三）事務未正確處理

 

      開啟了事務，但是沒有復原或沒有提交，形成了未提交事務。它的特徵與觀察方式與上面所述相同。從中可以看出進程“54”仍持有事務，但此進程停滯不做事，也無等待任何資源，但仍持有事務，從SQL SERVER 2005的Microsoft SQL Server Management Studio管理工具中的“活動監視器--》進程資訊”視圖，進一步觀察“上一批”欄位，檢查進程是否已經持有資源一段時間。

 

 

在SQL 2005(2008)中執行程式碼範例一，得到如。

 

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 工具中的事務事件類別目錄，錄製SQL SERVER所觸發的事務事件，也可以通過dbcc opentran （‘<資料庫>’）命令觀察針對某個資料庫執行最久的事務事件，由哪個程式擁有，如果沒有指定資料庫名稱或ID，則返回當前串連所在的資料庫執行最久的事務事件，一般未提交事務可能是由於未做好錯誤處理所造成的。

 執行dbcc opentran命令的之後，如。其中UID是無意義的。

 

 

     例如，執行命令逾時，放棄批處理但未復原事務。其中的錯誤處理，應該如下例一般。

If @@trancount>0   Rollback tran ---或是設定： Set XACT_ABORT on

 

(上述設定是指當SQL SERVER 在發生任何錯誤時，都要復原事務)