开源 centos Firefox nginx java 程序员 云计算 Ubuntu google linux Windows Python shell 编程 mysql apache Android 微软 wordpress php

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
如下圖所,我們可以看到阻塞其它會話以及被阻塞會話的信息,如下所示

image

現在上面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;

延伸阅读

    评论