- 如何监控和解决SQL Server的阻塞(1) : 点击打开链接
- 如何监控和解决SQL Server的阻塞(3)(扩展事件)点击打开链接
上篇介绍了什么是阻赛,以及如何察看"当前"的阻塞现象. 这篇我们详细谈一下如何去监控阻塞.
1. 什么是阻塞的厥值
随着系统用户数的增加,数据库访问连接也不断增加,数据库上可能的阻塞可能性也随之增加。 对于DBA来说,我们更加关注正在发生的阻塞是什么类型的,这个阻塞在什么情况下是不可以被企业用户所接受的。例如一个在线交易系统,用户在付款的时候被阻塞了5秒钟,那么5秒钟是不能被用户所接受的。而对于一个报表系统,5秒钟不算什么,可能阻塞事件超过3分钟,那么用户才会开始抱怨。这个用户忍耐的时间,我们称为厥值(Threshold).
2. 监控阻塞的厥值
2.1 监控的目的
当某个阻塞超过假定的厥值后,DBA收到通知。
2.2 如何设定阻塞的厥值
SQL 提供了一个服务器配置选项 blocked process threshold ,该选项用于指定阈值(以秒为单位),超过该阈值将生成阻塞的进程报告。可设置该阈值介于 0 到 86,400 之间。默认情况下,不生成阻塞的进程报告。对于系统任务或正在等待未生成可检测死锁的资源的任务,不生成该事件。可以定义一个生成该事件时执行的警报。例如,可以选择通知管理员采取相应的操作来处理阻塞情况。阻塞的进程阈值使用死锁监视器后台线程监视等待时间大于(或数倍于)配置的阈值的任务列表。每个报告间隔中,为每个阻塞的任务生成一次事件。已通过最大努力完成了阻塞的进程报告。不保证报表的数据始终为实时数据,也不保证报表数据接近实时。该设置立即生效,无需停止并重新启动服务器。引用:点击打开链接
开启服务器配置选项blocked process threshold ,设置厥值为5秒
- sp_configure 'show advanced options', 1 ;
- GO
- RECONFIGURE ;
- GO
- sp_configure 'blocked process threshold', 5 ;
- GO
- RECONFIGURE ;
- GO
2.2 设置事件通知 (Event Notification)
当开启Blocked process threshold后,SQL实例会自动收集超过厥值的阻赛时间,DBA所需要做的事情就是设置一个事件通知,例如:当厥值超过5秒后,发邮件通知自己,邮件内容包含阻赛的所有信息。
2.2.1 假设你有一个数据库叫做" DBA " ,首先设置Event Notification, 在DBA数据库下运行如下语句
- USE [DBA]
- GO
- CREATE QUEUE BlockedProcessReportQueue;
- GO
- CREATE SERVICE BlockedProcessReportService
- ON QUEUE BlockedProcessReportQueue
- ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
- GO
- CREATE ROUTE BlockedProcessReportRoute
- WITH SERVICE_NAME = 'BlockedProcessReportService',
- ADDRESS = 'LOCAL';
- GO
- CREATE EVENT NOTIFICATION BlockedProcessReport
- ON SERVER
- WITH FAN_IN
- FOR BLOCKED_PROCESS_REPORT
- TO SERVICE 'BlockedProcessReportService','current database';
- GO
2.2.2 创建一个阻塞表
- USE [DBA]
- IF OBJECT_ID('[dbo].[BlockedProcessReports]') IS NULL
- CREATE 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]
- GO
- CREATE PROCEDURE [dbo].[ProcessBlockProcessReports]
- WITH EXECUTE AS OWNER
- AS
- SET NOCOUNT ON
- DECLARE @message_body XML
- ,@message_type INT
- ,@dialog UNIQUEIDENTIFIER
- ,@subject VARCHAR(MAX)
- ,@body VARCHAR(MAX)
- WHILE (1 = 1)
- BEGIN
- BEGIN
- BEGIN TRANSACTION
-
- WAITFOR (
- RECEIVE TOP(1)
- @message_type=message_type_id,
- @message_body=CAST(message_body AS XML),
- @dialog = conversation_handle
- FROM dbo.BlockedProcessReportQueue
- ), TIMEOUT 1000
-
-
- IF (@@ROWCOUNT = 0)
- BEGIN
- ROLLBACK TRANSACTION
- BREAK
- END
- INSERT 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'
- ,@subject = @subject
- ,@body = @body ;
- END
-
-
-
- COMMIT TRANSACTION
- END;
- GO
2.2.4 将刚刚创建的存储过程添加入service broker queue
- USE DBA
- GO
- ALTER QUEUE BlockedProcessReportQueue
- WITH
- ACTIVATION
- (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并且倒入到表中.