[原创]SQL SERVER 监控阻塞的脚本_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3173 | 回复: 0

主题： [原创]SQL SERVER 监控阻塞的脚本

潇湘隐者

注册用户

等级：少校
经验：871
发帖：52
精华：2
注册：2014-3-29
状态：离线
发送短消息息给潇湘隐者

加好友发送短消息息给潇湘隐者

发消息

发表于：

2014-6-8 22:59:50 | [全部帖] [楼主帖]

楼主

数据库产生阻塞（Blocking）的本质原因：连续持有锁的时间过长，数目过多，粒度过大。阻塞是事务隔离带来的副作用，它是不可避免的，而且是一个数据库系统常见的现象。但是阻塞的时间和出现频率要控制在一定的范围内，阻塞持续的时间过长或阻塞出现过多（过于频繁），就会对数据库性能产生严重的影响。

很多时候，DBA需要知道数据库在出现性能问题时，有没有发生阻塞？什么时候开始的？发生在那个数据库上？阻塞发生在那些SQL语句之间？阻塞的时间有多长？阻塞发生的频率？阻塞有关的连接是从那些客户端应用发送来的？.......

如果我们能够知道这些具体情况，我们就能迅速定位阻塞问题发生在那些SQL语句之间，根据得到的具体信息（具体阻塞的时间等，那些SQL阻塞了那些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，执行下面语句

USE DBMonitor;
GO
BEGIN TRANSACTION
SELECT * FROM dbo.TEST(TABLOCKX);
--COMMIT TRANSACTION;

打开另外一个会话窗口2，执行下面语句

USE DBMonitor;
GO
SELECT * FROM dbo.TEST

打开第三个会话窗口3，执行下面语句

如下图所，我们可以看到阻塞其它会话以及被阻塞会话的信息，如下所示

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
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
USE [YourSQLDba]
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 : 监控数据库阻塞情况，显示阻塞会话信息或收集阻塞会话信息或发送告警邮件
--==================================================================================================================
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

原文地址：http://www.cnblogs.com/kerrycode/p/3756692.html

参考资料：

http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/
http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx
http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/

该贴由hui.chen转至本版2014-11-5 16:24:18

本版精华
热门帖子

操作引用/回复

总帖数

每页帖数

1/1页

返回列表

用户登录

Weblogic中间件技术论坛

Tuxedo中间件技术论坛

数据库论坛

Java论坛

Linux/unix论坛

网站地图