当前位置:首页 > 数据库 > 正文内容

SQL Server中自动抓取阻塞

2024-11-29数据库3

背景

当发数据库生阻塞时,可以通过SQL语句来获取当前阻塞的会话情况,可以得到下面的信息

说明:会话55阻塞了会话53。两个会话都执行了update test set fid=10 where fid=0。

但我们也经常碰到客户生产环境出现阻塞,由于不会抓取或者没有及时抓取,导致问题发生后,由于没有相关的信息,导致问题不能定位的问题。

为了能够保留问题发生的现场,实际上可以通过SQL Server的扩展事件来实现自动抓取。

部署方式

前提

由于SQL SERVER对阻塞的跟踪报告事件默认是禁用的,需要通过执行下面的SQL语句开启。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXEC sp_configure 'show advanced options', 1; 
 
GO 
 
RECONFIGURE; 
 
GO 
 
EXEC sp_configure 'blocked process threshold', 10; 
 
GO 
 
RECONFIGURE; 
 
GO
 
EXEC sp_configure 'blocked process threshold'

执行后,应该看到下面的结果,表示修改成功。

配置

打开Microsoft SQL SERVER Management Studio,点击\扩展事件\会话

在会话节点,按右键选择【新建会话】

输入会话名称

并且勾选,来保证服务器启动时,自动启动扩展事件。

选择blocked_process_report事件

点【确认】后,可以看到新建立的【阻塞】事件会话

启动会话

选择【阻塞】事件会话,按右键弹出菜单,选择【启动会话】

监控会话

启动会话后,发生过阻塞后,就可以通过【监控实时数据】来查看数据了

查看监控结果

点击阻塞的记录,双击字段为blocked_process的值列,就可以看到通过脚本抓到的类似的阻塞会话详细信息。

问题

但,这种方式抓取,从实际运行情况来看,当阻塞的会话超过2个时,记录的信息的会话不完整,存在丢失的问题,需要注意。

打开一个新的会话,同样执行update test set fid=10 where fid=0,用语句查询时,结果如下:

表示会话55阻塞了会话53,会话53阻塞了会话73。

但此时扩展事件抓取的数据,丢失了会话55的信息。只有会话53阻塞会话73的记录。

• 查询阻塞的SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名],
  
t1.resource_associated_entity_id AS [阻塞资源对象],
  
t1.resource_description as [资源描述信息], t1.request_mode AS [请求的锁],
  
t1.request_session_id AS [等待会话], t2.wait_duration_ms AS [等待时间],     
  
(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                     
  
    CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
  
   WHERE r.session_id = t1.request_session_id
  
) AS [等待会话执行的批SQL],
  
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
  
        (CASE WHEN r.statement_end_offset = -1
  
        THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
  
        ELSE r.statement_end_offset END )/2)
  
    FROM sys.dm_exec_requests AS r WITH (NOLOCK)
  
    CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
  
    WHERE r.session_id = t1.request_session_id
  
) AS [等待会话执行的SQL],                   
  
t2.blocking_session_id AS [阻塞会话],                                       
  
(SELECT [text] FROM sys.sysprocesses AS p                                     
  
    CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
  
    WHERE p.spid = t2.blocking_session_id
  
) AS [阻塞会话执行的批SQL]
  
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
  
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
  
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

• blocked-process-report事件说明

Blocked Process Report Event Class - SQL Server | Microsoft Learn