我要评论背景
当发数据库生阻塞时，可以通过sql语句来获取当前阻塞的会话情况，可以得到下面的信息
说明：会话55阻塞了会话53。两个会话都执行了update test set fid=10 where fid=0。
但我们也经常碰到客户生产环境出现阻塞，由于不会抓取或者没有及时抓取，导致问题发生后，由于没有相关的信息，导致问题不能定位的问题。
为了能够保留问题发生的现场，实际上可以通过sql server的扩展事件来实现自动抓取。
部署方式
前提
由于sql server对阻塞的跟踪报告事件默认是禁用的，需要通过执行下面的sql语句开启。
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
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
以上就是sql server中自动抓取阻塞的详细流程的详细内容，更多关于sql server自动抓取阻塞的资料请关注代码网其它相关文章！
发表评论