SQL Server 監控統計阻塞腳本資訊

    資料庫産生阻塞（Blocking）的本質原因 ：SQL語句連續持有鎖的時間過長 ，數目過多， 粒度過大。阻塞是事務隔離帶來的副作用，它是不可避免的，而且是一個資料庫系統常見的現象。 但是阻塞的時間和出現頻率要控制在一定的範圍内，阻塞持續的時間過長或阻塞出現過多（過于頻繁），就會對資料庫性能産生嚴重的影響。

    很多時候，DBA需要知道資料庫在出現性能問題時，有沒有發生阻塞？ 什麼時候開始的？發生在那個資料庫上？ 阻塞發生在那些SQL語句之間？ 阻塞的時間有多長？ 阻塞發生的頻率？ 阻塞有關的連接配接是從那些用戶端應用發送來的？.......

    如果我們能夠知道這些具體資訊，我們就能迅速定位問題，分析阻塞産生的原因，  進而找出出現性能問題的根本原因，并根據具體原因給出相應的解決方案（索引調整、優化SQL語句等）。

方法1：檢視那個引起阻塞，檢視blk不為0的記錄，如果存在阻塞程序，則是該阻塞程序的會話 ID。否則該列為零。

    EXEC sp_who active

方法2：檢視那個引起阻塞，檢視字段BlkBy，這個能夠得到比sp_who更多的資訊。

    EXEC sp_who2 active

方法3：sp_lock 系統存儲過程，報告有關鎖的資訊，但是不友善定位問題

方法4：sp_who_lock存儲過程

方法5：右鍵伺服器-選擇“活動和螢幕”，檢視程序選項。注意“任務狀态”字段。

方法6：右鍵服務名稱-選擇報表-标準報表-活動-所有正在阻塞的事務。

但是上面方法，例如像sp_who、 sp_who2，sp_who_lock等，都有或多或少的缺點：例如不能檢視阻塞和被阻塞的SQL語句。不能從檢視一段時間内阻塞發生的情況等；沒有顯示阻塞的時間....... 我們要實作下面功能：

    1:  檢視那個會話阻塞了那個會話

    2：阻塞會話和被阻塞會話正在執行的SQL語句

    3：被阻塞了多長時間

    4：像用戶端IP、Proagram_Name之類資訊

    5：阻塞發生的時間點

    6：阻塞發生的頻率

    7：如果需要，應該通知相關開發人員，DBA不能啥事情都包攬是吧，那不還得累死，總得讓開發人員員參與進來優化（有些問題就該他們解決），多了解一些系統運作的具體情況，有利于他們認識問題、解決問題。

    8：需要的時候開啟這項功能，不需要關閉這項功能

于是為了滿足上述功能，有了下面SQL 語句

我們做一個測試例子來驗證一下

1：打開第一會話視窗1，執行下面語句

2：打開第二個會話視窗2，執行下面語句

3：打開第三個會話視窗3，執行下面語句

如下圖所，我們可以看到阻塞其它會話以及被阻塞會話的資訊，如下所示

現在上面SQL已經基本實作了檢視阻塞具體資訊的功能，但是現在又有幾個問題：

          1：上面SQL腳本隻适合已經出現阻塞情況下檢視阻塞資訊，如果沒有出現阻塞情況，我總不能傻傻的一直在哪裡點選執行吧，因為阻塞這種情況有可能在那段時間都不會出現，隻會在特定的時間段出現。

          2：我想了解一段時間内資料庫出現的阻塞情況，那麼需要将阻塞資訊保留下來。

         3：有時候忙不過來，我想将這些具體阻塞資訊發送給相關開發人員，讓他們了解具體情況。

于是我想通過一個存儲過程來實作這方面功能，通過設定參數@OutType，預設為輸出阻塞會話資訊，當參數為"Table" 時，将阻塞資訊寫入資料庫表，如果參數為 "Email"表示将阻塞資訊通過郵件發送開發人員。

正好這段時間，我在YourSQLDba上擴充一些功能，于是我将這個存儲過程放置在YouSQLDba資料庫中。

存儲過程如下所示：

最後在資料庫建立一個作業，調用該存儲過程，然後在某段時間啟用作業監控資料庫的阻塞情況，作業的執行頻率是個比較難以定奪的頭痛問題，具體要根據系統情況來決定，我習慣2分鐘執行一次。

最後，這個腳本還有一個問題，如果阻塞或被阻塞的SQL語句是某個存儲過程裡面的一段腳本，顯示的SQL是整個存儲過程，而不是正在執行的SQL語句，目前還沒有想到好的方法解決這個問題。我目前手工去檢視阻塞情況，如果非要檢視存儲過程裡面被阻塞的正在執行的SQL，一般結合下面SQL語句檢視（輸入阻塞或被阻塞會話ID替代@sessionid）