SQL Server 監控統計阻塞指令碼資訊

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

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

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

查看阻塞的方法比較多， 我在這篇部落格MS 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 陳述式

SELECT wt.blocking_session_id                  AS BlockingSessesionId

      ,sp.program_name                         AS ProgramName

      ,COALESCE(sp.LOGINAME, sp.nt_username)   AS HostName    

      ,ec1.client_net_address                  AS ClientIpAddress

      ,db.name                                 AS DatabaseName        

      ,wt.wait_type                            AS WaitType                    

      ,ec1.connect_time                        AS BlockingStartTime

      ,wt.WAIT_DURATION_MS/1000                AS WaitDuration

      ,ec1.session_id                          AS BlockedSessionId

      ,h1.TEXT                                 AS BlockedSQLText

      ,h2.TEXT                                 AS BlockingSQLText