最近遇到有台DB主機的CPU使用率常常滿載，觀察DB的活動監視器，發現有過多Connection似乎沒有colse，這是不好的現象，那得先找出相關的是哪些Connection囉，SQL 2000本身只提供sp_who與sp_who2這兩個遇存程序來觀察
針對 sp_who 的使用 MSDN 中提到:
提供 Microsoft SQL Server Database Engine 執行個體中有關目前使用者、工作階段和處理序的資訊。 您可以篩選資訊，只傳回屬於特定使用者或屬於特定工作階段的非閒置處理序
也就是說可以透過 sp_who 回傳的資訊觀察連線資訊，sp_who 還有分 sp_who, sp_who2 幾種，回傳的資訊略有差異，
而回傳的SPID數值50以下的為系統進程，可以先忽略掉。
sp_who可以返回如下資訊： (可選參數LoginName, 或active代表活動會話數)
Spid (系統進程ID)
status (進程狀態)
loginame (用戶登錄名)
hostname(用戶主機名稱)
blk (阻塞進程的SPID)
dbname (進程正在使用的資料庫名)
Cmd (當前正在執行的命令類型)
sp_who2除了顯示上面sp_who的輸出資訊外，還顯示下面的資訊： (可選參數LoginName, 或active代表活動會話數)
CPUTime (進程佔用的總CPU時間)
DiskIO (進程對磁片讀的總次數)
LastBatch (客戶最後一次調用存儲過程或者執行查詢的時間)
ProgramName (用來初始化連接的應用程式名稱，或者主機名稱)
接下來就來就用 BEGIN TRAN 模擬Blocking狀態發生，
開啟一個查詢視窗，執行第一段SQL陳述
1 |
BEGIN TRAN |
2 |
UPDATE dbo.UserInfo SET Name = 'TEST' WHERE Id=44 |
3 |
--COMMIT // 先不COMMIT |
再開啟另一個查詢視窗，執行第二段SQL陳述
1 |
UPDATE dbo.UserInfo SET Name = 'TEST_2' WHERE Id=45 |
這邊可以看到第一段SQL有使用交易機制，在未Commit的情況下Table使用會被這個連線佔據，當第二段SQL執行時就必須等待第一段SQL執行Commit後才能工作
此時我使用 sp_who2 查詢資料庫進程資訊後，可以發現SPID 53的連線 BlkBy 欄位顯示55，也就是SPID 53因為SPID 55工作未執行完成而被阻擋住了
由此如果我們需要知道SPID 55是在做什麼?SQL語句為何?的時候可以用 DBCC INPUTBUFFER 陳述式查看，
DBCC INPUTBUFFER 可以查看該SPID的SQL語句是什麼，具體使用方式就是 DBCC INPUTBUFFER (SPID)，
找到了影響資料庫Blocking的原因連線後，就可以使用 KILL (SPID) 將此連線中斷掉即可，
另外也可以使用 sp_lock 查看被鎖定的情形。
參考資料:
http://technet.microsoft.com/zh-tw/library/ms174313.aspx
http://technet.microsoft.com/zh-tw/library/ms187730.aspx
http://technet.microsoft.com/zh-tw/library/ms173730.aspx
http://technet.microsoft.com/zh-tw/library/ms187749.aspx
http://support.microsoft.com/kb/264689/zh-tw
留言列表
