文章摘要
这篇文章介绍了使用复杂SQL查询分析事务锁（tran locks）的相关执行情况。该SQL使用了多个系统视图（如`sys.dm_exec_requests`、`sys.dm_exec_locks`、`sys.dm_exec_query_stats`、`sys.dm_exec_sql_text`）来提取事务锁信息，并结合子查询过滤特定事务锁和请求。通过这种方式，可以提取原始SQL语句并分析其执行情况，帮助用户更好地理解事务锁的行为。
SELECT
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st where qs.sql_handle in (select distinct sql_handle from sys.dm_exec_requests where session_id in (SELECT request_session_id as Spid
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p
ON l.resource_associated_entity_id=p.hobt_id
LEFT JOIN sys.indexes i
ON p.object_id=i.object_id
AND p.index_id=i.index_id
LEFT JOIN sys.objects o
ON p.object_id=o.object_id
LEFT JOIN sys.schemas s
ON o.schema_id=s.schema_id
LEFT JOIN sys.objects o2
ON l.resource_associated_entity_id=o2.object_id
LEFT JOIN sys.schemas s2
ON o2.schema_id=s2.schema_id
LEFT JOIN sys.databases db
ON l.resource_database_id=db.database_id
WHERE resource_database_id=DB_ID() and request_mode in (‘X’)
))