如何獲取導致高鎖定等待時間的特定查詢？
我正在查看 SQL 鎖定等待時間 perf mon 計數器，它指示在一天中的某些時間範圍內的高值從 5 到 20 秒。
我已經設置了擴展事件，它為我提供了正在執行的 T-SQL 語句。有什麼方法可以獲取以下列表：
- 導致鎖定時間過長的特定查詢？
- 實際上阻止其他查詢的特定查詢？
你可以看到這些文章。它描述了 SQL 伺服器中的鎖定過程：
辨識導致長鎖定等待的 SQL Server 查詢和具有長鎖定保持時間的查詢，這些查詢是長鎖定等待的風險。
我會給你有史以來最令人沮喪的答案：做不到。
或者，至少，沒有應用程序日誌記錄的獨立單一方式，我個人建議在生產環境中這樣做是可靠的。
在實時觀察中，sys.dm_os_waiting任務可以辨識長鎖等待，等待的資源，等待的session_id和exec_context_id，blocking_session_id和blocking_exec_context_id。但是，如果阻塞會話正在執行查詢，則不一定是負責獲取鎖的查詢。事實上，空閒會話可能正在持有鎖。考慮這個有點荒謬的例子：
BEGIN TRAN SELECT TOP (1) sv.number FROM master.dbo.spt_values sv (TABLOCKX) WHERE sv.number = -9 ORDER BY sv.number; SELECT 1; WAITFOR DELAY '00:01:00'; COMMIT TRAN在另一個會話中，以下內容將處於鎖定狀態，直到事務送出…
SELECT TOP (1) sv.number FROM master.dbo.spt_values sv WHERE sv.number = -1 ORDER BY sv.number;即使您回顧了該會話中的上一個查詢，它也是 SELECT 1 並且它沒有獲取 master.dbo.spt_values 上的鎖。
所以重要的是要區分一個事務負責鎖，一個會話是事務的所有者。如果使用顯式事務，則顯式事務中的任何查詢都應被視為潛在的鎖定獲取者。
system_health 擴展事件會話包含超過 30 秒的鎖定等待資訊 - 當等待解決時記錄此資訊。
使用 system_health 會話 - SQL Server | 微軟文件
在非生產 SQL Server 中，使用 lock:acquired、lock:escalation 和 lock:released 事件進行調查。
但是在一個繁忙的系統上，有很多鎖活動，所以像這樣的擴展事件會話對我來說會有太多的觀察者成本。
因此，最簡單的情況是當 os_waiting_tasks 辨識出長時間的鎖等待時，blocking_session_id 不在事務中，並且該會話中的目前請求是獲得鎖的請求。
在這種情況下，考慮當時會話的隔離級別，因為它將指導一些鎖定行為，考慮查詢中的提示，因為它們將指導一些鎖定行為，查詢的結構可以確定鎖定順序，最後鎖定升級可能參與。從行鎖到表鎖的鎖升級是 SQL Server 在查詢獲得行鎖門檻值後嘗試的一種效率策略。因此，當數據隨時間增長時，隨著查詢範圍的增加，可能會出現以前在較小的表中或查詢範圍較小的情況下不會出現的表級鎖。如果收縮鎖記憶體可以緩解 SQL Server 的內部記憶體壓力，則 SQL Server 也可以將鎖升級用作記憶體管理工具。
同樣，如果持有鎖的會話在顯式事務中，則事務中的任何查詢都可能獲得了鎖。這就是應用程序日誌記錄的用武之地。
查詢儲存可能會有所幫助……但查詢儲存不辨識 session_id 值。而且您不會知道鎖升級是否發生在僅基於來自查詢儲存的資訊的查詢中。
鎖定資訊記錄在事務日誌中。但是，我建議僅在不得已的情況下才使用生產 SQL Server 實例中的事務日誌讀取，並且僅在極少數情況下使用。從事務日誌中讀取的 fn_dblog() 函式在執行時會導致 log_reuse_wait_desc = log_scan - 在開發實例中，我自己通過使用 fn_dblog() 來調查 txlog 增長如此之快的原因，為 txlog 完整條件做出了貢獻:-)