sql server查詢語句阻塞優化效能

2022-04-09 19:00:18

在生產環境下，有時公司客服反映網頁半天打不到，除了在瀏覽器按F12的Network響應來排查，確定web伺服器無故障後。就需要檢查資料庫是否有出現阻塞

當時資料庫的生產環境中主表資料量超過2000w,子表資料量超過1億，且更新和新增頻繁。再加上做了同步映象，很消耗資源。

這時就要新建一個對談，大概需要了解以下幾點：

1.當前活動對談量有多少？
2.對談執行時間？
3.對談之間有沒有阻塞？
4.阻塞時間 ?

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

一. 阻塞查詢 sp_lock

執行 exec sp_lock 下面列下關鍵欄位

spid 是指程序ID，這個過濾掉了系統程序，只展示了使用者程序spid>50。

dbid 指當前範例下的哪個資料庫 , 使用DB_NAME() 函數來標識資料庫

type 請求鎖住的模式

mode 鎖的請求狀態

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

總結：當mode 不為GRANT狀態時，需要了解當前鎖的模式，以及通過程序ID查詢當前sql 語句

例如當前程序ID是416，且mode狀態為WAIT 時，檢視方式 DBCC INPUTBUFFER(416)

用sp_lock查詢顯示的資訊量很少，也很難看出誰被誰阻塞。所以當資料庫版本為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;

上面查詢只顯示有阻塞的對談，關注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 能顯示對談程序有多少，等待時間， open_tran有多少事務，阻塞對談是多少. 整體內容更為詳細。
關鍵欄位說明:

spid 對談ID（程序ID），SQL內部對一個連線的編號，一般來講小於50
kipid 執行緒ID
blocked: 阻塞的程序ID，值大於0表示阻塞, 值為本身程序ID表示io操作
waittime:當前等待時間（以毫秒為單位）。
open_tran: 程序的開啟事務數
hostname:建立連線的使用者端工作站的名稱
program_name 應用程式的名稱。
hostprocess 工作站程序 ID 號。
loginame 登入名。
- [status]
- running = 對談正在執行一個或多個批
- background = 對談正在執行一個後臺任務，例如死鎖檢測
- rollback = 對談具有正在處理的事務回滾
- pending = 對談正在等待工作執行緒變為可用
- runnable = 對談中的任務在等待,由scheduler來執行的可執行佇列中。(重要)
- spinloop = 對談中的任務正在等待調節鎖變為可用。
- suspended = 對談正在等待事件（如 I/O）完成。(重要)
- sleeping = 連線空閒
- wait resource 格式為 fileid:pagenumber:rid 如(5:1:8235440)
- kpid=0, waittime=0 空閒連線
- kpid>0, waittime=0 執行狀態
- kpid>0, waittime>0 需要等待某個資源，才能繼續執行,一般會是suspended（等待io）
- kpid=0, waittime=0 但它還是阻塞的源頭，檢視open_tran>0 事務沒有及時提交。

如果blocked>0，但waittime時間很短，說明阻塞時間不長，不嚴重
如果status 上有好幾個runnable狀態任務，需要認真對待。 cpu負荷過重沒有及時處理使用者的並行請求

到此這篇關於sql server查詢語句阻塞優化效能的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。

sql server查詢語句阻塞優化效能

一. 阻塞查詢 sp_lock

二.阻塞查詢 dm_tran_locks

三.阻塞查詢 sys.sysprocesses

熱門文章