sql-server - 跟踪锁的最佳方式 - SQL Server

Question

我们发布了一个新的 sp，在测试期间我们发现当它运行时会阻塞其他 OLTP 事务。我们发现最初是因为新的 sp 导致表上的锁升级，我们减少了批处理大小的数量并且能够避免这种情况。即使在避免锁升级之后，它仍然会阻止即将到来的 oltp 事务。我认为它锁定了 oltp 事务正在更新的同一行。

我需要找到一种方法来跟踪新 sp 持有和释放的所有锁。我尝试了 trace/xevents(lock acquired/release)，它看起来不像是捕获所有锁，可能是因为它发生得太快了。

为了了解如何获取锁，我通过执行 select * from atable 对其进行了测试。但它给了我不同的结果。当我们执行 select * 时，它不会放置一系列页面锁，所以我应该在跟踪中看到共享页面锁。但我所看到的只是获取和释放了 IS 锁。

跟踪给定事务的所有锁的最佳方法是什么？

Answer 1

我在一个 session 中运行了以下查询

begin tran
update orderstst 
set unitprice=unitprice+1
waitfor delay '00:00:20'

当查询在其他 session 上运行时在 dmv 下运行..

select resource_database_id,request_mode,request_type,request_status,txt.text
 from sys.dm_tran_locks lck
 join
 sys.dm_exec_requests ec
 on ec.session_id=lck.request_session_id
  cross apply
  sys.dm_exec_Sql_text(ec.sql_handle) txt

我得到以下数据...

当事务仍未提交但已完成时，我再次在 dmv 之上运行。但没有得到任何输出。因为当前未执行。

但是在 dmv 下运行，仍然会给我所有持有锁的 session 的锁信息..所以你将能够识别哪个 session 持有更多的锁

  select resource_database_id,request_mode,request_type,request_status
 from sys.dm_tran_locks lck
 join
 sys.dm_exec_sessions ec
 on ec.session_id=lck.request_session_id

上面的查询给了我下面的信息..

所以总而言之，您必须通过 sql 代理作业运行 DMV1 或 DMV2 一段时间，然后插入到某个表中以供以后分析。

从 SQL 2012 开始，您还可以使用扩展事件..

转到管理 -> 扩展事件，右键单击并说，启动新 session 向导。

给它一个名字并在服务器启动时检查启动

下一个屏幕为您提供了是否选择默认模板的选项，我选择锁的默认模板，如下所示，然后单击下一步。

在下一个屏幕中，您可以选择不同的事件，在 channel 中，选择所有 channel 并在类别中做同样的事情，然后选择您感兴趣的事件，我在下面选择..

在这个界面，你可以选择actions，我选择text，sessionid

在下一个屏幕中，过滤就像说例如..仅为数据库名称收集事件，如'somename'或查询像一些文本..

下一个屏幕是您可以将文件保存到磁盘以供以后分析的地方。

完成其余屏幕，最后选择立即开始事件 session 选项..

当您完成收集数据后，转到扩展事件并停止您创建的 session 。右键单击并说查看目标数据..显示在屏幕下方

编辑:自 2019 年 12 月 3 日起，开始新 session 向导现在位于此处: