最近遇到有台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


創作者介紹

╭。☆║♡♥㊗-ⓨⓤⓝ-♡♥║☆。╮

Yun1450 發表在 痞客邦 PIXNET 留言(0) 人氣()