一次sql server2012 AwaysON只讀節點嚴重阻塞分析

週二下午處理了一單sql server2012只讀節點阻塞嚴重的問題。首先接到應用人員說明的問題，然後我們進入該伺服器進行查詢分析：
    
SELECT blocking_session_id, wait_duration_ms, session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id IS NOT NULL;

blocking_session_id     wait_duration_ms      session_id 
40                      23675                  238
40                      10544                  115
.............................................

粗看一下，好像是40大概產生了好幾十個阻塞。致使連線到上面的使用者基本上不能使用

也可以透過sys.sysprocesses來查詢，其中blocked是阻塞產生源。

40應該是一個後臺程式，所需求的資源為LCK_M_SCH_M，當某任務正在等待獲取架構修改鎖時出現。在任務等待獲取使用中止阻塞程式的架構修改鎖時發生。 （與 ALTER TABLE 和 ALTER INDEX 的低優先順序等待選項相關。）

select blocking_session_id,wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks where session_id = 40;

blocking_session_id     wait_duration_ms      session_id

139                     375601                40

可以看到是139在阻塞這個會話，並且很長時間了，繼續查詢139執行的會話是：

SELECT t.text 
FROM sys.dm_exec_connections c 
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t 
WHERE c.session_id = 139

text

exec mytest_custom_proc '','','','','','','','','','','','傳統業代','1','','54','500','0','','613672'

可以看到執行了一個儲存過程，正是這個語句一直在阻塞。
把該儲存過程給到，開發人員分析，他們說這是某個客戶端正在執行業務資料匯出所致

其中，匯出的是狀態為1的客戶狀態，經過統計分析，這個為1的狀態的數量級大概在150萬左右，加上網路傳輸，所以一直卡在這裡，並且後來檢查，不只是一個人在匯出這個量級的資料！

所以基本上可以確定的是，從awayson的主節點發起了一個修改，在備節點進行處理，而備節點提供給客戶查詢，這個查詢時間太長，導致主節點發起的修改無法完成，形成第一層阻塞。而備節點的還原操作因為被hang，而其它基於該表查詢申請共享鎖hang住。

最後處理辦法：

    由於有好幾個客戶在做，無賴之下，我們把只讀節點停止，重新啟動。開發人員修改程式，對這種大批次的匯出進行處理