sql-server-2008 - 如何结合 sys.dm_exec_query_stats 和 sys.dm_tran_locks 实现结果的融合

  显示原文与译文双语对照的内容

查询 Sqlserver 2005 -2008查询以查找死锁并识别查询文本

我有下列查询可以给我锁状态,也告诉我命令导致锁


SELECT
 object_name(sl.resource_associated_entity_id) as 'TableName', 
 dr.command,
 sl.*
FROM
 sys.dm_tran_locks as sl
 LEFT JOIN
 sys.dm_exec_requests dr
 ON
 sl.request_session_id=dr.session_id
WHERE
 sl.resource_type = 'OBJECT'

我有另一个查询,它提供了查询文本,


SELECT top 5 deqs.last_execution_time AS [Time], dest.text AS [Query],
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

但是我不知道如何组合两个查询,以便能够获得锁查询查询。 感谢你的帮助。

时间: 作者:

我创建了这个查询应该能够。


 SELECT
 object_name(sl.resource_associated_entity_id) as 'TableName', 
 dr.command, dest.text, 
 sl.*
 FROM
 sys.dm_tran_locks as sl
 LEFT JOIN
 sys.dm_exec_requests dr
 ON
 sl.request_session_id=dr.session_id

 CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS dest

 WHERE
 sl.resource_type = 'OBJECT'

作者:
...