sql-server – 如何从SQL Server中的locks_lock_timeouts_greate

CREATE EVENT SESSION MyQuery ON SERVER
    ADD EVENT sqlserver.locks_lock_timeouts_greater_than_0
    (
        ACTION (sqlserver.sql_text,sqlserver.tsql_stack)
    )
    ADD TARGET package0.ring_buffer (SET max_memory = 4096)
    WITH (max_dispatch_latency = 1 seconds)

如何确定锁定的对象以及当前锁定的对象？

更新：我需要这个用于SQL Server 2008.如何在2012年之前的版本上调试锁定超时？

解决方法

您可以通过以下方式查看可用于事件的字段：

select p.name package_name,o.name event_name,c.name event_field,c.type_name field_type,c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
      on o.package_guid = p.guid
join sys.dm_xe_object_columns c
      on o.name = c.object_name
where o.object_type = 'event'
  AND o.name = 'locks_lock_timeouts_greater_than_0'
order by package_name,event_name

结果是：

sqlserver   locks_lock_timeouts_greater_than_0  ID  uint16  readonly
sqlserver   locks_lock_timeouts_greater_than_0  UUID    guid_ptr    readonly
sqlserver   locks_lock_timeouts_greater_than_0  VERSION uint8   readonly
sqlserver   locks_lock_timeouts_greater_than_0  CHANNEL etw_channel readonly
sqlserver   locks_lock_timeouts_greater_than_0  KEYWORD keyword_map readonly
sqlserver   locks_lock_timeouts_greater_than_0  count   uint64  data
sqlserver   locks_lock_timeouts_greater_than_0  lock_type   uint64  data

但是,在SQL 2012中,此事件由lock_timeout_greater_than_0替换(http://msdn.microsoft.com/en-us/library/ms144262.aspx),其中包含以下字段集

sqlserver   lock_timeout_greater_than_0 UUID    guid_ptr    readonly
sqlserver   lock_timeout_greater_than_0 VERSION uint8   readonly
sqlserver   lock_timeout_greater_than_0 CHANNEL etw_channel readonly
sqlserver   lock_timeout_greater_than_0 KEYWORD keyword_map readonly
sqlserver   lock_timeout_greater_than_0 collect_resource_description    boolean customizable
sqlserver   lock_timeout_greater_than_0 collect_database_name   boolean customizable
sqlserver   lock_timeout_greater_than_0 resource_type   lock_resource_type  data
sqlserver   lock_timeout_greater_than_0 mode    lock_mode   data
sqlserver   lock_timeout_greater_than_0 owner_type  lock_owner_type data
sqlserver   lock_timeout_greater_than_0 transaction_id  int64   data
sqlserver   lock_timeout_greater_than_0 database_id uint32  data
sqlserver   lock_timeout_greater_than_0 lockspace_workspace_id  ptr data
sqlserver   lock_timeout_greater_than_0 lockspace_sub_id    uint32  data
sqlserver   lock_timeout_greater_than_0 lockspace_nest_id   uint32  data
sqlserver   lock_timeout_greater_than_0 resource_0  uint32  data
sqlserver   lock_timeout_greater_than_0 resource_1  uint32  data
sqlserver   lock_timeout_greater_than_0 resource_2  uint32  data
sqlserver   lock_timeout_greater_than_0 object_id   int32   data
sqlserver   lock_timeout_greater_than_0 associated_object_id    uint64  data
sqlserver   lock_timeout_greater_than_0 duration    uint64  data
sqlserver   lock_timeout_greater_than_0 resource_description    unicode_string  data
sqlserver   lock_timeout_greater_than_0 database_name   unicode_string  data

从这里我能够从他们的ID导出数据库(database_id)和我的案例表(各种各样在object_id,associated_object_id,resource_0).

我没有看到一种明显的方法来查找谁将此对象从此事件捕获的数据中锁定.

编辑 – 有关使用sp_lock和sp_who2调试锁定超时事件原因的示例,请参阅SQL Server Lock Timeout Exceeded Deleting Records in a Loop.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!