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

一、使用工具觀察與分析資料庫中鎖資訊

      對於並發系統、對於有大量讀寫資料庫操作的業務系統等，當多人同時訪問資料庫時，最複雜的情況之一就是大量的事務與資源糾纏在一起，相互被鎖而彼此等待，也稱為死結。當資料庫中出現很多很多鎖時，系統瞬間就無法提供正常服務。此時觀察系統資源的使用方式，會發現CPU使用率不高，記憶體佔用量也不高，還有很多未使用的記憶體，網路頻寬也充足，硬碟也不繁忙，通過資料庫管理工具查詢的話，SQL SERVER中的資料也正常無誤，但是使用系統的使用者訪問此資料庫時卻要需要等很多久很久，更多的就出現連線逾時，資料庫無響應。

      此現象類似高速公路上有大量的車輛進入，而在收費口卻只開了一個、二個。造成收費口的車輛擁堵，而後方卻又有大量的車輛湧入，這時所有的車輛都會慢下來，駕駛員彼此再減速刹車互相等待，最後導致後方的車子會被迫停下來，形成高速公路停車場。（就如第一次節假日高速公路免費通行那天，即2012年國慶高速公路大堵車的情形，高速公路成停車場）

      如果資料庫中在設計時，設計不當，設計有過熱資料表時，也就是所有應用程式的訪問行為都跟某張資料表有關，甚至集中訪問某些欄位，這就像前述例子中的所有車輛都在同一地點（高速公路收費口）駛上高速公路。這時耗時與快速的兩種訪問行為混合在一起，就像大貨車與小轎車一起爭車道，很容易發生彼此鎖定，形成死結而動彈不得的情況。

      此類效能延遲現象，尤其在資料使用一段較長時間之後容易發生，當新系統剛上線時，或SQL語句寫的不好，如Select * from SaleInfo 且沒有where條件，初期因資料量不大，可以瞬間取回結果而不影響別人。但當系統使用一段時間之後，資料庫中的數量增加，使用人數增多，使用方式改變。不好的SQL語句就出現運行遲緩，這時資料庫開始積聚等待處理的需求，也就是彼此幹擾而造成鎖定，互動作用後，可能瞬間癱瘓整個系統。當你觀察當前資料庫的鎖定時，可能會發現存在幾千個鎖定，很難理出頭緒。

       本文首先來討論可以使用哪些工具來觀察與分析SQL SERVER中的鎖資訊。當然，所有的效能調校都需要先瞭解情況，最好是可重複發生的情景，例如在每天什麼時間，多少使用者上線後，執行了什麼樣的功能後，等等。先通盤瞭解情況，然後評估SQL SERVER的問題，再進一步深入觀察SQL SERVER。

第一步，使用SQL SERVER提供圖形介面工具觀察哪些進程被加鎖。

 1 ) SQL SERVER 2005可以通過Microsoft SQL Server Management Studio的“管理--》活動監視器”--》“右鍵”--》“進程資訊”，這裡呈現串連、鎖定的各種細節。如1。

 

                                                                             圖1

 

 

2) SQL SERVER 2005 的Microsoft SQL Server Management Studio提供圖形介面工具中的資訊不會自動更新。重新整理“活動監視器”介面中的內容，需要手動使用滑鼠點擊上方工具列上的[重新整理] 按鈕。如2，是未點擊[重新整理] 按鈕之前的顯示的內容，圖3，是點擊[重新整理] 按鈕之後的，顯示的內容。

　　　　　　　　圖2

　　　　　　　　圖3

 

2） SQL SERVER 2005中可以通過中的設定，設定成自動更新的時間周期。如4。

 

 

                               圖4

 

 

4） SQL SERVER 2005的Microsoft SQL Server Management Studio中的“活動監視器--》進程資訊”提供相當多的資訊，其中“等待類型”與“阻塞者”可呈現進程彼此的關係及進程的狀態資訊。

如果要查詢“鎖”的細節資訊，則可以點擊右鍵視窗中的“活動監視器--》按進程分類的鎖”及“活動監視器--》按對象分類的鎖”兩個標記進行觀察。

活動監視器--》按進程分類的鎖

如5。

 

                                               圖5

活動監視器--》按對象分類的鎖

如6。

 

                                                圖6

 

接下來說一下SQL SERVER 2008中的活動監視器的使用：

1) SQL SERVER 2008的活動監視器就不在管理中了。一、點擊工具列上的表徵圖。二、使用右鍵點擊資料庫伺服器，如7。然後都會出現8的圖形介面工具。

 

 

                圖7

 

                       圖8

 

2） SQL SERVER 2008中預設就是自動更新的，自動更新時間間隔為10秒。可以通過中的設定，設定成自動更新的時間周期。如9。按右鍵“概述”，選擇“重新整理間隔”，然後選擇活動監視器擷取新的執行個體資訊所用的間隔。

 

 

          圖9

 

 

3） SQL SERVER 2008的Microsoft SQL Server Management Studio中的“活動監視器--》進程”只提供了一些基本的資訊，其中“等待類型”與“阻塞者”可呈現進程彼此的關係及進程的狀態資訊。所提供的鎖定進程的資訊沒有SQL SERVER 2005中所提供的詳細。

 

                                      圖10