突然有一天,某個非常重要的Table被Lock,
之前曾經發生過一次,不過沒有找到Root Cause,
當時懷疑是因為Reindex的Job導致該Table被Lock,
不過那個時候沒有確切的證據,因為找錯方向了,
這次很即時的發現是Web的某個Query,
Block了Reindex的Job,然後Reindex的Job又Block其他動作。
當下的動作其實用了幾個簡單的DMV,
[blocking_session_id]為佔住資源Block別人的ID
-- Active blocking issues?
SELECT [session_id],
[wait_duration_ms],
[wait_type],
[blocking_session_id]
FROM sys.[dm_os_waiting_tasks]
WHERE [wait_type] LIKE N'LCK%'
ORDER BY [wait_duration_ms];
GO
在用以下語法查詢Isolation level
-- Isolation level of session?
SELECT [session_id],
[transaction_isolation_level]
FROM sys.[dm_exec_sessions]
WHERE [session_id] = 64;
GO
剛好因為公司有裝OPSERVER,所以很快發現了來源為Session 64,
而Session 64就是前面所提到的Query,而這個Session 64又Block住Session 118,
(Session 118為Reindex的Job)
經過了這次的經驗才知道,原來OPSERVER再Blocking Detected是有階層排序的….
幸好 Session ID64 的動作不是很重要的Query,因此直接 Kill後,所有的Block事件就迎刃而解了。
此外,以下有幾個DMV的SYS TABLE在Trouble shooting可以多加利用:
- sys.dm_exec_query_plan -Show plan for the query in XML format.
- sys.dm_exec_sql_text – Text of T-SQL batch.
- sys.dm_os_waiting_tasks – About blocked and blocking processes.
- sys.dm_exec_sessions – About authenticated sessions on SQL Server.
- sys.dm_exec_requests – About the requests currently executing on SQL Server.
- sys.dm_tran_locks – About the current locks and the processes blocking them.
而[wait_type]這個欄位,其實對於各種Trouble Shooting而言非常重要,狀態有很多種,詳細可以參閱以下網址:
https://msdn.microsoft.com/en-us/library/ms179984.aspx
這篇也寫的非常的棒:
https://www.mssqltips.com/sqlservertip/2927/identify-the-cause-of-sql-server-blocking/