SQLServer的一次堵塞分析

SQLServer的一次堵塞分析（2010-08-27）
今天工作人員突然報告說某個介面無法正常開啟了，第一個想到的便是SQLServer又發生堵塞了。
在SQLServer中，做了一個5分鐘執行一次的定時任務，定期掃描堵塞情況；不過五分鐘有些太久了。
就執行了一下查詢堵塞的指令碼，看看目前系統里正在發生的堵塞情況。
SELECT
  blocked_query.session_id AS blocked_session_id,
  blocking_query.session_id AS blocking_session_id,
  blocking_sql_text.text AS blocking_sql_text,
  blocked_sql_text.text AS blocked_sql_text,
  waits.wait_type AS blocking_resource,
  blocked_query.command AS blocked_command,
  blocking_query.command AS blocking_command,  
  blocked_query.wait_type AS blocked_wait_type,
  blocked_query.wait_time AS blocked_wait_time,  
  blocking_query.total_elapsed_time AS blocking_elapsed_time,
  GETDATE()
  FROM sys.dm_exec_requests blocked_query
  JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) blocking_sql_text
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) blocked_sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id

查詢結果很簡單，
被堵塞的是一個select語句，堵塞的是一個觸發器；兩者操作的是同一個表，blocking_resource為LCK_M_S，很明顯是一個讀寫的相互堵塞。
分析步驟理應優先從堵塞程式開始分析，然後再分析select語句
觸發器的業務邏輯比較複雜，大概有600多行，其中有一二十個select、update語句
只能按順序一個個來分析相關的select和update語句了，看看哪條sql可能出了問題
主要是看SQL的where條件是否滿足索引和高選擇性要求，很快便定位到一條sql語句
SELECT TOP 1 @var1=field1 FROM tablename WHERE field2=@var2 AND field1 IS NOT NULL AND primarykey<>@primarykey
該表將近10萬條記錄，而執行該查詢，等待了1分鐘卻看不到執行結果。理論上是不應該的，先標記下來吧，繼續往下跟蹤。
很快又發現一條帶資料庫連結的查詢
SELECT TOP 1 primarykey FROM DBLINK.DBNAME.USERNAME.tablename WHERE COND1
先試著執行一下吧，該SQL也是半天沒有響應。
問題應該出現在這兩個地方，需要再瞭解一下相應的業務邏輯再進行SQL最佳化，當務之急是先把該session殺掉
執行kill sessionid後，卻還是無法開啟程式介面，繼續執行查詢堵塞指令碼，發現blocking_command變成了KILLED/ROLLBACK，也就是說一直處於rollback狀態，沒有殺成功，很奇怪。而且整個資料庫似乎已經全部癱瘓了，所有應用程式均無法執行。
於是系統工程師就把資料庫重啟了一下，又重新開啟該程式介面進行資料處理，結果很快又出現之前的症狀。
後來想是不是DBLINK出現了問題，繼續執行基於該DBLINK的查詢試一下，發現基本上全部無法執行；還是先檢查一下網路吧
系統工程師登陸到伺服器上檢視windows的日誌，果然發現了很多網路故障，緊急處理一下網路。
再次執行查詢堵塞指令碼，發現堵塞已經自動消除，而那條看似很慢的SQL也很快執行出結果了。

至此堵塞問題已解決。
鑑於SQLServer的鎖的隔離機制被設定為READ_COMMITTED_SNAPSHOT，讀和寫會導致衝突，問題的根源也就不難理解了，但造成問題的最終原因卻可能是多方面的。