背景
如果数据库出现大量阻塞，如不及时进行人工干预，可能会出现数据库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
ELSE r.statement_end_offset
END - r.statement_start_offset )
/ 2 + 1)
FROM sys.dm_exec_requests AS r WITH ( NOLOCK )
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
AS qt
WHERE r.session_id = t1.request_session_id
) AS waiter_stmt --- statement executing now
,
t2.blocking_session_id AS [blocker sid] --- spid of blocker
,
( SELECT text
FROM sys.sysprocesses AS p WITH ( NOLOCK ) --- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE p.spid = t2.blocking_session_id
) AS blocker_stmt ,
GETDATE() time
FROM sys.dm_tran_locks AS t1 WITH ( NOLOCK ) ,
sys.dm_os_waiting_tasks AS t2 WITH ( NOLOCK )
WHERE t1.lock_owner_address = t2.resource_address;
SET NOCOUNT OFF;
END;
GO
第三步：配置邮件设置，并测试邮件发送是否正常
--配置邮件设置
--1. 启用database mail
use master
GO
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Database mail XPs',1
reconfigure
GO
--2. 添加account
exec msdb..sysmail_add_account_sp
@account_name = 'monitor_blocking' -- mail account
,@email_address = '某某@163.com' -- sendmail address
,@display_name = 'monitor_blocking' -- sendusername
,@replyto_address = null
,@description = null
,@mailserver_name = 'smtp.163.com' -- SMTP Address
,@mailserver_type = 'SMTP' -- SMTP
,@port = 25 -- port
,@username = '某某@163.com' -- account
,@password = '替换成密码' -- pwd
,@use_default_credentials = 0
,@enable_ssl = 0 --is ssl enabled on SMTP server
,@account_id = NULL
--3. 添加profile
exec msdb..sysmail_add_profile_sp
@profile_name = 'monitor_blocking' -- profile name
,@description = 'dba mail profile' -- profile description
,@profile_id = NULL
--4. 关联account and profile
exec msdb..sysmail_add_profileaccount_sp
@profile_name = 'monitor_blocking' -- profile name
,@account_name = 'monitor_blocking' -- account name
,@sequence_number = 1 -- account order in profile
--5. 测试验证发送database mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'monitor_blocking',
@recipients = '某某@qq.com;某某@163.com;',
@body = 'This is a testing mail',
@subject = 'Testing Database Mail';
GO
后续步骤可以参考数据库出现阻塞及时邮件预警提醒（下）