我们做一个测试例子来验证一下
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; |
