用sp_lock診斷SQL Sever的效能問題

sp_lock 命令相關詳細描述見

http://msdn.microsoft.com/zh-cn/library/ms187749.aspx

 

 

在IT 專家中有一種普遍的誤解，就是認為“鎖定是不好的東西”，你必須盡一切可能保證資料庫鎖定不會使得進程無法正常運行。為了能夠確保一個一致的資料庫環境，在對資源進行修改時，資料庫引擎必須利用一種機制來獲得對資源的獨佔權。

SQL Server中也用鎖定，它們是指為了達到這種一致性，資料庫引擎用來保證每一次只有一個線程同時訪問同一個資源的對象。如果不用鎖定的話，各個進程同時進行資料修改就可能發生，這就會使資料庫處於一種不一致的狀態。這樣看來，鎖定就成了好東西；但是，你應該以特定的方式來計劃你的應用程式，讓涉及的鎖定的數量降到最少。在這篇文章中，我將討論一個讓你能夠分析資料庫鎖定問題的預存程序。

找出什麼被鎖定了

系統的反應遲緩意味著你應該做一些調查了。你的尋找最好從測定系統發生鎖定的數量和頻率開始。如果你的系統內容處理事務性很高的話，這樣各個應用程式爭奪資源就會很常見，從而引起鎖定。解決這些問題的關鍵就在於能夠確定被鎖定資源和爭奪資源的進程。

sp_lock

sp_lock這個系統預存程序與SQL Server 2000 打包在一起，它將使你對在你系統中發生的鎖定有深入的瞭解。這個程式會從主要資料庫中的syslockinfo中返回與鎖定相關的大量資訊，而主要資料庫是一個包括了所有允許、轉換和等待鎖定請求資訊的系統工作台。

讓我們來看一下運行 sp_lock 程式之後，它會為我們提供什麼資訊：

EXECUTE sp_lock

在我的系統中，這是該預存程序返回的內容。sp_lock 返回的資訊並不是一目瞭然的，要獲得有用的資料，還需要做一些尋找。但是，你也可以複製該預存程序的文本，然後建立一個新的，從而得到關於系統進程的更好的解釋。（在這篇文章中，我們將集中討論sp_lock返回的資料。）

從上面的結果我們可以看到spid、dbid、objid、indid、type、resource、mode和status欄位。spid是進程標識號碼，用於識別到SQL 伺服器的串連。要發現哪些使用者和該spid相連，你就要執行預存程序sp_who，並將spid作為一個參數傳輸給該程式。dbid是鎖定發生的資料庫，你可以在主要資料庫中的sysdatabases表格中找到它。欄位objid用來顯示在資料庫中鎖定發生所在的對象。要查看這個對象，你可以在主要資料庫中的sysobjects表格中查詢指定的objid。

在以上的螢幕中產生的單一記錄並不一定能顯示正在你的工作環境中發生的真實情況。在運行這個程式時，你想要找到500到1000個甚至更多結果。每一次你執行sp_lock，都將有可能得到不同的結果，因為又發生了新的鎖定，而部分舊的鎖定已經被解除了。如果你發現sp_lock返回的結果中，大量的結果都有著相同的spid，很有可能該進程進行中大型的處理，同時這些鎖定可能開始阻止新事務的發生。

當你發現一個spid 獲得了大量的資料庫鎖定時，這將有助於確定什麼預存程序或語句正在運行。為了達到這個目的，運行以下 DBCC 命令：

DBCC INPUTBUFFER(spid)

這個DBCC命令將返回正在EventInfo欄位中啟動並執行語句的相關資訊。

一個可靠的起點

系統運行緩慢可能說明你的表格上有大量的鎖定。造成這些鎖定的原因較多，如某個使用者正在你的系統中運行一個相當長的查詢，一個進程佔用大量資源或者兩個關鍵進程爭奪同一資源，經常造成死結。

一旦發現你認為正在減緩你系統速度的進程，應該怎麼辦？在大多數情況下，不能採取任何措施，只能監控系統。結束這個進程並不是明智之舉，因為它包括了很多系統鎖定，除非你完全肯定不會有其他的負面影響。不然的話，你就應該想辦法自動分析鎖定狀況。還有一個解決辦法就是想出一種方法，使得在一天的特定時間內，當系統鎖數量達到極限時，發出通知。

你對自己的系統資訊收集的越多，在解決問題時，你的優勢就越大。

Tim Chapman 是肯塔基州路易維爾市一家銀行的SQL Server資料庫管理員，他有超過7年的行業經驗。