?在生產(chǎn)環(huán)境下，有時公司客服反映網(wǎng)頁半天打不到，除了在瀏覽器按F12的Network響應(yīng)來排查，確定web服務(wù)器無故障后。就需要檢查數(shù)據(jù)庫是否有出現(xiàn)阻塞

當(dāng)時數(shù)據(jù)庫的生產(chǎn)環(huán)境中主表數(shù)據(jù)量超過2000w,子表數(shù)據(jù)量超過1億，且更新和新增頻繁。再加上做了同步鏡像，很消耗資源。

這時就要新建一個會話，大概需要了解以下幾點(diǎn)：

• 1.當(dāng)前活動會話量有多少？
• 2.會話運(yùn)行時間？
• 3.會話之間有沒有阻塞？
• 4.阻塞時間 ?

查詢阻塞的方法有很多。有sql 2000 的sp_lock, 有sql 2005及以上的dmv

一. 阻塞查詢 sp_lock

執(zhí)行 exec sp_lock ?下面列下關(guān)鍵字段

spid 是指進(jìn)程ID，這個過濾掉了系統(tǒng)進(jìn)程，只展示了用戶進(jìn)程spid>50。

dbid 指當(dāng)前實(shí)例下的哪個數(shù)據(jù)庫?, 使用DB_NAME() 函數(shù)來標(biāo)識數(shù)據(jù)庫

type 請求鎖住的模式

mode 鎖的請求狀態(tài)

• GRANT：已獲取鎖。
• CNVRT：鎖正在從另一種模式進(jìn)行轉(zhuǎn)換，但是轉(zhuǎn)換被另一個持有鎖（模式相沖突）的進(jìn)程阻塞。
• WAIT：鎖被另一個持有鎖（模式相沖突）的進(jìn)程阻塞。

總結(jié)：當(dāng)mode 不為GRANT狀態(tài)時， 需要了解當(dāng)前鎖的模式，以及通過進(jìn)程ID查找當(dāng)前sql 語句?

例如當(dāng)前進(jìn)程ID是416，且mode狀態(tài)為WAIT 時，查看方式 DBCC INPUTBUFFER(416)

用sp_lock查詢顯示的信息量很少，也很難看出誰被誰阻塞。所以當(dāng)數(shù)據(jù)庫版本為2005及以上時不建議使用。

?二.阻塞查詢 ?dm_tran_locks?

SELECT 
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

上面查詢只顯示有阻塞的會話， 關(guān)注blocking_session_id 也就是被阻塞的會話ID，同樣使用DBCC INPUTBUFFER來查詢sql語句

三.阻塞查詢?sys.sysprocesses

SELECT 
 spid,
 kpid,
 blocked,
 waittime AS 'waitms', 
 lastwaittype, 
 DB_NAME(dbid)AS DB,  
 waitresource, 
 open_tran,
 hostname,[program_name],
 hostprocess,loginame,
 [status]
 FROM sys.sysprocesses WITH(NOLOCK) 
 WHERE    kpid>0  AND  [status]<>'sleeping'  AND spid>50
 AND spid<>@@SPID

sys.sysprocesses ?能顯示會話進(jìn)程有多少， 等待時間， open_tran有多少事務(wù)， 阻塞會話是多少. 整體內(nèi)容更為詳細(xì)。
關(guān)鍵字段說明:

• spid 會話ID（進(jìn)程ID），SQL內(nèi)部對一個連接的編號，一般來講小于50
• kipid 線程ID
• blocked: 阻塞的進(jìn)程ID， 值大于0表示阻塞, 值為本身進(jìn)程ID表示io操作
• waittime:當(dāng)前等待時間（以毫秒為單位）。
• open_tran: 進(jìn)程的打開事務(wù)數(shù)
• hostname:建立連接的客戶端工作站的名稱
• program_name 應(yīng)用程序的名稱。
• hostprocess 工作站進(jìn)程 ID 號。
• loginame 登錄名。
• • [status]
  • running = 會話正在運(yùn)行一個或多個批
  • background = 會話正在運(yùn)行一個后臺任務(wù)，例如死鎖檢測
  • rollback = 會話具有正在處理的事務(wù)回滾
  • pending = 會話正在等待工作線程變?yōu)榭捎?/li>
  • runnable = 會話中的任務(wù)在等待,由scheduler來運(yùn)行的可執(zhí)行隊(duì)列中。(重要)
  • spinloop = 會話中的任務(wù)正在等待調(diào)節(jié)鎖變?yōu)榭捎谩?/li>
  • suspended = 會話正在等待事件（如 I/O）完成。(重要)
  • sleeping = 連接空閑
  • wait resource 格式為 fileid:pagenumber:rid 如(5:1:8235440)
  • kpid=0, waittime=0 空閑連接
  • kpid>0, waittime=0 運(yùn)行狀態(tài)
  • kpid>0, waittime>0 需要等待某個資源，才能繼續(xù)執(zhí)行,一般會是suspended（等待io）
  • kpid=0, waittime=0 但它還是阻塞的源頭，查看open_tran>0 事務(wù)沒有及時提交。

如果blocked>0，但waittime時間很短，說明阻塞時間不長，不嚴(yán)重
如果status 上有好幾個runnable狀態(tài)任務(wù)，需要認(rèn)真對待。 cpu負(fù)荷過重沒有及時處理用戶的并發(fā)請求