診斷執行多層儲存過程的程序中的塊
免責聲明：我是一名開發人員。請對我好點。我不是負責後續內容的開發人員。我努力成為優秀的人之一。
我繼承了一份支持票證，該票證涉及導致客戶端 SQL Server 2008R2 安裝受阻的特定過程。我可以隨時在客戶端伺服器上觸發該塊，但無法在其他任何地方複製它。我們甚至創建了一個與客戶端伺服器具有完全相同的硬體統計數據的虛擬伺服器，將完全相同的數據庫恢復到完全相同的 SQL Server 設置，但沒有骰子——無法複製它。這個過程本身是醜陋的：一個儲存過程被呼叫，然後執行其他幾個儲存過程，都使用命名事務，一些嵌套在游標中。該過程遵循以下虛擬碼：
sp_Outermost (named transaction Trans_Outermost) sp_Nested1 (Trans_Nested1) sp_Nested2 (Trans_Nested2) sp_Nested3 (Trans_Nested3) sp_Nested3_1 (Trans_Nested3_1) sp_Nested3_1_1 (Trans_Nested3_1_1) sp_Nested3_1_1_1 (Trans_Nested3_1_1_1) sp_Nested3_1_1_1_1 (Trans_Nested3_1_1_1_1)對不起…不知道如何描述它。
任何儲存過程都沒有
TRY-CATCH邏輯，儘管有一些“自定義”錯誤處理涉及GOTOs並設置“錯誤號”（稍後會詳細介紹）。當我在 Activity Monitor 中查看程序時，Task State 是
RUNNING，Command 是SELECT，Wait Type 是ASYNC_NETWORK_IO。如果我執行
DBCC OPENTRAN或查看sys.dm_tran_session_transactionsandsys.dm_tran_active_transactions，它會將最外層事務 (Trans_Outermost) 列為打開事務。但是，當我對sys.dm_exec_connectionsand執行查詢時sys.dm_exec_sessions，我被告知正在執行的查詢實際上是sp_Nested3_1_1_1. 情況總是如此。此外，執行從這個答案ganked 的查詢，我看到正在等待的語句總是這樣的：SET @ErrorNum = 85656該
@ErrorNum變數在這些儲存過程中的每一個中都被聲明和使用。一個簡單的陳述會引起這麼多麻煩，這讓我感到非常非常奇怪SET，但我不相信巧合。我註釋掉了 in 的每一個用法，
@ErrorNum看看sp_Nested3_1_1_1這是否有所作為，嗯，確實如此。有一個儲存過程寫入從其他所有儲存過程呼叫的審計日誌表。現在，錯誤語句來自該過程，但仍然涉及@ErrorNum.SET @ErrorNum = 85026所以，我的問題是，我怎樣才能弄清楚這個阻塞的根本原因是什麼？如果在硬體不足的伺服器上的嵌套位置聲明並使用了同名的局部變數，這會導致問題嗎？我還能在哪裡看？
我剛剛發現這些程序
RAISERROR WITH SETERROR與一組自定義消息 ID 一起使用，都超過 85000 個。不確定這是否重要，但這是我現在正在Google搜尋的地方。我註釋掉了 sp_Nested3_1_1_1 和 sp_Nested3_1_1_1_1 中的一些程式碼，特別是圍繞這個
@ErrorNum業務，現在它告訴我在 sp_Nested3_1_1 中看似完全合法的程式碼行才是問題所在。SELECT CASE WHEN @Attached_ID = -1 THEN SCOPE_IDENTITY() ELSE @Attached_ID END AS Attached_ID這對我來說似乎完全是武斷的，讓我想知道這是否與糟糕的硬體有關，以及除了我們的之外，他們還在上面執行另外兩個企業數據庫這一事實。
我正在使用以下查詢以及活動監視器來確定發生鎖定的時間/位置：
SELECT t.text, QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name, c.connect_time, s.last_request_start_time, s.last_request_end_time, s.status FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t WHERE c.session_id = 61;--your blocking spid以及來自此答案的第一個查詢。
問：所以，我的問題是，我怎樣才能弄清楚這個阻塞的根本原因是什麼？
您可能會發現它是舊標準之一：
- 程式碼的並發問題。
- 進行重建的維護工作。
- 使用者直接連接到數據庫進行報告（或 Excel！）導致阻塞。
但是，如果不是不可能的話，僅在 Activity Monitor 中就很難追踪到這一點。如果您可以承受在客戶端伺服器上進行一些基本修改，我認為您應該專注於擷取那裡的阻塞內容。
- 將 Adam Mechanic 的 sp_WhoIsActive 放在系統上。如果您沒有僅用於保存工具的數據庫，最好將其放在 master 中，以便您可以在任何地方執行它。
- 它可以辨識阻塞鍊和鎖並輸出到表。但首先您需要製定表定義並創建表（在這種情況下，我們也會將其放入 master 中，但最好使用特殊的工具數據庫）。這是改編自 Kendra Little 的片段。 `DECLARE @destination_table VARCHAR(4000) ; SET @destination_table = ‘BLOCKED_PROCESS_REPORT’ ;
DECLARE @schema VARCHAR(4000) ; EXEC sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @find_block_leaders = 1, @RETURN_SCHEMA = 1, @SCHEMA = @schema OUTPUT ; SET @schema = REPLACE(@schema, ‘<table_name>’, @destination_table) ; PRINT @schema EXEC(@schema) ;` 3. Exec sp_configure ‘阻塞的程序門檻值（s）’。如果設置為 0，則將其設置為 15（秒）並執行 Reconfigure。這通常是安全的，但標準的買家注意警告適用。不要將它設置得比這個低得多，如果你將它設置為 30 秒，你可能會錯過一些事情，因為 30 秒是 ADO.NET 預設超時。 4. 創建執行 sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @find_block_leaders = 1, @destination_table = ‘BLOCKED_PROCESS_REPORT’ 的代理作業 5. 創建一個代理 WMI 警報，鍵入 WMI 事件警報，查詢為 SELECT * FROM BLOCKED_PROCESS_REPORT，以及執行上述作業的響應。 6. 在兩個會話中測試它（BEGIN TRAN，INSERT 到表中，然後從另一個會話中的表中刪除，並等待看到您的 BLOCKED_PROCESS_REPORT 表在大約 30 秒後開始填充數據）。
現在你坐下來等待。一旦問題再次發生，您將在 BLOCKED_PROCESS_REPORT 中獲得一堆詳細資訊，說明什麼阻塞了什麼、以什麼順序以及採取了哪些鎖，並且可以從那裡開始。
完成後記得清理這些東西。