背景

如果数据库出现大量阻塞，如不及时进行人工干预，可能会出现数据库hang，严重甚至影响用户业务正常运转。我们希望尽量在出现阻塞的时候及时邮件通知相关干系人去响应，避免出现大量用户报障之后相关处理人员才后知后觉的情况发生，或者有张表可以存储当时的阻塞情况，供我们后期优化分析使用。基于上述需求，我们基于数据库的Job来收集数据库的阻塞情况，并使用sp_sent_dbmail发送邮件；

测试环境

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

Feb 10 2012 19:39:15 

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

提前准备事项

可以提前注册发送邮件的账号

例如：

邮箱账号：wxchina_monitor@163.com

密码：省略

 

SMTP服务器: smtp.163.com

端口：25

注意登录邮箱设置SMTP

实现流程

 

实现步骤

第一步：创建用来存放信息的表

a.新建监控库【azure_monitor】以及存放阻塞记录的表【monitor_blocking】

--创建监控库
IF DB_ID('azure_monitor') IS NULL
    BEGIN
        CREATE DATABASE azure_monitor;
    END;
GO

ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;

 

USE [azure_monitor] --存放阻塞信息的库名
GO
IF OBJECT_ID('monitor_blocking','U') IS NOT NULL
DROP TABLE dbo.Monitor_blocking
GO
CREATE TABLE [dbo].[Monitor_blocking](
[monitor_id] [INT] IDENTITY(1,1) NOT NULL,
[lock type] [NVARCHAR](60) NOT NULL,
[database] [NVARCHAR](128) NULL,
[blk object] [BIGINT] NULL,
[lock req] [NVARCHAR](60) NOT NULL,
[waiter sid] [INT] NOT NULL,
[wait time] [BIGINT] NULL,
[waiter_batch] [NVARCHAR](MAX) NULL,
[waiter_stmt] [NVARCHAR](MAX) NULL,
[blocker sid] [SMALLINT] NULL,
[blocker_stmt] [NVARCHAR](MAX) NULL,
[time] [DATETIME] NOT NULL,
[blocking_date] AS (CONVERT([VARCHAR](100),[time],(23))),
[confirm_flag] [BIT] NULL,
[confirm_user] [NVARCHAR](50) NULL,
[confirm_date] [DATETIME] NULL,
CONSTRAINT [PK_monitor_blocking] PRIMARY KEY CLUSTERED
(
[monitor_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Monitor_blocking] ADD CONSTRAINT [DF_Monitor_blocking_confirm_flag] DEFAULT ((0)) FOR [confirm_flag]
GO
ALTER TABLE [dbo].[Monitor_blocking] ADD CONSTRAINT [DF_Monitor_blocking_confirm_date] DEFAULT (GETDATE()) FOR [confirm_date]
GO

　　

第二步：创建收集信息的存储过程

a.创建收集阻塞的存储过程

USE azure_monitor; 
GO 
IF OBJECT_ID('Monitor_p_blocking', 'p') IS NOT NULL
    DROP PROCEDURE Monitor_p_blocking;
GO
----监控库 sql server blocking 的存储过程例子
/*=============================================
-- Author:    jil.wen
-- Create date: 2016/11/16
-- Description:   监控数据库上阻塞情况；
-- demo :   exec dbo.Monitor_p_blocking 
 ============================================= */
CREATE PROCEDURE Monitor_p_blocking
AS
    BEGIN 
        SET NOCOUNT ON;
        INSERT  INTO [dbo].[Monitor_blocking]
                ( [lock type] ,
                  [database] ,
                  [blk object] ,
                  [lock req] ,
                  [waiter sid] ,
                  [wait time] ,
                  waiter_batch ,
                  waiter_stmt ,
                  [blocker sid] ,
                  blocker_stmt ,
                  [time]
                )
                SELECT  t1.resource_type AS [lock type] ,
                        DB_NAME(resource_database_id) AS [database] ,
                        t1.resource_associated_entity_id AS [blk object] ,
                        t1.request_mode AS [lock req]                          -- lock requested    
                        ,
                        t1.request_session_id AS [waiter sid]                      -- spid of waiter    
                        ,
                        t2.wait_duration_ms AS [wait time] ,
                        ( SELECT    text
                          FROM      sys.dm_exec_requests AS r WITH ( NOLOCK ) --- get sql for waiter    
                                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
                          WHERE     r.session_id = t1.request_session_id
                        ) AS waiter_batch ,
                        ( SELECT    SUBSTRING(qt.text,
                                              r.statement_start_offset / 2,
                                              ( CASE WHEN r.statement_end_offset = -1
                                                     THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                          * 2
                                                     ELS