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

何监控和解决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秒

[sql] view plain copy

 

1. sp_configure 'show advanced options', 1 ;  
2. GO  
3. RECONFIGURE ;  
4. GO  
5. sp_configure 'blocked process threshold', 5 ;  
6. GO  
7. RECONFIGURE ;  
8. GO  

 

2.2 设置事件通知 （Event Notification)

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

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

[sql] view plain copy

 

1. USE [DBA]  
2. GO  
3. CREATE QUEUE BlockedProcessReportQueue;  
4. GO  
5. CREATE SERVICE BlockedProcessReportService  
6. ON QUEUE BlockedProcessReportQueue  
7. ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);  
8. GO  
9. CREATE ROUTE BlockedProcessReportRoute  
10. WITH SERVICE_NAME = 'BlockedProcessReportService',  
11. ADDRESS = 'LOCAL';  
12. GO  
13. CREATE EVENT NOTIFICATION BlockedProcessReport  
14. ON SERVER  
15. WITH FAN_IN  
16. FOR BLOCKED_PROCESS_REPORT  
17. TO SERVICE 'BlockedProcessReportService','current database';  
18. GO  

 

2.2.2  创建一个阻塞表

[sql] view plain copy

 

1. USE [DBA]  
2. IF OBJECT_ID('[dbo].[BlockedProcessReports]') IS NULL  
3. CREATE TABLE [dbo].[BlockedProcessReports]  
4. (  
5. blocked_process_id int IDENTITY(1,1) PRIMARY KEY,  
6. database_name sysname,  
7. post_time datetime,  
8. blocked_process_report xml  
9. );  
10. GO  

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

[sql] view plain copy

 

1. USE [DBA]  
2. GO  
3. CREATE PROCEDURE [dbo].[ProcessBlockProcessReports]  
4. WITH EXECUTE AS OWNER  
5. AS  
6. SET NOCOUNT ON  
7. DECLARE @message_body XML  
8. ,@message_type INT  
9. ,@dialog UNIQUEIDENTIFIER  
10. ,@subject VARCHAR(MAX)  
11. ,@body VARCHAR(MAX)  
12. WHILE (1 = 1)  
13. BEGIN  
14. BEGIN  
15. BEGIN TRANSACTION  
16. -- Receive the next available message from the queue  
17. WAITFOR (  
18. RECEIVE TOP(1) -- just handle one message at a time  
19. @message_type=message_type_id, --the type of message received  
20. @message_body=CAST(message_body AS XML), -- the message contents  
21. @dialog = conversation_handle -- the identifier of the dialog this message was received on  
22. FROM dbo.BlockedProcessReportQueue  
23. ), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away  
24.   
25. -- If we didn't get anything, bail out  
26. IF (@@ROWCOUNT = 0)  
27. BEGIN  
28. ROLLBACK TRANSACTION  
29. BREAK  
30. END  
31. INSERT INTO [dbo].[BlockedProcessReports]  
32. (  
33. database_name  
34. ,post_time  
35. ,blocked_process_report  
36. )  
37. SELECT DB_NAME(CAST(@message_body AS XML).value('(/EVENT_INSTANCE/DatabaseID)[1]',  
38. 'int'))  
39. ,CAST(@message_body AS XML).value('(/EVENT_INSTANCE/PostTime)[1]',  
40. 'datetime')  
41. ,CAST(@message_body AS XML).query('(/EVENT_INSTANCE/TextData/blocked-process-report/.)[1]')  
42. SET @subject = @@SERVERNAME + ' - Block Notification'  
43. SELECT @body = CONVERT(NVARCHAR(MAX),CAST(@message_body AS XML).query('(/EVENT_INSTANCE/TextData/blocked-process-report/.)[1]')) + CHAR(13)  
44. + CHAR(13)  
45. + '!! Automatically generated by [Monitor].[ProcessBlockProcessReports] !!'  
46. EXEC msdb.dbo.sp_send_dbmail @recipients = 'dba@your_company' -- your email  
47. ,@subject = @subject -- Subject defined above  
48. ,@body = @body ; -- Body defined above  
49. END  
50. -- Commit the transaction. At any point before this, we could roll  
51. -- back - the received message would be back on the queue AND the response  
52. -- wouldn't be sent.  
53. COMMIT TRANSACTION  
54. END;  
55. GO  

 

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

[sql] view plain copy

 

1. USE DBA  
2. GO  
3. ALTER QUEUE BlockedProcessReportQueue  
4. WITH  
5. ACTIVATION  
6. (STATUS=ON,  
7. PROCEDURE_NAME = [dbo].[ProcessBlockProcessReports],  
8. MAX_QUEUE_READERS = 1,  
9. EXECUTE AS OWNER);  
10. GO  

 

2.2.5 ProcessBlockProcessReports表的内容

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



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

 

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