qileilove
          

          blog已經轉移至github,大家請訪問 http://qaseven.github.io/
          
			
          
				
					
	
          
		
          
			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
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db
          ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt
          ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.dm_exec_connections ec1
          ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2
          ON ec2.session_id = wt.blocking_session_id
          LEFT OUTER JOIN master.dbo.sysprocesses sp
          ON SP.spid = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
          　我們做一個測試例子來驗證一下
          　　1：打開第一會話窗口1，執行下面語句
          USE DBMonitor;
          GO
          BEGIN TRANSACTION
          SELECT * FROM dbo.TEST(TABLOCKX);
          --COMMIT TRANSACTION;
          　　2：打開第二個會話窗口2，執行下面語句
          　　USE DBMonitor;
          　　GO
          　　SELECT * FROM dbo.TEST
          　　3：打開第三個會話窗口3，執行下面語句
          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
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db
          ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt
          ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.dm_exec_connections ec1
          ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2
          ON ec2.session_id = wt.blocking_session_id
          LEFT OUTER JOIN master.dbo.sysprocesses sp
          ON SP.spid = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
          　　如下圖所，我們可以看到阻塞其它會話以及被阻塞會話的信息，如下所示
          　　現在上面SQL已經基本實現了查看阻塞具體信息的功能，但是現在又有幾個問題：
          　　1：上面SQL腳本只適合已經出現阻塞情況下查看阻塞信息，如果沒有出現阻塞情況，我總不能傻傻的一直在哪里點擊執行吧，因為阻塞這種情況有可能在那段時間都不會出現，只會在特定的時間段出現。
          　　2：我想了解一段時間內數據庫出現的阻塞情況，那么需要將阻塞信息保留下來。
          　　3：有時候忙不過來，我想將這些具體阻塞信息發送給相關開發人員，讓他們了解具體情況。
          　　于是我想通過一個存儲過程來實現這方面功能，通過設置參數@OutType，默認為輸出阻塞會話信息，當參數為"Table" 時，將阻塞信息寫入數據庫表，如果參數為 "Email"表示將阻塞信息通過郵件發送開發人員。
          　　正好這段時間，我在YourSQLDba上擴展一些功能，于是我將這個存儲過程放置在YouSQLDba數據庫中。
          USE [YourSQLDba]
          GO
          IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Maint].[BlockingSQLHistory]') AND type='U')
          BEGIN
          CREATE TABLE Maint.BlockingSQLHistory
          (
          RecordTime                        DATETIME           ,
          DatabaseName                      SYSNAME            ,
          BlockingSessesionId               SMALLINT           ,
          ProgramName                       NCHAR(128)         ,
          UserName                          NCHAR(256)         ,
          ClientIpAddress                   VARCHAR(48)        ,
          WaitType                          NCHAR(60)          ,
          BlockingStartTime                 DATETIME           ,
          WaitDuration                      BIGINT             ,
          BlockedSessionId                  INT                ,
          BlockedSQLText                    NVARCHAR(MAX)      ,
          BlockingSQLText                   NVARCHAR(MAX)      ,
          CONSTRAINT PK_BlockingSQLHistory  PRIMARY KEY(RecordTime)
          )
          END
          GO
          　　存儲過程如下所示：
          USE [YourSQLDba]
          GO
          IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Maint].[sp_who_blocking]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [Maint].[sp_who_blocking]
          GO
          SET ANSI_NULLS ON
          GO
          SET QUOTED_IDENTIFIER ON
          GO
          --==================================================================================================================
          --        ProcedureName         :            [Maint].[sp_who_blocking]
          --        Author                :            Kerry    http://www.cnblogs.com/kerrycode/
          --        CreateDate            :            2014-04-23
          --        Description           :            監控數據庫阻塞情況，顯示阻塞會話信息或收集阻塞會話信息或發送告警郵件
          /******************************************************************************************************************
          Parameters                   ：                                    參數說明
          ********************************************************************************************************************
          @OutType         :            默認為輸出阻塞會話信息，"Table", "Email"分別表示將阻塞信息寫入表或郵件發送
          @EmailSubject    :            郵件主題.默認為Sql Blocking Alert，一般指定，例如“ServerName Sql Blocking Alert"
          @ProfileName     :            @profile_name 默認值為YourSQLDba_EmailProfile
          @RecipientsLst   :            收件人列表
          ********************************************************************************************************************
          Modified Date    Modified User     Version                 Modified Reason
          ********************************************************************************************************************
          2014-04-23             Kerry         V01.00.00         新建存儲過程[Maint].[sp_who_blocking]
          *******************************************************************************************************************/
          --==================================================================================================================
          CREATE PROCEDURE [Maint].[sp_who_blocking]
          (
          @OutType
          VARCHAR(8) ='Default'                  ,
          @EmailSubject
          VARCHAR(120)='Sql Blocking Alert'      ,
          @ProfileName
          sysname='YourSQLDba_EmailProfile'      ,
          @RecipientsLst
          VARCHAR(MAX) = NULL
          )
          AS
          BEGIN
          SET NOCOUNT ON;
          DECLARE @HtmlContent  NVARCHAR(MAX) ;
          IF @OutType NOT IN ('Default', 'Table','Email')
          BEGIN
          PRINT 'The parameter @OutType is not correct,please check it';
          return;
          END
          IF @OutType ='Default'
          BEGIN
          SELECT db.name                                 AS DatabaseName
          ,wt.blocking_session_id                  AS BlockingSessesionId
          ,sp.program_name                         AS ProgramName
          ,COALESCE(sp.LOGINAME, sp.nt_username)   AS UserName
          ,ec1.client_net_address                  AS ClientIpAddress
          ,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
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db
          ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt
          ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.dm_exec_connections ec1
          ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2
          ON ec2.session_id = wt.blocking_session_id
          LEFT OUTER JOIN master.dbo.sysprocesses sp
          ON SP.spid = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
          END
          ELSE IF @OutType='Table'
          BEGIN
          INSERT INTO [Maint].[BlockingSQLHistory]
          SELECT GETDATE()                               AS RecordTime
          ,db.name                                 AS DatabaseName
          ,wt.blocking_session_id                  AS BlockingSessesionId
          ,sp.program_name                         AS ProgramName
          ,COALESCE(sp.LOGINAME, sp.nt_username)   AS UserName
          ,ec1.client_net_address                  AS ClientIpAddress
          ,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
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db
          ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt
          ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.dm_exec_connections ec1
          ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2
          ON ec2.session_id = wt.blocking_session_id
          LEFT OUTER JOIN master.dbo.sysprocesses sp
          ON SP.spid = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
          END
          ELSE IF @OutType='Email'
          BEGIN
          SET @HtmlContent =
          N'<head>'
          + N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>'
          + N'<table border="1">'
          + N'<tr>
          <th>DatabaseName</th>
          <th>BlockingSessesionId</th>
          <th>ProgramName</th>
          <th>UserName</th>
          <th>ClientIpAddress</th>
          <th>WaitType</th>
          <th>BlockingStartTime</th>
          <th>WaitDuration</th>
          <th>BlockedSessionId</th>
          <th>BlockedSQLText</th>
          <th>BlockingSQLText</th>
          </tr>' +
          CAST (
          (SELECT db.name                                  AS TD, ''
          ,wt.blocking_session_id                   AS TD, ''
          ,sp.program_name                          AS TD, ''
          ,COALESCE(sp.LOGINAME, sp.nt_username)    AS TD, ''
          ,ec1.client_net_address                   AS TD, ''
          ,wt.wait_type                             AS TD, ''
          ,ec1.connect_time                         AS TD, ''
          ,wt.WAIT_DURATION_MS/1000                 AS TD, ''
          ,ec1.session_id                           AS TD, ''
          ,h1.TEXT                                  AS TD, ''
          ,h2.TEXT                                  AS TD, ''
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db
          ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt
          ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.dm_exec_connections ec1
          ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2
          ON ec2.session_id = wt.blocking_session_id
          LEFT OUTER JOIN master.dbo.sysprocesses sp
          ON SP.spid = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
          FOR XML PATH('tr'), TYPE
          ) AS NVARCHAR(MAX) ) +
          N'</table>'
          IF @HtmlContent  IS NOT NULL
          BEGIN
          EXEC msdb.dbo.sp_send_dbmail
          @profile_name = @ProfileName    ,
          @recipients   = @RecipientsLst    ,
          @subject      = @EmailSubject    ,
          @body         = @HtmlContent    ,
          @body_format  = 'HTML' ;
          END
          END
          END
          GO
          　　最后在數據庫新建一個作業，調用該存儲過程，然后在某段時間啟用作業監控數據庫的阻塞情況，作業的執行頻率是個比較難以定奪的頭痛問題，具體要根據系統情況來決定，我習慣2分鐘執行一次。
          　　最后，這個腳本還有一個問題，如果阻塞或被阻塞的SQL語句是某個存儲過程里面的一段腳本，顯示的SQL是整個存儲過程，而不是正在執行的SQL語句，目前還沒有想到好的方法解決這個問題。我目前手工去查看阻塞情況，如果非要查看存儲過程里面被阻塞的正在執行的SQL，一般結合下面SQL語句查看（輸入阻塞或被阻塞會話ID替代@sessionid）
          SELECT   [Spid] = er.session_id
          ,[ecid]
          ,[Database] = DB_NAME(sp.dbid)
          ,[Start_Time]
          ,[SessionRunTime]    = datediff(SECOND, start_time,getdate())
          ,[SqlRunTime]=     RIGHT(convert(varchar,
          dateadd(ms, datediff(ms, sp.last_batch, getdate()), '1900-01-01'),
          121), 12)
          ,[HostName]
          ,[Users]=COALESCE(sp.LOGINAME, sp.nt_username)
          ,[Status] = er.status
          ,[WaitType] = er.wait_type
          ,[Waitime] = er.wait_time/1000
          ,[Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
          ( CASE WHEN er.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
          * 2
          ELSE er.statement_end_offset
          END - er.statement_start_offset ) / 2)
          ,[Parent Query] = qt.text
          ,[PROGRAM_NAME] = program_name
          FROM    sys.dm_exec_requests er
          INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
          CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
          WHERE   session_Id = @sessionid;
           
          English »
          		 
          Text-to-speech function is limited to 100 characters
          
		
          
			posted on 2014-06-04 10:49 順其自然EVO 閱讀(351) 評論(0)  編輯  收藏  所屬分類: 測試學習專欄 、數據庫 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
          
	
          
		
          <2014年6月>
          
	
          日一二三四五六
          25262728293031
          1234567
          891011121314
          15161718192021
          22232425262728
          293012345
          

          

					

          導航
          

					

          統計
          
	
					
					

          常用鏈接
          


          留言簿(55)
          


		
          隨筆分類
          
		
				
			
	
		
          隨筆檔案
          
		
				
			
	
		
          文章分類
          
		
				
			
	
		
          文章檔案
          
		
				
			
	



          搜索
          



          最新評論
	
          

          
	
			
		

          


          閱讀排行榜
          



          評論排行榜
          


				
			
          			
			

	

    







          
        
	




主站蜘蛛池模板：
正镶白旗|
江山市|
扎赉特旗|
齐河县|
荥阳市|
金阳县|
左云县|
府谷县|
浮山县|
封开县|
腾冲县|
奈曼旗|
牡丹江市|
开平市|
喜德县|
玉溪市|
云安县|
石门县|
舞阳县|
马山县|
邹平县|
闽清县|
通州市|
青河县|
昆明市|
莱西市|
和政县|
永春县|
肥乡县|
延津县|
长丰县|
嘉兴市|
会东县|
宁城县|
惠安县|
黄冈市|
凭祥市|
贵德县|
广西|
黔南|
航空|