SQL Server 是否有任何简单的方法来获取有关给定查询在执行过程中取得了多少（可能还有什么类型）锁的指标？
基本原理......
我正在重写一个相当讨厌的存储过程，它大量使用游标。基于集合的解决方案是可能的，因此我得到了三个版本：原始版本、带表变量的基于集合的版本和完全基于 CTE 的基于集合的版本。我为基于 CTE 的解决方案获得了很好的指标，但由于 CTE 版本创建的查询的大小和复杂性，我对发布它持谨慎态度，直到我对它进行了尽可能多的尽职调查。
现在我意识到 SQL 取出的锁是它认为它需要在给定时间点取出的锁，并且在加载的生产服务器上它可能表现不同，但我希望它能给我一种感觉它会表现得很好。
目前我正在使用探查器并保持事务打开以便我可以运行sp_lock，但想知道我是否错过了 SQL Server 2008 中的一个技巧。
或者，我试图说服自己，探查器在插入表变量时返回大量读取是可以的（对于给定的 okay 值），但我没有说服自己。CTE 解决方案的读取次数是 table-var 版本的一半，而 table-var 版本的高读取计数似乎完全归因于插入。就持续时间而言，CTE 和 table var one 大致相同，执行时间比基于游标的方法快大约 150-200%。
具有直方图目标、事件计数器目标或分桶器目标的扩展事件。您可以从 MSDN 上的How to: Find the Objects That Have the Most Locks Taked on them示例开始，它提供的几乎正是您想要的。您可以修改示例以获取锁类型（示例“按原样”过滤掉 S 和 U 锁），您可以优化它以按会话过滤等。
话虽这么说，但我必须指出，通过计算获得的锁来进行性能调查是非常不寻常的。重点通常放在等待统计信息上，如等待和队列方法中所述。有关如何再次使用扩展事件来捕获特定查询的相关信息的信息，请参见捕获单个操作的等待统计信息。
另一方面，如果您从并发的角度担心锁（新的 CTE 是否会因锁定而产生更多争用？），那么我会建议调查部署快照隔离。
在查询运行时获取查询的 sid，然后将其插入sys.dm_tran_locks。