如何监控和解决SQL Server的阻塞(2)（事件通知）

• 如何监控和解决SQL Server的阻塞(1) : 点击打开链接
• 如何监控和解决SQL Server的阻塞(3)（扩展事件）点击打开链接

 

上篇介绍了什么是阻赛,以及如何察看"当前"的阻塞现象. 这篇我们详细谈一下如何去监控阻塞.

 

1. 什么是阻塞的厥值

随着系统用户数的增加,数据库访问连接也不断增加,数据库上可能的阻塞可能性也随之增加。　对于DBA来说，我们更加关注正在发生的阻塞是什么类型的，这个阻塞在什么情况下是不可以被企业用户所接受的。例如一个在线交易系统，用户在付款的时候被阻塞了５秒钟，那么５秒钟是不能被用户所接受的。而对于一个报表系统，５秒钟不算什么，可能阻塞事件超过３分钟，那么用户才会开始抱怨。这个用户忍耐的时间，我们称为厥值(Threshold).

 

2. 监控阻塞的厥值

2.1 监控的目的

当某个阻塞超过假定的厥值后，DBA收到通知。

 

2.2 如何设定阻塞的厥值

SQL 提供了一个服务器配置选项　blocked process threshold ，该选项用于指定阈值（以秒为单位），超过该阈值将生成阻塞的进程报告。可设置该阈值介于 0 到 86,400 之间。默认情况下，不生成阻塞的进程报告。对于系统任务或正在等待未生成可检测死锁的资源的任务，不生成该事件。可以定义一个生成该事件时执行的警报。例如，可以选择通知管理员采取相应的操作来处理阻塞情况。阻塞的进程阈值使用死锁监视器后台线程监视等待时间大于（或数倍于）配置的阈值的任务列表。每个报告间隔中，为每个阻塞的任务生成一次事件。已通过最大努力完成了阻塞的进程报告。不保证报表的数据始终为实时数据，也不保证报表数据接近实时。该设置立即生效，无需停止并重新启动服务器。引用：点击打开链接

开启服务器配置选项blocked process threshold ，设置厥值为5秒

sp_configure 'show advanced options', 1 ;GORECONFIGURE ;GOsp_configure 'blocked process threshold', 5 ;GORECONFIGURE ;GO

 

2.2 设置事件通知 （Event Notification)

当开启Blocked process threshold后，SQL实例会自动收集超过厥值的阻赛时间，DBA所需要做的事情就是设置一个事件通知，例如：当厥值超过５秒后，发邮件通知自己，邮件内容包含阻赛的所有信息。

2.2.1 假设你有一个数据库叫做" DBA " ,首先设置Event Notification, 在DBA数据库下运行如下语句

USE [DBA]GOCREATE QUEUE BlockedProcessReportQueue;GOCREATE SERVICE BlockedProcessReportServiceON QUEUE BlockedProcessReportQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);GOCREATE ROUTE BlockedProcessReportRouteWITH SERVICE_NAME = 'BlockedProcessReportService',ADDRESS = 'LOCAL';GOCREATE EVENT NOTIFICATION BlockedProcessReportON SERVERWITH FAN_INFOR BLOCKED_PROCESS_REPORTTO SERVICE 'BlockedProcessReportService','current database';GO

 

2.2.2  创建一个阻塞表

USE [DBA]IF OBJECT_ID('[dbo].[BlockedProcessReports]') IS NULLCREATE TABLE [dbo].[BlockedProcessReports](blocked_process_id int IDENTITY(1,1) PRIMARY KEY,database_name sysname,post_time datetime,blocked_process_report xml);GO

2.2.3 创建一个存储过程，来告诉数据库当阻塞产生后需要干什么（例如：把阻塞信息存入BlockedProcessReports表，然后发邮件通知DBA)

USE [DBA]GOCREATE PROCEDURE [dbo].[ProcessBlockProcessReports]WITH EXECUTE AS OWNERASSET NOCOUNT ONDECLARE @message_body XML,@message_type INT,@dialog UNIQUEIDENTIFIER,@subject VARCHAR(MAX),@body VARCHAR(MAX)WHILE (1 = 1)BEGINBEGINBEGIN TRANSACTION-- Receive the next available message from the queueWAITFOR (RECEIVE TOP(1) -- just handle one message at a time@message_type=message_type_id, --the type of message received@message_body=CAST(message_body AS XML), -- the message contents@dialog = conversation_handle -- the identifier of the dialog this message was received onFROM dbo.BlockedProcessReportQueue), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away-- If we didn't get anything, bail outIF (@@ROWCOUNT = 0)BEGINROLLBACK TRANSACTIONBREAKENDINSERT INTO [dbo].[BlockedProcessReports](database_name,post_time,blocked_process_report)SELECT DB_NAME(CAST(@message_body AS XML).value('(/EVENT_INSTANCE/DatabaseID)[1]','int')),CAST(@message_body AS XML).value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),CAST(@message_body AS XML).query('(/EVENT_INSTANCE/TextData/blocked-process-report/.)[1]')SET @subject = @@SERVERNAME + ' - Block Notification'SELECT @body = CONVERT(NVARCHAR(MAX),CAST(@message_body AS XML).query('(/EVENT_INSTANCE/TextData/blocked-process-report/.)[1]')) + CHAR(13)+ CHAR(13)+ '!! Automatically generated by [Monitor].[ProcessBlockProcessReports] !!'EXEC msdb.dbo.sp_send_dbmail @recipients = 'dba@your_company' -- your email,@subject = @subject -- Subject defined above,@body = @body ; -- Body defined aboveEND-- Commit the transaction. At any point before this, we could roll-- back - the received message would be back on the queue AND the response-- wouldn't be sent.COMMIT TRANSACTIONEND;GO

 

2.2.4 将刚刚创建的存储过程添加入service broker queue

USE DBAGOALTER QUEUE BlockedProcessReportQueueWITHACTIVATION(STATUS=ON,PROCEDURE_NAME = [dbo].[ProcessBlockProcessReports],MAX_QUEUE_READERS = 1,EXECUTE AS OWNER);GO

 

2.2.5 ProcessBlockProcessReports表的内容

Block的所有历史信息会以XML格式存入



打开XML你会发现丰富的阻赛信息,足够帮助DBA 排查了

 

这里可能有些聪明的DBA 连XML都不愿意阅读,应为XML 格式不适合阅读,也不适合查询和过滤信息. 在下篇中我会讨论如何有效地阅读 XML并且倒入到表中.